delete

Undocumented parameters of database

SELECT X.KSPPINM NAME, DECODE(BITAND(KSPPIFLG/256, 1), 1, 'TRUE', 'FALSE') SESMOD, DECODE( BITAND(KSPPIFLG/65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE' ) SYSMOD, KSPPDESC DESCRIPTION FROM SYS.X_$KSPPI X WHERE X.INST_ID = USERENV('INSTANCE') AND TRANSLATE(KSPPINM,'_','#') LIKE '#%' ORDER BY 1
delete

Jobs – useful queries

show running jobs: select jf.sid,        jf.job,        jf.FAILURES,        jf.LAST_DATE,        jf.THIS_DATE,        j.LOG_USER,        j.interval,        j.what   from dba_jobs_running jf, dba_jobs j  where jf.job = j.JOB(+)
delete

Show structure of ref cursor

declare l_rcursor sys_refcursor; l_colCnt number; l_descTbl dbms_sql.desc_tab; begin open l_rcursor for select * from all_users; dbms_sql.describe_columns ( c       => dbms_sql.to_cursor_number(l_rcursor), col_cnt => l_colCnt, desc_t  => l_descTbl ); for i in 1 .. l_colCnt loop dbms_output.put_line( l_descTbl(i).col_name ); end loop; end;
delete

Show objects without necessary grants

Simple query to check objects which do not have some specified grants: SELECT DISTINCT O.OBJECT_NAME FROM ALL_OBJECTS O WHERE O.OWNER = '...your_schema...' AND O.OBJECT_NAME LIKE '...some_mask...%' AND O.OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY', 'TYPE') MINUS SELECT DISTINCT T.TABLE_NAME FROM ALL_TAB_PRIVS T WHERE T.TABLE_SCHEMA = '...your_schema...' AND T.TABLE_NAME LIKE '...some_mask...%' AND (T.GRANTEE NOT IN ( '...role_name...', '...role_name....')...
delete

Useful dictionary objects / queries

V$ACCESS – Displays objects in the database that are currently locked and the sessions that are accessing them. V$FILESTAT – Contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time. This is useful in isolating where the I/O activity is happening if the bottleneck is I/O related. show invalid objects: select * from all_objects o where o.status <> 'VALID show dependencies for some object: select DISTINCT D.OWNER, D.name, D.REFERENCED_NAME from aLL_dependencies d where d.name =...