I want to use the list of SQL statements for daily use.

-- Monitor whether the index is used
Alter index & index_name monitoring usage;
Alter index & index_name nomonitoring usage;
Select * from V $ object_usage where index_name = & index_name;

-- Calculate 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;

-- Evaluate 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 '% & parameer % ';

-- Calculate the large latch IN THE SYSTEM
Select name, sum (gets), sum (misses), sum (sleeps), sum (wait_time)
? From v $ latch_children
Group by name having sum (gets)> 50 order by 2;

-- Calculate the switching frequency of archiving logs (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 (. first_time, 'yyyy-mm-dd hh24: MI: ss') end_time, round (. 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?
-- 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;

-- Obtain invalid objects
Select 'alter procedure '| object_name | 'compile ;'
? From dba_objects
Where status = 'invalid' and owner = '&' and object_type in ('package', 'package body ');
/
Select owner, object_name, object_type, status from dba_objects where status = 'invalid ';

-- Evaluate the process/session Status
Select P. PID, P. spid, S. Program, S. Sid, S. Serial #
? From v $ PROCESS p, V $ session s where S. paddr = P. ADDR;

-- Query 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;

-- Query the index information of a 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 the table's foreign key information
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 table partitions and subpartitions (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 generate an execution plan
Explain plan set statement_id = '& SQL _id' For & SQL;
Select * from table (dbms_xplan.display );

-- Obtain the redo information of 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 % ';

-- Calculate the number of objects cached in the cache that exceed 5%.
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 is blocking a session (10 Gb)
Select Sid, username, event, blocking_session,
? Seconds_in_wait, wait_time
? From v $ session where state in ('waiting') and wait_class! = 'Idle ';

-- Calculate the OS process ID of the 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 is not null;

-- Query session Blocking
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, 'TT', '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;

-- Request waiting events and session information/request session waiting 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 not 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 not like '% SQL * Net %' order by S. Username;

-- Obtain the file_id/block_id waiting for the session
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 not like '% SQL %' and event not like '% RDBMS %' and event not like '% 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 not like '% SQL %' and event not like '% RDBMS %' and event not like '% Mon %'
) X where X. p1 = L. latch #);

-- Find the object waiting for the session
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;

-- Obtain the block information in the 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;

-- Evaluate the space usage of log files
Select le. leseq current_log_sequence #, 100 * CP. cpodr_bno/Le. lesiz percentage_full
? From x $ kcccp, x $ kccle
? Where Le. leseq = CP. cpodr_seq;

-- Find the waiting object
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

-- Calculate the redo size of the current transaction
Select Value
? From v $ mystat, V $ statname
? Where V $ mystat. Statistic # = V $ statname. Statistic # And v $ statname. Name = 'redo size ';

-- Wake up SMON to clear temporary segments
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

-- Calculate the rollback 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;

-- Query SQL statements with more disk reads
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;

-- Calculate the severe SQL statement of disk sort
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;

-- Find the object creation code
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;

-- Index 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 ';

Search for a large number of cited rows
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'

-- Find the SID of the current session, serial #
Select Sid, serial # from V $ session where audsid = sys_context ('userenv', 'sessionid ');

-- Calculate the unused space of the tablespace.
Col Mbytes format 9999.9999
Select tablespace_name, sum (bytes)/1024/1024 Mbytes from dba_free_space group by tablespace_name;

-- Calculate the trigger 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 ';

-- Query tables with undefined Indexes
Select table_name from user_tables where table_name not in (select table_name from user_ind_columns );

-- Execute common processes
Exec print_ SQL ('select count (*) from tab ');
Exec show_space2 ('table _ name ');

-- 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;

Check who is using the rollback segment, or check whether a user can use the rollback segment,
Find the increasing transaction of the rollback segment, and then look at how to handle it, whether it can be commit, and no more
Let's see if we can kill it, and so on. Check the user information and rollback segment information of the currently used rollback segment:
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 the user's rollback segment information
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, 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 memory usage
Select decode (greatest (class, 10), 10, decode (class, 1, 'data', 2, 'sort ', 4, 'head', to_char (class )), 'rollback') "class ",
Sum (decode (bitand (flag, 1), 1) "not dirty", sum (decode (bitand (flag, 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 the data buffer hit rate
Select a. Value + B. Value "logical_reads", C. Value "phys_reads ",
Round (100 * (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 # = 40 and B. Statistic # = 41 and C. Statistic # = 42;

Select name, (1-(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 (. bytes)/(1024*1024 ))-
(Sum (. bytes)/(1024*1024) shared_pool_avail, (sum (. bytes)/(1024*1024)/(max (B. value)/(1024*1024) * 100 avail_pool_pct
From v $ sgastat A, V $ parameter B where (. pool = 'shared pool 'and. name not in ('free memory ') and B. name = 'shared _ pool_size ';

-- 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 status of the object
Select owner, namespace, type, name, sharable_mem, loads, executions, locks, pins, kept
From v $ db_object_cache where type not 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 the library cache hit rate
Select namespace, gets, gethitratio * 100 gethitratio, pins, pinhitratio * 100 pinhitratio, reloads, invalidations from V $ librarycache

-- View the hash of some users
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 hit rate
Select (sum (getmisses)/sum (gets) ratio from V $ rowcache;

-- View the usage of the Undo segment
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 );

-- Obtain the locked object
Select do. object_name, session_id, process, locked_mode
From v $ locked_object Lo, dba_objects do where Lo. object_id = do. object_id;

-- Find the tracking file of 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 ';

-- Calculates the rollback segment and block number when an object is in a transaction.
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'

-- 9i online redefinition table
/* If the online redefinition table does not have a primary key, you need 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/41024050.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;

-- Permission-related dictionary
All_col_privs ???? Indicates column authorization. The user and public are granted to the column.
All_col_privs_made? Indicates column authorization. The user is the owner and the authorized user.
All_col_recd ???? Indicates column authorization. The user and public are granted to the column.
All_tab_privs ???? Indicates the authorization on the object. The user is public or authorized or the user is the owner.
All_tab_privs_made? Indicates the permission on the object. The user is the owner or an authorized user.
All_tab_privs_recd? Indicates the permission on the object. The user is public or authorized.
Dba_col_privs ???? All permissions on Database Columns
Dba_role_privs ??? Show roles that have been granted to users or other roles
Dba_sys_privs ???? System permissions granted to users or roles
Dba_tab_privs ???? All permissions on database objects
Role_role_privs ??? Show roles granted to users
Role_sys_privs ??? Displays the system permissions granted to users through roles
Role_tab_privs ??? Displays the object permissions granted to users through roles
Session_privs ???? Displays all current system permissions that a user can use
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 to the column. The user is the owner or the principal.
User_col_privs_recd: displays the permissions granted to the column. The user is the owner or authorized.
User_role_privs ??? Show all roles granted to users
User_sys_privs ??? Displays all system permissions granted to users
User_tab_privs ??? Show all object permissions granted to the user
User_tab_privs_made: displays the object permissions granted to other users. The user is the owner.
User_tab_privs_recd: displays the object permissions granted to other users. Users are granted permissions.

-- How to Use dbms_stats analysis table and mode?
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 be in the range [1,254].
Repeat -- collects histograms only on the columns that already have histograms.
Auto -- Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
Skewonly -- Oracle determines the columns to collect histograms based on the data distribution of the columns
*/