Useful SQL

I want to list useful SQL during routing DBA work
#SQL*Plus Tips
Set dbms_output size


Exit SQL*Plus when error

whenever oserror exit sql.sqlcode;
whenever sqlerror exit sql.sqlcode;

allow & (ampersands) to be used in SQL

set define ~
SELECT 'Alex & Alice' FROM dual;
SELECT q'{This is Alex's name 'Wenjin'}' FROM DUAL;  --10g and above

escape wildcard characters

select name from EMP where name like ‘%\%%’ escape ‘\’;

SQL*Plus spool to html or excel sheet

set pagesize 30000
set term off termout off
spool emp.xls
set markup html on;
select * from EMP;
set markup html off;
spool off

Using SQL*Plus copy command

copy from alex/alex@db1 to alex/alex@db2 create tmp_emp using select * from emp;

#Log miner

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => ‘/archdir/arch_61311.arc’, options=> );
SQL> Select sql_redo, sql_undo from v$logmnr_contents order by TIMESTAMP;

#Flashback Query

create table ORDER_DETAIL_bak as select *
as of timestamp to_timestamp(’20100924 00:00:00.000000′,’YYYYMMDD HH24:MI:SS.FF’);

#Flashback drop

flashback table EMP to before drop;

#Flashback database

flashback database to timestamp to_timestamp(‘20100212 22:43:47′,’YYYYMMDD HH24:MI:SS’);

#Get SCN from timestamp

select timestamp_to_scn(to_timestamp(’20100924 00:00:00.000000′,’YYYYMMDD HH24:MI:SS.FF’)) from dual;

#WITH statement

WITH tmp_list as
(select b.bind_name,b.max_length,count(*)
 from v$sql_bind_metadata b, v$sql s
 where b.address=s.child_address and s.hash_value=1698493292
 group by bind_name,max_length
select * from tmp_list where bind_name in (
select bind_name from tmp_list group by bind_name having count(*)>1

#Check literal SQL memory usage

  col stmt for a50
  set linesize 132 pagesize 1000
   SELECT substr(sql_text,1,40) "Stmt", count(*),
           sum(sharable_mem)    "Mem",
           sum(users_opening)   "Open",
           sum(executions)      "Exec"
     FROM v$sql
    GROUP BY substr(sql_text,1,40)
   HAVING sum(sharable_mem) > 1024*1024
   order by 3

#Pre-SQL for batch job, such as creating big index

set linesize 132 pagesize 1000 time on timing on echo on
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
alter session set sort_area_size=200000000;
alter session set hash_area_size=104857600;
alter session set db_file_multiblock_read_count=128;
alter session set "_sort_multiblock_read_count"=128;
alter session enable resumable;

#check whether SQL tried to update the same row

select ROW_WAIT_OBJ# ,ROW_WAIT_FILE#,ROW_WAIT_BLOCK# ,ROW_WAIT_ROW# ,machine,count(*) from v$session where sql_hash_value=2608744427 group by ROW_WAIT_OBJ# ,ROW_WAIT_FILE#,ROW_WAIT_BLOCK# ,ROW_WAIT_ROW#,machine

#Get the locked row ROWID

select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=543
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

#When insert thousands of rows to a table with literal SQL

alter session set cursor_sharing = force; –avoid shared pool issue

#display explain plan

select * from table(dbms_xplan.display); #display explain plan for, plan table
select * from table(dbms_xplan.display_cursor); #this will display the previous cursor
select * from table(dbms_xplan.display_cursor(sql_id)); #display give sql id, can add child number

#Check temporary space usage

SQL> select * from v$tempseg_usage; #check temporary tablespace usages, LMT
SQL> select username, contents, segtype, sum(blocks)/1024*8 Mb from V$TEMPSEG_USAGE group by username, contents, segtype;
SQL> select SUM(BYTES_CACHED)/1048576 alloc_mb, SUM(BYTES_USED)/1048576 inuse_mb FROM V$TEMP_EXTENT_POOL;
SQL> select sum(BYTES_USED)/1048576 used_mb, sum(BYTES_FREE)/1048576 free_mb from V$TEMP_SPACE_HEADER; #LMT temp space allocated

#reclaim space, lower high water mark
–For assm table

ALTER TABLE emp SHRINK SPACE COMPACT; –move rows, indexes still usagable
ALTER TABLE emp SHRINK SPACE; –lower high water mark, lock table a short time
ALTER TABLE emp DEALLOCATE UNUSED; –deallocation space on

–For non-assm table

alter table emp move; –table will be locked and indexes need to rebuild
–less lock time more complex

#Dynamic IN-Lists in PL/SQL

I will keep this post updated. If you find something useful, please leave me a message 🙂


About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to Useful SQL

  1. Amro says:

    I am oracle dba for SAP
    With large company
    We 25 production database the size from 2TB to 12TB

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: