Oracle Database DBA daily SQL list

-- 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 '% meer % ';

-- 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 <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 _hashvalue = 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 wner = '&' 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;

1 2 3 4 5 6 Next Page