mark We need to resize temp, but trying to resize hangs. Lets see what is using TEMP SELECT tu.username, s.sid, s.serial#, S.MACHINE, s.inst_id, tu.tablespace FROM gv$tempseg_usage tu, gv$session s WHERE tu.session_addr = s.saddr; DBSNMP 1715 99 myhost 1 TEMP2 You can either wait for the sessions to complete or in my case, it was the agent … Read more
You can shrink the datafile of the UNDO tablespace on the primary database to 1G by using the following command: ALTER DATABASE DATAFILE ‘+DATA/datafile/undotbs01.369.942861921’ RESIZE 2048M; ASM needs the correct ASM filename. The undo datafile on the standby database will be automatically resized as well. If you get the following error: ORA-03297: file contains used … Read more
There is no excerpt because this is a protected post.
Find the sessions using the default temporary tablespace. select tu.username,s.sid,s.serial#, S.MACHINE,s.inst_id, tu.tablespace from gv$tempseg_usage tu, gv$session s where tu.session_addr=s.saddr;
Step 1: Get the sql_id of the oracle session you would like to tune. There are a number of ways to get this, this is probably the easiest. select x.sid ,x.serial# ,x.username ,x.sql_id ,x.sql_child_number ,optimizer_mode ,hash_value ,address ,sql_text from v$sqlarea sqlarea ,v$session x where x.sql_hash_value = sqlarea.hash_value and x.sql_address = sqlarea.address and x.username is not … Read more
Goal The 10g version of Oracle RAC only allows ALTER SYSTEM KILL SESSION (SID,SERIAL#); as a local session on a local node. To kill from one node sessions across multiple nodes the following script has been created… Kill_Session_User(p_username VARCHAR2) – This takes the schema/user name and will loop through all sessions for this user in … Read more
Step 1. Symptoms when trying to drop a undo tablespace SQL> drop tablespace undotbs1; drop tablespace undotbs1 * ERROR at line 1: ORA-01548: active rollback segment ‘_SYSSMU1_1282527427$’ found, terminate dropping tablespace Step 2. File which rollback segments to remove SQL> SELECT segment_name, tablespace_name, status FROM sys.dba_rollback_segs; 2 SEGMENT_NAME TABLESPACE_NAME STATUS —————————— —————————— —————- SYSTEM SYSTEM … Read more
There is no excerpt because this is a protected post.
How do we report on databases that haven’t been backed up? We create a report from Enterprise manager and see what has failed and what backups are potentially missing Run this in Enterprise Manager /* mark young 23-August 2011 */ SELECT a.HOST_NAME, a.DISPLAY_NAME, CASE WHEN b.end_time < SYSDATE – 2 THEN ‘3. BACKUP OLDER THAN … Read more
Write the contents out to a CSV file import sys import cx_Oracle import os def printf (format,*args): sys.stdout.write (format % args) def printException (exception): error, = exception.args printf (“Error code = %s\n”,error.code); printf (“Error message = %s\n”,error.message); username = sys.argv[1] password = sys.argv[2] databaseName = sys.argv[3] new_user = sys.argv[4] new_password = sys.argv[5] print(‘Connecting to ‘ … Read more