DECLARE   CURSOR CC IS   SELECT * FROM ALL_OBJECTS WHERE OWNER=’ANAR’;  —– My user BEGIN       FOR I IN 1..5 LOOP              FOR II IN CC LOOP            BEGIN             EXECUTE IMMEDIATE ‘DROP ‘||II.OBJECT_TYPE||’ ‘||II.OWNER||’.’||II.OBJECT_NAME;               DBMS_OUTPUT.PUT_LINE(II.OBJECT_TYPE||’ ‘||II.OWNER||’.’||II.OBJECT_NAME);                          EXCEPTION WHEN OTHERS THEN       […]

select sid, sql_text from v$session s, v$sql q where sid in (select sid from v$session where state in (‘WAITING’) and wait_class != ‘Idle’ and event=’enq: TX – row lock contention’ and ( q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

SELECT Start_Date, Start_Time, Num_Logs, ROUND (Num_Logs * (Vl.Bytes / (1024 * 1024)), 2) AS Mbytes, Vdb.NAME AS Dbname FROM ( SELECT TO_CHAR (Vlh.First_Time, ‘YYYY-MM-DD’) AS Start_Date, TO_CHAR (Vlh.First_Time, ‘HH24’) || ‘:00’ AS Start_Time, COUNT (Vlh.Thread#) Num_Logs FROM V$log_History Vlh GROUP BY TO_CHAR (Vlh.First_Time, ‘YYYY-MM-DD’), TO_CHAR (Vlh.First_Time, ‘HH24’) || ‘:00’) Log_Hist, V$log Vl, V$database Vdb WHERE […]

SELECT a.tablespace_name, total_bytes/1024/1024 AS “Total, MB”, used_mbytes AS “Used, MB”,   (total_bytes/1024/1024 – used_mbytes) AS “Free, MB” FROM     (SELECT tablespace_name, SUM(bytes_used + bytes_free) AS total_bytes       FROM v$temp_space_header GROUP BY tablespace_name) a,     (SELECT tablespace_name, used_blocks*8/1024 AS used_mbytes FROM v$sort_segment) b WHERE a.tablespace_name=b.tablespace_name; TABLESPACE_NAME Free, MB   Used, MB Total, MB TEMP   1200 500 2000

SELECT a.tablespace_name, “Free, MB”, “Total, MB” FROM   (SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS “Total, MB” FROM dba_data_files GROUP BY tablespace_name   UNION   SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS “Total, MB” FROM dba_temp_files GROUP BY tablespace_name) a,   (SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024) AS “Free, MB” FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name (+) ORDER BY a.tablespace_name; TABLESPACE_NAME Free, MB   Total, MB SYSAUX   14 765 SYSTEM   10 150 TEMP   45 UNDOTBS1   44 89 USERS   14 3

— – V$SESSION_WAIT — – V$SESSION — – V$PROCESS SELECT W.SID, TO_CHAR (P.SPID,’99999′) PID, SUBSTR(W.EVENT, 1, 28) EVENT, SUBSTR(S.USERNAME,1,20) USERNAME, SUBSTR(S.OSUSER, 1,10) OSUSER, S.STATE, S.WAIT_TIME, S.SECONDS_IN_WAIT WIS, SUBSTR(W.P1TEXT||’ ‘||TO_CHAR(W.P1)||’-‘|| W.P2TEXT||’ ‘||TO_CHAR(W.P2)||’-‘|| W.P3TEXT||’ ‘||TO_CHAR(W.P3), 1, 45) P1_P2_P3_TEXT FROM V$SESSION_WAIT W, V$SESSION S, V$PROCESS P WHERE S.SID=W.SID AND P.ADDR = S.PADDR AND S.USERNAME IS NOT NULL AND […]

SELECT DBMS_METADATA.GET_DDL(‘USER’,UPPER(S.USERNAME)) USER_DDLFROM ALL_USER S;