SQL list for daily use (excerpt)

--Monitor whether the index uses
ALTER index &index_name monitoring usage;
Alter index &index_name nomonitoring usage;
SELECT * from v$object_usage where index_name = &index_name;

--To find the I/O distribution of data files
Select Df.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetim
? from V$filestat Fs,v$dbfile DF
where fs.file#=df.file# order by Df.name;

--Find the value of a hidden parameter
? Col ksppinm Format A54
? Col KSPPSTVL Format A54
? Select Ksppinm, KSPPSTVL
? From X$ksppi Pi, X$KSPPCV CV
where Cv.indx=pi.indx and pi.ksppinm like ' \_% ' escape ' and pi.ksppinm like '%¶meer% ';

--To find the larger latch in the system
Select Name,sum (gets), sum (misses), sum (Sleeps), sum (wait_time)
? from V$latch_children
Group BY name has sum (gets) > order by 2;

--Change the frequency of the archive log (the production system may take a long time)
Select Start_recid,start_time,end_recid,end_time,minutes from (select Test.*, rownum as RN
? From (select B.recid Start_recid,to_char (b.first_time, ' yyyy-mm-dd hh24:mi:ss ') start_time,
? A.recid End_recid,to_char (a.first_time, ' yyyy-mm-dd hh24:mi:ss ') End_time,round (((a.first_time-b.first_time) *24) * 60,2) minutes
? from V$log_history A,v$log_history b where a.recid=b.recid+1 and B.first_time > Sysdate-1
? ORDER BY a.first_time Desc) Test y where Y.rn < 30
?
--Find the transaction being processed by the rollback segment
Select A.name,b.xacts,c.sid,c.serial#,d.sql_text
? from V$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction E
where A.usn=b.usn and B.usn=e.xidusn and C.TADDR=E.ADDR
? and C.sql_address=d.address and C.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece;

--To find an invalid object
Select ' Alter PROCEDURE ' | | object_name| | ' compile; '
? from Dba_objects
where status= ' INVALID ' and owner= ' & ' and object_type in (' PACKAGE ', ' PACKAGE ');
/
Select Owner,object_name,object_type,status from dba_objects where status= ' INVALID ';

--seeking the state of process/session
Select p.pid,p.spid,s.program,s.sid,s.serial#
? from V$process p,v$session s where s.paddr=p.addr;

--Find the status of the current session
Select Sn.name,ms.value
? from V$mystat ms,v$statname SN
where ms.statistic#=sn.statistic# and Ms.value > 0;

--To find the index information of the table
Select Ui.table_name,ui.index_name
? from User_indexes Ui,user_ind_columns UIC
where Ui.table_name=uic.table_name and Ui.index_name=uic.index_name
? and ui.table_name like ' &table_name% ' and uic.column_name= ' &column_name ';

--Displays foreign key information for the table
Col search_condition format A54
Select Table_name,constraint_name
? From User_constraints
? where Constraint_type = ' R ' and constraint_name in (select Constraint_name from user_cons_columns where Column_name= ' ; 1 ');

Select Rpad (child.table_name,25, ') Child_tablename,
? rpad (cp.column_name,17, ') Referring_column,rpad (parent.table_name,25, ') Parent_tablename,
? rpad (pc.column_name,15, ') Referred_column,rpad (child.constraint_name,25, ') constraint_name
? from User_constraints child,user_constraints parent,
????? User_cons_columns cp,user_cons_columns pc
where Child.constraint_type = ' R ' and child.r_constraint_name = Parent.constraint_name and
? Child.constraint_name = cp.constraint_name and parent.constraint_name = Pc.constraint_name and
? Cp.position = pc.position and child.table_name = ' &table_name '
? order by Child.owner,child.table_name,child.constraint_name,cp.position;

--Displays the partition and sub partition of the table (user_tab_subpartitions)
COL table_name format A16
Col partition_name format A16
Col high_value format A81
Select Table_name,partition_name,high_value from user_tab_partitions where table_name= ' &table_name '

--use Dbms_xplan to build an execution plan
Explain plan set statement_id = ' &sql_id ' for &sql;
SELECT * FROM table (dbms_xplan.display);

--Ask for redo information for a transaction (bytes)
Select S.name,m.value
? From V$mystat M,v$statname s
where m.statistic#=s.statistic# and s.name like '%redo size% ';

--to cache objects that are more than 5% cached
Select O.owner,o.object_type,o.object_name,count (B.OBJD)
? from V$BH b,dba_objects o
where B.OBJD = o.object_id
? GROUP BY O.owner,o.object_type,o.object_name
"Having count (B.OBJD) >" Select To_number (Value) *0.05 from v$parameter where name = ' db_block_buffers ');

--Ask who blocked a session (10g)
Select SID, Username, event, Blocking_session,
? Seconds_in_wait, Wait_time
? from V$session where state in (' Waiting ') and Wait_class!= ' Idle ';

--The OS process ID for session
Col Program Format A54
Select P.spid "OS Thread", B.name "Name-user", S.program
? from V$process p, v$session s, v$bgprocess b
? where p.addr = s.paddr and p.addr = b.paddr
UNION All
Select P.spid "OS Thread", S.username "Name-user", S.program
? from V$process p, v$session s where p.addr = S.paddr and s.username are NOT null;

--Check the conversation block
Col user_name format A32
Select/*+ Rule */Lpad (", Decode (L.xidusn, 0,3,0)) | | L.oracle_username user_name, o.owner,o.object_name,s.sid,s.serial#
? from V$locked_object l,dba_objects o,v$session s
where l.object_id=o.object_id and L.session_id=s.sid order by o.object_id,xidusn Desc;

Col username Format A15
Col lock_level format A8
Col owner Format A18
Col object_name format A32
Select/*+ Rule */s.username, decode (L.type, ' TM ', ' table lock ', ' tx ', ' row lock ', null) Lock_level, O.owner,o.object_name, s.sid,s.serial#
? from V$session s,v$lock l,dba_objects o
where L.sid = S.sid and L.id1 = o.object_id (+) and s.username is not null;

--Waiting for the event and session information/waiting for the session and session information
Select Se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
? From V$session s,v$session_event SE
Where S.username is isn't null and Se.sid=s.sid and s.status= ' ACTIVE ' and ' se.event ' not like '%sql*net% ' order by S.username;

Select S.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait
? From V$session s,v$session_wait SW
Where s.username is not null and SW.SID=S.SID and sw.event isn't like '%sql*net% ' order by S.username;

--file_id/block_id of conversation waiting
Col Event format A24
Col p1text format A12
Col p2text format A12
Col p3text format A12
Select Sid,event,p1text, p1, P2text, p2, P3text, p3
? From v$session_wait
Where event isn't like '%sql% ' and event not like '%rdbms% ' and the '%mon% ' order by event;

Select Name,wait_time from V$latch l where exists (select 1 from (select Sid,event,p1text, p1, P2text, p2, P3text, p3
? From v$session_wait
Where event isn't like '%sql% ' and '%rdbms% ' and '%mon% '
) x where x.p1= l.latch#);

--to ask for a session waiting for the object
Col owner Format A18
Col segment_name format A32
Col segment_type format A32
Select Owner,segment_name,segment_type
? From Dba_extents
where file_id = &file_id and &block_id between block_id and block_id + blocks-1;

--To find the block information in buffer cache
Select O.object_type, substr (o.object_name,1,10) objname, B.OBJD, B.status, COUNT (B.OBJD)
? from? V$bh B, Dba_objects o
? where B.OBJD = o.data_object_id and O.owner = ' &1 ' GROUP by O.object_type, O.OBJECT_NAME,B.OBJD, B.status;

--Find out the space use of log files
Select Le.leseq current_log_sequence#, 100*cp.cpodr_bno/le.lesiz percentage_full
? from X$KCCCP cp,x$kccle le
? where Le.leseq =cp.cpodr_seq;

--Ask for the object in wait
Select/*+rule */S.sid, s.username, W.event, O.owner, O.segment_name, O.segment_type,
?????? O.partition_name, w.seconds_in_wait seconds, w.state
? From V$session_wait W, v$session s, dba_extents o
? where W.event in (select name from V$event_name? where Parameter1 = ' file# '
?? and parameter2 = ' block# ' and name not like ' control% ')
?? and O.owner <> ' sys ' and w.sid = s.sid and w.p1 = o.file_id and W.p2 >= o.block_id and W.p2 < o.block_id + O.B Locks

--Redo dimensions for current transaction
Select value
? From V$mystat, V$statname
? where v$mystat.statistic# = v$statname.statistic# and V$statname.name = ' redo size ';

--Wake Smon to clear the temporary segment
Column PID New_value Smon
Set Termout off
Select P.pid from Sys.v_$bgprocess b,sys.v_$process p where b.name = ' Smon ' and p.addr = b.paddr
/
Set Termout on
Oradebug Wakeup &smon
Undefine Smon

--Return rate
Select b.value/(A.value + b.value), a.value,b.value from V$sysstat A,v$sysstat b
where a.statistic#=4 and b.statistic#=5;

--Ask for more SQL for disk read
Select St.sql_text from V$sql S,v$sqltext St
where S.address=st.address and S.hash_value=st.hash_value and S.disk_reads > 300;

--Ask for disk sort severity SQL
Select Sess.username, Sql.sql_text, sort1.blocks
? From V$session sess, V$sqlarea sql, V$sort_usage sort1
? where sess.serial# = Sort1.session_num
?? and sort1.sqladdr = sql.address
?? and Sort1.sqlhash = Sql.hash_value? and Sort1.blocks > 200;

--To create code for the object
Column column_name format A36
Column Sql_text format A99
Select Dbms_metadata.get_ddl (' TABLE ', ' &1 ') from dual;
Select Dbms_metadata.get_ddl (' INDEX ', ' &1 ') from dual;

--Find the index of the table
Set Linesize 131
Select A.index_name,a.column_name,b.status, B.index_type
From User_ind_columns a,user_indexes b
where A.index_name=b.index_name and a.table_name= ' &1 ';

The number of the rows in the exploration and introduction
Select Index_name,blevel,num_rows,clustering_factor,status from user_indexes where Num_rows > 10000 and blevel > 0
Select Table_name,index_name,blevel,num_rows,clustering_factor,status from user_indexes where status <> ' VALID '

--sid,serial# of current session
Select Sid, serial# from v$session where audsid = Sys_context (' USERENV ', ' SESSIONID ');

--to find unused space in the table space
Col MBytes format 9999.9999
Select Tablespace_name,sum (bytes)/1024/1024 MBytes from Dba_free_space Group by Tablespace_name;

--Find the triggers defined in the table
Select Table_name,index_type,index_name,uniqueness from user_indexes where table_name= ' &1 ';
Select Trigger_name from user_triggers where table_name= ' &1 ';

--Table for undefined index
Select table_name from USER_TABLES WHERE TABLE_NAME (SELECT table_name from user_ind_columns);

--Perform common procedures
EXEC print_sql (' SELECT COUNT (*) from Tab ');
EXEC show_space2 (' table_name ');

--Please free memory
SELECT * from V$sgastat where name= ' free memory ';
Select A.name,sum (b.value) from V$statname a,v$sesstat b where a.statistic# = b.statistic# GROUP by A.name;

See who is using that to roll back the segment, or to see if a user is using the rollback segment,
Find out what's going on in the back of the roll, and see how to handle it, if you can commit it, no more.
Just see if you can kill it, and so on, view the user information and rollback segment information for the rollback segment that is currently in use:
Set Linesize 121
Select R.name "ROLLBACK SEGMENT name", l.sid "Oracle pid", p.spid "SYSTEM pid", s.username "Oracle username"
From V$lock L, v$process p, V$rollname R, V$session s
WHERE L.sid = p.pid (+) and S.sid=l.sid and TRUNC (L.ID1 (+)/65536) = R.usn and L.type (+) = ' TX ' and l.lmode (+) = 6 ORDER BY R.name;

--View information about the user's rollback segment
Select S.username, rn.name from v$session s, V$transaction T, V$rollstat R, V$rollname RN
where s.saddr = t.ses_addr and T.xidusn = R.usn and R.usn = Rn.usn

--Generate an execution plan
Explain plan set statement_id= ' A1 ' for &1;
--View the execution plan
Select Lpad (', 2* (level-1)) | | Operation Operation,options,object_name,position from Plan_table
Start with Id=0 and statement_id= ' A1 ' Connect by prior id=parent_id and Statement_id= ' A1 '

--View the memory for storage usage
Select Decode (Greatest (class,10), 10,decode (class,1, ' Data ', 2, ' Sort ', 4, ' Header ', To_char (Class)), ' Rollback ' "class ",
SUM (Decode (Bitand (flag,1), 1,0,1)) "Not Dirty", Sum (Decode (Bitand (flag,1), 1,1,0)) "Dirty",
SUM (dirty_queue) "On Dirty", COUNT (*) ' total '
From X$BH GROUP by Decode (Greatest (class,10), 10,decode (class,1, ' Data ', 2, ' Sort ', 4, ' Header ', To_char (Class)), ' Rollback ');

--View Table space status
? select Tablespace_name,extent_management,segment_space_management from Dba_tablespaces;
? select Table_name,freelists,freelist_groups from User_tables;

--View System requests
SELECT DECODE (name, ' summed dirty write queue Length ', value)/
DECODE (name, ' write requests ', value) ' Write Request Length '
From V$sysstat WHERE name in (' summed dirty queue length ', ' write requests ') and value>0;

--Calculate data buffer hit ratio
Select A.value + b.value "logical_reads", C.value "Phys_reads",
Round ((a.value+b.value)-c.value)/(A.value+b.value)) "BUFFER HIT RATIO"
From V$sysstat A, V$sysstat B, V$sysstat c
where a.statistic# = b.statistic# = and c.statistic# = 42;

SELECT name, (physical_reads/(db_block_gets+consistent_gets)) *100 h_ratio from V$buffer_pool_statistics;

--View Memory usage
Select Least (Max (b.value)/(1024*1024), sum (a.bytes)/(1024*1024)) shared_pool_used,
Max (B.value)/(1024*1024) shared_pool_size,greatest (max (b.value)/(1024*1024), sum (a.bytes)/(1024*1024))-
(SUM (a.bytes)/(1024*1024)) Shared_pool_avail ((SUM (a.bytes)/(1024*1024))/(Max (B.value)/(1024*1024)) *100 Avail_ pool_pct
From V$sgastat A, V$parameter b where (a.pool= ' shared pool "and a.name not in (' Free Memory ')) and B.name= ' Shared_pool_siz E ';

--View User memory usage
Select Username, sum (sharable_mem), sum (PERSISTENT_MEM), sum (RUNTIME_MEM)
From Sys.v_$sqlarea A, dba_users b
where a.parsing_user_id = b.user_id GROUP by username;

--View the cache condition of the object
Select Owner,namespace,type,name,sharable_mem,loads,executions,locks,pins,kept
From V$db_object_cache where type isn't in (' Not LOADED ', ' non-existent ', ' VIEW ', ' TABLE ', ' SEQUENCE ')
and Executions>0 and loads>1 and kept= ' NO ' ORDER by owner,namespace,type,executions Desc;

Select Type,count (*) from V$db_object_cache group by type;

--View library cache hit ratio
Select Namespace,gets, gethitratio*100 gethitratio,pins,pinhitratio*100 pinhitratio,reloads,invalidations from v$ Librarycache

--View some user's hash
Select A.username, COUNT (b.hash_value) total_hash,count (b.hash_value)-count (Unique (b.hash_value)) Same_hash,
(Count (Unique (b.hash_value))/count (b.hash_value)) *100 U_hash_ratio
From Dba_users A, V$sqlarea b where a.user_id=b.parsing_user_id group by A.username;

--View dictionary Hits
Select (SUM (getmisses)/sum (gets)) ratio from V$rowcache;

--View the Undo segment usage
SELECT D.segment_name,extents,optsize,shrinks,aveshrink,aveactive,d.status
From V$rollname N,v$rollstat S,dba_rollback_segs D
WHERE D.segment_id=n.usn (+) and D.SEGMENT_ID=S.USN (+);

--Invalid object
Select Owner,object_type,object_name from dba_objects where status= ' INVALID ';
Select Constraint_name,table_name from dba_constraints where status= ' INVALID ';

--Find a process and track it
Select s.sid,s.serial# from V$session s,v$process p where s.paddr=p.addr and p.spid=&1;
EXEC dbms_system. Set_sql_trace_in_session (&1,&2,true);
EXEC dbms_system. Set_sql_trace_in_session (&1,&2,false);

--Find the Locked object
Select Do.object_name,session_id,process,locked_mode
From V$locked_object Lo, dba_objects does where lo.object_id=do.object_id;

--Find the trace file for the current session
SELECT P1.value | | '/' || P2.value | | ' _ora_ ' | | P.spid | | '. Ora ' filename
? From V$process p, v$session s, v$parameter p1, V$parameter p2
? WHERE p1.name = ' user_dump_dest ' and p2.name = ' instance_name '
?? and p.addr = s.paddr and S.audsid = USERENV (' SESSIONID ') and P.background is null and InStr (P.program, ' cjq ') = 0;

--Find the file and block number of the object
Select Segment_name,header_file,header_block
From dba_segments where segment_name like ' &1 ';

--the fallback and block number of the object when the transaction occurs
Select A.segment_name,a.header_file,a.header_block
From dba_segments A,dba_rollback_segs b
where A.segment_name=b.segment_name and b.segment_id= ' &1 '

Online redefinition table for--9i
* * If the online redefined table does not have a primary key to create a primary key
exec dbms_redefinition.can_redef_table (' cybercafe ', ' announcement ');
CREATE TABLE Anno2 as SELECT * from announcement
exec dbms_redefinition.start_redef_table (' cybercafe ', ' announcement ', ' Anno2 ');
exec dbms_redefinition.sync_interim_table (' cybercafe ', ' announcement ', ' Anno2 ');
exec dbms_redefinition.finish_redef_table (' cybercafe ', ' announcement ', ' Anno2 ');
drop table Anno2
exec dbms_redefinition.abort_redef_table (' cybercafe ', ' announcement ', ' Anno2 ');

--Common LOGMNR scripts (cybercafe)
EXEC sys.dbms_logmnr_d.build (dictionary_filename => ' esal ', dictionary_location => '/HOME/ORACLE/LOGMNR ');
EXEC sys.dbms_logmnr.add_logfile (logfilename=> '/home/oracle/oradata/esal/archive/1_24050.dbf ', options=> Sys.dbms_logmnr.new);

EXEC sys.dbms_logmnr.add_logfile (logfilename=> '/home/oracle/oradata/esal/archive/1_22912.dbf ', options=> Sys.dbms_logmnr.addfile);
EXEC sys.dbms_logmnr.add_logfile (logfilename=> '/home/oracle/oradata/esal/archive/1_22913.dbf ', options=> Sys.dbms_logmnr.addfile);
EXEC sys.dbms_logmnr.add_logfile (logfilename=> '/home/oracle/oradata/esal/archive/1_22914.dbf ', options=> Sys.dbms_logmnr.addfile);

EXEC sys.dbms_logmnr.start_logmnr (dictfilename=> '/home/oracle/logmnr/esal.ora ');
CREATE TABLE LOGMNR2 as SELECT * from V$logmnr_contents;

--Permissions-related dictionaries
All_col_privs???? Represents the authorization on the column, and the user and public are the grantee
All_col_privs_made? Represents the authorization on the column, the user is the owner and the grantee
ALL_COL_RECD???? Represents the authorization on the column, and the user and public are the grantee
All_tab_privs???? Represents an authorization on an object that the user is public or is granted or that the user is the owner
All_tab_privs_made? Represents the permissions on an object, the user is the owner or the grantee
All_tab_privs_recd? Represents a permission on an object that the user is public or is granted
Dba_col_privs???? All authorizations on a database column
Dba_role_privs??? Show roles that have been granted to a user or other role
Dba_sys_privs???? System permissions granted to a user or role
Dba_tab_privs???? All permissions on a database object
Role_role_privs??? Display the roles that have been granted to the user
Role_sys_privs??? Show system permissions granted to a user by role
Role_tab_privs??? Show object permissions granted to a user by role
Session_privs???? Show all system permissions that users now have access to
User_col_privs??? Displays the permissions on the column, the user is the owner, the grantee, or the grantee
User_col_privs_made displays the permissions granted on the column, the user is the owner or the grantee
USER_COL_PRIVS_RECD displays the permissions granted on the column, the user is the owner or the grantee
User_role_privs??? Show all roles that have been granted to the user
User_sys_privs??? Displays all system permissions that have been granted to the user
User_tab_privs??? Show all object permissions that have been granted to the user
User_tab_privs_made displays the object permissions that have been granted to other users, the user is the owner
USER_TAB_PRIVS_RECD displays the object permissions that have been granted to other users, who are the grantee

--How to use Dbms_stats to analyze tables and patterns?
EXEC dbms_stats.gather_schema_stats (ownname=> ' &user_name ', Estimate_percent=>dbms_stats.auto_sample_ Size
? method_opt => ' For all columns size auto ',degree=> dbms_stats. Default_degree);
EXEC dbms_stats.gather_schema_stats (ownname=> ' &user_name ', Estimate_percent=>dbms_stats.auto_sample_ Size,cascade=>true);
/*
For all [INDEXED | HIDDEN] COLUMNS [Size_clause]
For COLUMNS [size clause] column|attribute [size_clause] [, Column|attribute [Size_clause] ...],
Where size_clause is defined as Size_clause: = size {integer | REPEAT | AUTO | Skewonly}
Integer--number of histogram buckets. Must is in the range [1,254].
Repeat--collects histograms only in the columns that already have histograms.
Auto--oracle determines the columns to collect histograms based on data distribution and the workload.
Skewonly--oracle determines the columns to collect histograms based on the data distribution of the columns
*/