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;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
TESTMC> 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; OWNER OBJECT_TYPE COUNT(1) --------------- ------------------- ---------- SYS PACKAGE BODY 24 EXFSYS PACKAGE BODY 1 APEX_030200 PACKAGE BODY 10 TESTSCHEMA PROCEDURE 42 ETNT SYNONYM 16 TESTSCHEMA TRIGGER 6 ORDPLUGINS PACKAGE BODY 2 MDSYS PACKAGE BODY 4 SYS SYNONYM 1 SYS TYPE BODY 1 DBSNMP PACKAGE BODY 1 WMSYS PACKAGE 1 EXFSYS TRIGGER 1 XDB PACKAGE BODY 3 TESTSCHEMA FUNCTION 3 SYS FUNCTION 1 SYSMAN PACKAGE BODY 2 SYS PROCEDURE 2 OLAPSYS PACKAGE BODY 9 TESTSCHEMA VIEW 90 SYS PACKAGE 6 WMSYS PACKAGE BODY 6 ORDSYS PACKAGE BODY 2 MDSYS PACKAGE 1 TESTSCHEMA PACKAGE BODY 1 TESTSCHEMA PACKAGE 1 TESTSCHEMA MATERIALIZED VIEW 70 PUBLIC SYNONYM 2 TESTSCHEMA SYNONYM 1 29 rows selected. |
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 ;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
HR> select table_name,index_name,column_name,column_position from dba_ind_columns where table_name =upper('&tab') order by 2,4,3 ; Enter value for tab: EMPLOYEES old 1: select table_name,index_name,column_name,column_position from dba_ind_columns where table_name =upper('&tab') order by 2,4,3 new 1: select table_name,index_name,column_name,column_position from dba_ind_columns where table_name =upper('EMPLOYEES') order by 2,4,3 TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ ------------------------------ ------------------------------ --------------- EMPLOYEES <strong>EMP_DEPARTMENT_IX</strong> DEPARTMENT_ID 1 EMPLOYEES EMP_EMAIL_UK EMAIL 1 EMPLOYEES EMP_EMP_ID_PK EMPLOYEE_ID 1 EMPLOYEES EMP_JOB_IX JOB_ID 1 EMPLOYEES EMP_MANAGER_IX MANAGER_ID 1 EMPLOYEES EMP_NAME_IX LAST_NAME 1 EMPLOYEES EMP_NAME_IX FIRST_NAME 2 EMPLOYEES LAST_NAME_IND LAST_NAME 1 8 rows selected. HR> |
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’ ;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SYS> 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' 2 ; 'EXECUTEDBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'||''''||OWNER||''''||',TABNAME=>'||''''||TABLE_NAME||''''||',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE);' ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'TEST2',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'DEP',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'EMPL',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'EMP',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'LOC',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'EMPLOYEE',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'SGVA_CRMGMT_EXTERNAL_STAT_MST',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'SGVA_CRMGMT_SUBDEPT_UNITMPG',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'REGIONS',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'LOCATIONS',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'DEPARTMENTS',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'JOBS',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'EMPLOYEES',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'JOB_HISTORY',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'SGVC_WORK_ALLOCATION_MST',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'SGVO_DEPT_MST',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'SGVO_UNITLSB_MPG',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'SGVA_CRMGMT_DTLS',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'SGVA_CRMGMT_ATTACH',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'SGVA_CRMGMT_REMARKS',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'SGVA_CRMGMT_MVMNT',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'COUNTRIES',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'LOCATIONS3',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'LOCATIONS2',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); 24 rows selected. SYS> |
Now execute the scripts generated
Example.,
|
1 2 3 4 5 6 |
SYS> SYS> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'LOCATIONS2',ESTIMATE_PERCENT=>40,DEGREE=>4,CASCADE=>TRUE); PL/SQL procedure successfully completed. SYS> |
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_timeselect 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.,
|
1 2 3 4 5 6 7 8 9 |
TESTMC> @long Elapsed Time Start Total Time Remain SID Time Operation Object Work Sofar (Mins) (Mins) SQL_HASH_VALUE ----- ------------ -------------------- ----------------------- ----------- ----------- --------- ---------- -------------- 1051 11-jun:05:10 Table Scan TESTUSER.ABC_REPORT 122039 823 1030 151635 0 TESTMC> |
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.