100 scripts you should know as a DBA – Part 1

In this multipart dba scripts post , we are going to learn the important scripts that a DBA should know in order to do the day to day database administration. I have not ordered these scripts based on the practicality or priority, so some of the scripts may be too basic to know.

1. How to find the number of invalid objects in the database?

set lines 1200 pages 1200
col OWNER for a15
select owner, object_type, count(1) from dba_objects where status =’INVALID’ group by owner,object_type;

2. How to create a Virtual index on a 11g database?

To create the virtual index on this column, simply add the NOSEGMENT clause to the CREATE INDEX statement.

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) NOSEGMENT;

To make the virtual index available we must set the _use_nosegment_indexes parameter.

ALTER SESSION SET “_use_nosegment_indexes” = TRUE;

Statistics can be gathered on virtual indexes in the same way as regular indexes

EXEC DBMS_STATS.gather_index_stats(‘SCHEMA_NAME’, ‘INDEX_NAME’);

3. How to find the indexes that are present on table?

set lines 1200 pages 1200
col COLUMN_NAME for a45
select table_name,index_name,column_name,column_position from dba_ind_columns where table_name =upper(‘&tab’) order by 2,4,3 ;

 

4. How to write a dynamic script to gather schema statistics ?

select ‘EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>’||””||owner||””||’,TABNAME=>’||””||table_name||””||
‘,ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE);’ from dba_tables where owner=’HR’ ;

Now execute the scripts generated

Example.,

 

If you want to get the dynamic scripts based on the size of a table,

select ‘EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>’||””||owner||””||’,TABNAME=>’||””||segment_name||””||
‘,ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE);’ from dba_segments
where owner=’HR’ and
segment_type=’TABLE’
order by bytes/1024/1024;

5. How to find long running queries?

set lines 140
cle bre
set lines 129
col sid form 9999
col start_time head “Start|Time” form a12 trunc
col opname head “Operation” form a20 trunc
col target head “Object” form a25 trunc
col totalwork head “Total|Work” form 9999999999 trunc
col Sofar head “Sofar” form 9999999999 trunc
col elamin head “Elapsed|Time|(Mins)” form 99999999 trunc
col tre head “Time|Remain|(Mins)” form 999999999 trunc
col Module Format a10

–select sid,to_char(start_time,’dd-mon:hh24:mi’) start_time,
— opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,
— time_remaining tre
–from v$session_longops
–where totalwork <> SOFAR
–order by start_time

select a.sid,to_char(start_time,’dd-mon:hh24:mi’) start_time,opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,time_remaining/60 tre,b.sql_hash_value
from v$session_longops a, v$session b
where
a.sid = b.sid and
totalwork <> SOFAR
order by start_time
/

Copy the script to long.sql

Example.,

6. How to find the global name of your database?

SYS> select * from global_name;

GLOBAL_NAME
——————–
SQLTEST

SYS> select value$ from sys.props$ where name = ‘GLOBAL_DB_NAME’;

VALUE$
—————-
SQLTEST

 

Also read, Complete Oracle 11g Course – Step by Step Oracle 11g

Words from dbapath

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.