Oracle Cheat Sheet

This blog post contains some random Oracle commands or SQL statements to have them at hand when they are needed.

Performance

# creating a table with a specific number of rows containing sample data  (max. around 5 billions on 12.2 (5 000 000 000))
create table t as select a2.* from all_objects a1, all_objects a2 where rownum<=100000 order by a1.object_id;
# create a plan without running the statement and show the plan
# only shows the plan Oracle would use
explain plan set statement_id ='st1' for select count(*) from t1;
select plan_table_output from table(dbms_xplan.display('PLAN_TABLE','st1','ALL ALLSTATS'));
# run a query and show the plan (1)
# uses explain, so the actual plan might differ
set autotrace on -- to avoid output from the query: set autotrace traceonly
select count(*) from t;
# run a query and show the plan (2)
# shows the actual plan used to run the statement
alter session set statistics_level = all;
select count(*) from t;
select * from table(dbms_xplan.display_cursor(format=>'ALL ALLSTATS'));
# show the last plan used for a statement where the SQL ID is known
select * from table(dbms_xplan.display_cursor('sql_id', null, 'ALL ALLSTATS LAST'));
# getting stats of the current session
set linesize 180
set pagesize 60
set trimspool on
set tab off
col name for a48
col event for a48
col stat_name for a48
col value for 999,999,999,999
select sn.name, ms.value 
from v$statname sn, v$mystat ms
where ms.value!=0 and sn.statistic#=ms.statistic#;
select se.event, se.total_waits, se.total_timeouts, se.time_waited, se.max_wait
from v$session_event se
where se.sid=sys_context('userenv','sid'); -- time_waited and max_wait = hundredths of a second
select tm.stat_name, tm.value 
from v$sess_time_model tm
where tm.sid=sys_context('userenv','sid') and tm.value!=0;
# find database segment based on file_id and block_id
SELECT  owner,segment_name,segment_type FROM  dba_extents WHERE  file_id=1 AND 41686 BETWEEN block_id AND block_id + blocks -1;
# display bind variable values used in statements
select * from v$sql_bind_capture where sql_id='sql_id';
# or
select * from table(dbms_xplan.display_cursor('sql_id', null, 'ADVANCED'));
# use bind variables in sqlplus
variable B1 varchar2(32)
execute :B1 := 'S'
variable B2 nvarchar2(32)
execute :B2 := '6315223'
select :B1, :B2 from dual;
# use bind variables in SQL Developer
var v0 varchar2(32);
exec :v0 := '123';
select * from t where c1 = :v0;
# get file_no and block_no of table rows
select rownum, dbms_rowid.rowid_relative_fno(rowid) file_no, dbms_rowid.rowid_block_number(rowid) block_no from t;
# find a long running SQL statement
select sql_id,elapsed_time/1000000 elapsed_sec,sql_fulltext,last_active_time,parsing_schema_name from v$sql order by elapsed_sec desc;
# Run a Dictionary Integrity Check
exec dbms_hm.run_check('Dictionary Integrity Check', 'dict_int')
set long 10000
select dbms_hm.get_run_report('dict_int') from dual;
# Get database NLS parameters
select * from nls_database_parameters;
# create a 10046 trace (including wait events and bind variables)
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='trc1';
alter session set events '10046 trace name context forever, level 12';
< SQL Statement > z. B. select count(*) from dba_objects where owner='SYS';
alter session set events '10046 trace name context off';
Tracefile in $ORACLE_BASE/diag/rdbms/$SID/$SID/trace
# get dependant objects and their status
select o.owner,o.object_name,o.object_type,o.status from dba_dependencies d, dba_objects o where d.owner=o.owner and d.name=o.object_name and d.type=o.object_type and d.referenced_owner='USER1' and d.referenced_name='T' order by o.status;