- Monitor indexing uses Alter Index & index_name monitoring usage; alter index & index_name nomonitoring usage; select * from v $ object_usage where index_name = & index_name
- I / O distribution of data file 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;
- See 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% '
- Summary Latchselect Name, SUM (Gets), SUM (Misses), SUM (Sleeps), SUM (WAIT_TIME)? From V $ Latch_Children Group by Name Having Sum (Gets)> 50 ORDER BY 2;
- Switching frequency for framing logs (Possible time will be very long) 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 DEST) Y WHERE Y.RN <30? - Request Returning Segments Are Processing Transactions 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 Ewhere 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
- find invalid object 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'; $ PROCESS P, V $ session s where s.paddr = p.Addr;
- See 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;
- seeking information table of the index 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 = '& colorn_name';
- foreign key information col search_condition format a54select table_name table, 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; - display of the partition and the sub-partition table (user_tab_subpartitions) col table_name format a16col partition_name format a16col high_value format a81select table_name, partition_name, hIGH_VALUE from user_tab_partitions where table_name = '& table_name'
- Generate an execution plan explain plan set statement_id = '& sql_id' for & sql; select * from table (DBMS_XPLAN.DISPLAY) is used using DBMS_XPLAN; SELECT *.
- Seeking a transaction redo information (bytes) SELECT S.NAME, M.VALUE? From V $ mystat M, V $ STATNAME S? WHERE M.STATITISTIC # = s.statistic # and s.name like '% REDO SIZE% ';
- Seeking Cache Coaches more than 5% of the object Select O.Owner, O.Object_Type, O.Object_name, Count (B.Objd)? From V $ BH B, DBA_Objects Owhere B.Objd = 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_buffrs');
- Ask who blocked a session (10g) Select Sid, UserName, Event, Blocking_Session, Drames_in_Wait, Wait_Time? From v $ session where state in ('waiting') and wait_class! = 'Idle';
- See SESSION process IDCOL Program Format A54Select P.SPID "OS Thread", B.Name "Name-User", S.Program? From V $ PROCESS P, V $ Session S, V $ BGPROCESS B? WHERE P .addr = spaddr and p.addr = b.paddr union allselect 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; - Search sessions Blocking COL USER_NAME FORMAT A32SELECT / * RULE * / LPAD ('', DECODE (L.XIDUSN, 0, 3, 0)) || L .oracle_user, o.object_name, s.sid, s.serial #? from v $ locked_object L, DBA_Objects O, V $ session s where l.object_id = object_id and l.session_id = s.SID ORDER BY O.Object_ID, Xidusn DESC;
col username format a15col lock_level format a8col owner format a18col object_name format a32select / * rule * / s.username, decode (l.type, 'tm', 'table lock', 'tx', 'row lock', null) lock_level , O.WNER, 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 = Object_ID ( ) And S.Username is not null;
- See the events and session information / Several Waiting and Session Information Select S.SID, S.USERNAME, SE.EVENT, SE.TOTAL_WAITS, SE.TIME_WAITAL_WAITS, SE.TIME_WAITAL_WAITS, SE.TIME_WAITED, SE.AVERAGE_WAIT? From V $ session S, V $ Session_event sewhere 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 swwhere s.username is not null and sw.SID = S.SID AND SW.Event Not Like '% SQL * Net%' Order by S.Username;
- seeking the session waiting file_id / block_idcol event format a24col p1text format a12col p2text format a12col p3text format a12select sid, event, p1text, p1, p2text, p2, p3text, p3 from v $ session_waitwhere 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_waitwhere evenet not limited Like '% RDBMS%' AND event not limited '% MON%') x where x.p1 = l.latch #);
? - seeking the session waiting for the object col owner format a18col segment_name format a32col segment_type format a32select owner, segment_name, segment_type from dba_extentswhere file_id = & file_id and & block_id between block_id and block_id blocks - 1;
- See 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;
- Space for log files Using Select Le.leseq current_log_sequence #, 100 * cp.cpodr_bno / le.Lesiz percentage_for? From x $ kcccp CP, x $ kccle le? Where le.leseq = cp.cpodr_seq;
- See the objects in the waiting target / * 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 NAMETER1 = 'file #'?? and parameter2 = 'block #' And name not like 'control%') ?? Ando.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.blocks - Reform Size 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 the temporary section Column Pid new_value SMONSET TERMOUT OFFSELECT P.PID from sys.v_ $ bgprocess B, SYS.V_ $ process P where b.name = 'smon' and p.addr = b.paddr / set termout Onoradebug Wakeup & SMONUNDEFINE SMON
- Refreshment 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 more SQLSELECT St.SQL_Text from V $ SQL S.Address = st.hash_value and s.disk_reads> 300;
- Discover DISK Sort serious sqlselect 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;
- seeking to create an object code that column column_name format a36column sql_text format a99select dbms_metadata.get_ddl ( 'TABLE', '& 1') from dual; select dbms_metadata.get_ddl ( 'INDEX', '& 1') from dual;
- seeking the table index set linesize 131select a.index_name, a.column_name, b.status, b.index_typefrom user_ind_columns a, user_indexes bwhere a.index_name = b.index_name and a.table_name = '& 1'; request an index representing the number of rows more select index_name, blevel, num_rows, CLUSTERING_FACTOR, status from user_indexes where num_rows> 10000 and blevel> 0select table_name, index_name, blevel, num_rows, CLUSTERING_FACTOR, status from user_indexes where status <> 'VALID'
- Seeking the current session SID, Serial # SELECT SID, Serial # from v $ session where audsid = sys_context ('useerenv', 'sessionID');
- Non-spaced space for watch space Col MBytes Format 9999.9999Select TableSpace_name, SUM (Bytes) / 1024/1024 mbytes from dba_free_space group by tablespace_name;
- See the trigger Select Table_name, INDEX_TYPE, INDEX_NAME, UNIQUENESS from User_Name, Uniqueness from User_Name, Uniqueness from User_Name, SELECT TRIGGER_NAME from User_Triggers Where Table_name = '& 1';
- Table select Table_name from user_tables where_name from user_tables where_name from user_tables where _name from user_tables;
- Execute the common process Exec Print_sql ('select count (*) from tab'; exec show_space2 ('Table_name');
- FREE MEMORYSELECT * FROM V $ SGASTAT WHERE Name = 'Free Memory'; SELECT A.NAME, SUM (B.VALUE) from V $ STATNAME A, V $ SESSSTAT B Where A.Statistic # = B.Statistic # group By a.name;
Check out who is using that can have a rollover, or check one can get the user who uses a return group, find out the growing transaction, then look at how to handle it, can you commit it? If you don't look at whether you can kill it, wait, check the user information and return segment information of the currently being used: Set Linesize 121Select 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 swhere 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 User Rollover segments Select S.username, RN.Name from V $ Session S, V $ ROLLSTAT R, V $ ROLLNAME RNWHERE S.SADDR = T.SES_ADDR AND T.XIDUSN = R.usn and R .usn = rn.usn
- Generate Execution Plan Explain Plan Set Statement_id = 'A1' For & 1; - View Execution Plan SELECT LPAD (', 2 * (Level-1)) || Operation Operation, Options, Object_name, Position from Plan_TableStart with id = 0 and statement_id = 'a1' connection by prior id = parent_id and statement_id = 'a1'
- View memory in memory 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 (DIRTY (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;
- Check system requests where 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 hits 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 $ SSSSTAT A, V $ SYSSTAT B, V $ sysstat cwhere 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 (A.BYTES) / (1024 * 1024)) - (A.BYTES) / (1024 * 1024)) Shared_Pool_avail, ((SUM A.BYTES / (1024 * 1024)) / (B.Value) / (1024 * 1024))) * 100 Avail_Pool_PCTFROM V $ SGASTAT A, V $ Parameter B Where (a.pool = 'Shared Pool' and A.NAME NOT IN ('Free Memory')) and b.name = 'shared_pool_size';
- View users use memory SELECT Username, SUM (Shaable_Mem), SUM (persistent_mem), SUM (Runtime_Mem) from sys.v_ $ sqlarea a, dba_users bwhere a.parsing_user_id = B.user_id group by username;
- View objects Cache SELECT OWNER, NAMESPACE, TYPE, NAME, Shaable_Mem, Loads, Executions, Locks, Pins, KeptFrom V $ db_object_cache where type not in ('not loaded', 'non-evistent', '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;
- Check the library cache hit ratio select namespace, gets, gethitratio * 100 gethitratio, pins, pinhitratio * 100 pinhitratio, RELOADS, INVALIDATIONS from v $ librarycache-- view some users hashselect a.username, count (b.hash_value) total_hash COUNT (B.hash_Value) -count (unique (b.hash_value)) Same_hash, (count (B.hash_Value) / count (b.hash_value) * 100 U_HASH_TIOFROM DBA_USERS A, V $ SQLAREA B WHERE A. User_id = b.parsing_user_id group by a.username;
- View Dictionary Sort Rate Select (SUM (GETMISSES) / SUM (Gets)) Ratio from V $ ROWCACHE
- View undo segment usage SELECT d.segment_name, extents, optsize, shrinks, aveshrink, aveactive, d.statusFROM v $ rollname n, v $ rollstat s, dba_rollback_segs dWHERE 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' Download Admiral;
- Ask a process and track it for 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 out locking objects Select Do.object_name, session_id, process, locked_mode from v $ loced_Object Lo, DBA_Objects do where lo.bject_id = DO.Object_ID;
- Request the current session tracking file Select P1.Value || '/' || p2.value || '_ora_' || p.spid || '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 = useerenv ('sessionID') and P . background is null and INSTR (P.Program, 'CJQ') = 0;
- select segment_name block number and file where the object request, header_file, header_block from dba_segments where segment_name like '& 1'; - rollback segment block number and select a.segment_name transaction occurs when an object request, 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 table redefinition / * If the online redefined table primary key need not create the primary key * / exec dbms_redefinition.can_redef_table ( 'cybercafe', 'announcement'); create table anno2 as select * from announcementexec 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 Anno2exec dbms_redefinition.abort_redef_table ('cybercafe ",' announcement ',' annou2 ');
- common logmnr script (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; - Dictionary related to permissions all_col_privs ???? The authorization, user, and public are authorized by the author all_col_privs_made? Indicating the authorization on the column, the user is the authorization, the authority ALL_COL_RECD ???? indicates the authorization on the column, the user, and public is awarded ALL ALL_TAB_PRIVS ???? Authorization on the object, the user is a public or awarder or user who is the owner all_tab_privs_made? Represents the permissions on the object, the user is the owner or awarder all_tab_privs_recd? Represents the permissions on the object, the user is public or granted by DBA_COL_PRIVS ?? • All authorization DBA_ROLE_PRIVs on the database column Displays the role DBA_SYS_PRIVS ???? I have granted the user or other role system authority DBA_TAB_PRIVS ???? All rights on the database object Role_Role_Privs ??? display The role Role_sys_privs that has been granted users is displayed by the system authority Role_tab_privs ??? to display the object authority of the user through the role session_privs ???? Display the user now available all system authority USER_COL_PRIVS ??? display column Permissions, the user is the owner, awarder, or awarded user_col_privs_made display list, the user is the authority or the authority user_col_privs_recd display list, the user is the owner or awarder user_role_privs ?? • Display all roles that have been granted to the user User_Sys_Privs ??? Display all system permissions that have been granted to the user User_tab_privs ??? Display all object authority that has been granted to the user USER_TAB_PRIVS_MADE display has been granted to other users, the user is the owner User_tab_privs_recd Displays object authority that has been granted to other users, users are granted
