Oracle DB Admin-Data Dictionary

System privileges
Check database setup is the RAC(Cluster Database) or NON-RAC(Single Instance)?
How to generate the HTML Report of any SQL Query
How to display the SQL Query result in single line?
Find the Database Sessions by schema name wise
Find the OSUSER and MACHINE name of sessions
How to check the Database sessions,process and open cursor parameters?
Verify the size of datafiles
How to find the DDL of any Specific Database Object (e.g TABLE,VIEW,SEQUENCE etc …?)
Check the Database Server Status
How to find the Database Name from any Server?
Tablespace and User Administration
How to find the location of Tablespace’s Datafile ?
How to check that Tablespace is AutoExtend or not?
How to Add New Datafile to Existing Tablespace?
How to find the Specific Oracle Data Dictionary ?
To find database redo load per day
How to monitor the Backup ?
How to Kill the app_username sessions?
Check Database Characteristics
Check Profile
Check DB Directory
Check Datafile
Check user source
Database Scheduler
USER TABLES
Count Tables list
Check the Total cursors open, by session
Alert Log Monitoring
Identifying Open Uncommitted Transactions
Check the Deadlock
DB Features used/not
DB Options installed/not installed
DB active_session_history
Find Invalid Objects
Find the Location of Datafile

System privileges

System wide access – resource, connect

select privilege from dba_sys_privs where grantee = 'RESOURCE';

PRIVILEGE
—————————————-
CREATE TRIGGER
CREATE SEQUENCE
CREATE TYPE
CREATE PROCEDURE
CREATE CLUSTER
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE

select privilege from dba_sys_privs where grantee = ‘CONNECT’;

PRIVILEGE
—————————————-
CREATE SESSION

How to check that deployed database setup is the RAC(Cluster Database) or NON-RAC(Single Instance) Database?

select decode(count(*), 0, 'No', 'Yes') RAC
from ( select 1
from v$active_instances
where rownum = 1 );

Attributes of RAC Database

su – oracle

OS# sqlplus / as sysdba

SQL> show parameter cluster_database
                          TRUE
SQL> show parameter cluster_database_instances
                         2

Attributes of NON-RAC Database

SQL> show parameter cluster_database
                              NULL
SQL> show parameter cluster_database_instances
                              NULL

Note:

If you don’t have DB sys password the you can check from the tnsnames.ora or oratab

$ cat $ORACLE_HOME/network/admin/tnsnames.ora
$ cat /etc/oratab or cat /var/opt/oracle/oratab

How to generate the HTML Report of any SQL Query.

Generate HTML Report from Database

SET SERVEROUTPUT ON
SQL>SPOOL ON;
SQL>SPOOL ‘REPORTNAME.HTML’; --Report Name
SQL>SET PAGESIZE 10000;
SQL>SET MARKUP HTML ON ENTMAP OFF;
SQL>SELECT * FROM DUAL; --Copy SQL Query
SQL>SPOOL OFF;

Find the Specified Report from Home Location

OS PROMPT:
ls -ltr
ftp mget

How to display the SQL Query result in single line?

clear columns
set colsep "|"
set linesize 32767
set pagesize 0
COLUMN MYUSER_IDENTITY 	        FORMAT A20
COLUMN MYCUI 			FORMAT A20
COLUMN MYRADIUSPOLICY 		FORMAT A30
COLUMN MYCUSTOMERREPLYITEM	FORMAT A50

SELECT MYUSER_IDENTITY,MYCUI,MYRADIUSPOLICY,MYCUSTOMERREPLYITEM FROM MYRADIUSCUSTOMER;

Reference
http://www.adp-gmbh.ch/ora/sqlplus/column.html
http://barrymcgillin.blogspot.in/2011/09/sqlplus-formatting-commands.html

Find the Database Sessions by schema name wise.

SQL> SELECT USERNAME,STATUS,COUNT(1) FROM V$SESSION GROUP BY USERNAME,STATUS;

---RAC Database

SQL> SELECT USERNAME,STATUS,COUNT(1) FROM GV$SESSION GROUP BY USERNAME,STATUS;

Find the OSUSER and MACHINE name of sessions

SELECT USERNAME,STATUS,OSUSER,MACHINE,COUNT(1) FROM GV$SESSION GROUP BY USERNAME,STATUS,OSUSER,MACHINE;

Note: desc V$SESSION

--RAC Database

SELECT USERNAME,STATUS,OSUSER,MACHINE,COUNT(1) FROM GV$SESSION GROUP BY USERNAME,STATUS,OSUSER,MACHINE;

Note: desc GV$SESSION

How to check the Database sessions,process and open cursor parameters?

OS# sqlplus / as sysdba
SQL> show parameter sessions
SQL> show parameter processes
SQL> show parameter open_cursors

--check the database parameters

SQL> show parameter

Verify the size of datafiles

sqlplus / as sysdba

SQL> SELECT (BYTES/1024)/1024,NAME FROM V$DATAFILE;

SQL> SELECT FILE_NAME,TABLESPACE_NAME,(BYTES/1024)/1024,STATUS,AUTOEXTENSIBLE FROM DBA_DATA_FILES;

How to find the DDL of any Specific Database Object (e.g TABLE,VIEW,SEQUENCE etc …?)

set long 10000
set pagesize 5000
select dbms_metadata.get_ddl('TABLE','EMP') from dual;

How to find the DDL of INDEX of Specific TABLE?

set long 10000
set pagesize 5000
select dbms_metadata.get_dependent_ddl('INDEX','EMP') from dual;

Check the Database Server Status

Check the Database Mode

OS#sqlplus / as sysdba

SQL>select open_mode from v$database;
SQL>exit;

    Check Oracle Listener Status

OS#lsnrctl status

#Check Oracle BG Process
OS# ps -ef | grep ora_
OR
OS# ps -ef | grep -i xe_

How to find the Database Name from any Server?

Linux/Solaris  .bash_profile OR .profile

echo $ORACLE_SID

Solaris  cat /var/opt/oracle/oratab
Linux    cat /etc/oratab

Tablespace and User Administration

---drop the user

          DROP USER MYUSER CASCADE;

--drop the tablespace

          DROP TABLESPACE MYUSER INCLUDING CONTENTS AND DATAFILES;

---Verify the path of the datafile

          SELECT NAME FROM V$DATAFILE;

          echo $ORACLE_BASE/oradata/$ORACLE_SID/

--Create the tablespace for single instance database

--This is not for RAC Database

         CREATE TABLESPACE MYUSER DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/MYUSER.dbf' size 100M AUTOEXTEND ON;

---Create the User

         CREATE USER MYUSER IDENTIFIED BY MYUSER DEFAULT TABLESPACE MYUSER TEMPORARY TABLESPACE temp
         QUOTA UNLIMITED ON MYUSER;

--Give the privileges to user.

         GRANT CONNECT,RESOURCE, CREATE ANY VIEW TO MYUSER;

How to find the location of Tablespace’s Datafile ?

               SELECT NAME FROM V$DATAFILE;

How to check that Tablespace is AutoExtend or not?

             SELECT TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES;

How to Add New Datafile to Existing Tablespace?

         ALTER TABLESPACE MYUSER
              ADD DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/MYUSER02.dbf' SIZE 100M AUTOEXTEND ON;

How to find the Specific Oracle Data Dictionary ?

SELECT * FROM DICTIONARY;

SQL> desc DICTIONARY
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TABLE_NAME                                         VARCHAR2(30)
COMMENTS                                           VARCHAR2(4000)

To find database redo load per day.

select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"
from v$archived_log
group by trunc(completion_time)
order by 1 desc;

How to monitor the Backup ?

How to monitor datapump backup?

   SQL> select sid, serial#, sofar, totalwork,dp.owner_name, dp.state, dp.job_mode
	from gv$session_longops sl, gv$datapump_job dp
	where sl.opname = dp.job_name and sofar != totalwork;

How to monitor the rman backup?

   SQL>alter session set nls_date_format='dd/mm/yy hh24:mi:ss';

   SQL> select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
	sysdate + TIME_REMAINING/3600/24 end_at
	from v$session_longops
	where totalwork > sofar
	AND opname NOT LIKE '%aggregate%'
	AND opname like 'RMAN%';

Backup Completion Time

set colsep “|”
set linesize 32767
set pagesize 10000
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds                        seconds
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

How to Kill the app_username sessions?

SELECT ‘ALTER SYSTEM KILL SESSION ‘ ||chr(39) ||s.sid||chr(44)||s.serial#||chr(39)||’ immediate;’
FROM v$session s JOIN v$process p ON p.addr = s.paddr
WHERE s.type != ‘BACKGROUND’
AND s.username = ‘&app_username’;

SQL> SELECT 'ALTER SYSTEM KILL SESSION  ' ||chr(39) ||s.sid||chr(44)||s.serial#||chr(39)||' immediate;'
  2  FROM   v$session s
  3         JOIN v$process p ON p.addr = s.paddr
  4  WHERE  s.type != 'BACKGROUND'
  5  AND s.username = '&app_username';
Enter value for app_username: TESTING
old   5: AND s.username = '&app_username'
new   5: AND s.username = 'TESTING'

'ALTERSYSTEMKILLSESSION'||CHR(39)||S.SID||CHR(44)||S.SERIAL#||CHR(39)||'IMMEDIAT
--------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION  '1093,186' immediate;
ALTER SYSTEM KILL SESSION  '1067,260' immediate;

More....

Check Database Characteristics
set colsep “|”
set linesize 32767
set pagesize 10000
COLUMN PROPERTY_NAME FORMAT A25
COLUMN PROPERTY_VALUE FORMAT A25
COLUMN DESCRIPTION FORMAT A100
SELECT * FROM database_properties;

Check Profile
set colsep “|”
set linesize 32767
set pagesize 10000
COLUMN PROFILE FORMAT A25
COLUMN RESOURCE_NAME FORMAT A25
COLUMN RESOURCE_TYPE FORMAT A25
COLUMN LIMIT FORMAT A20
SELECT * FROM dba_profiles;
–ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Check DB Directory
set colsep “|”
set linesize 32767
set pagesize 10000
COLUMN OWNER FORMAT A25
COLUMN DIRECTORY_NAME FORMAT A25
COLUMN DIRECTORY_PATH FORMAT A25
SELECT * FROM DBA_DIRECTORIES;
–GRANT READ,WRITE ON DIRECTORY DB_DUMP TO scott;

Check Datafile
set colsep “|”
set linesize 32767
set pagesize 10000
COLUMN NAME FORMAT A50
COLUMN STATUS FORMAT A15
COLUMN ENABLED FORMAT A15
COLUMN BYTES FORMAT 9999999999
SELECT NAME,STATUS,ENABLED,BYTES FROM V$DATAFILE;

user source
set colsep “|”
set linesize 32767
set pagesize 10000
COLUMN NAME FORMAT A25
COLUMN TYPE FORMAT A25
COLUMN LINE FORMAT 99999
COLUMN TEXT FORMAT A200
select NAME,TYPE,LINE,TEXT from user_source;

Database Scheduler
set linesize 10000
SELECT JOB_NAME,NEXT_RUN_DATE,RUN_COUNT FROM USER_SCHEDULER_JOBS;

BEGIN
— Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job (
job_name => ‘JOB_AUTO_SESSION_CLOSER’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN PROC_AUTO_SESSION_CLOSER(); END;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=hourly;byminute=24;’,
end_date => NULL,
enabled => TRUE,
comments => ‘Job defined entirely by the CREATE JOB procedure.’);
END;
/

Database Schedule Job Details:-

set colsep “|”
set linesize 32767
set pagesize 10000
COLUMN OWNER             FORMAT A15
COLUMN JOB_NAME          FORMAT A25
COLUMN NEXT_RUN_DATE     FORMAT A20
COLUMN LAST_RUN_DURATION FORMAT A20
COLUMN MAX_RUN_DURATION  FORMAT A5
COLUMN RUN_COUNT         FORMAT 999
COLUMN COMMENTS          FORMAT A15
SELECT OWNER,JOB_NAME,NEXT_RUN_DATE,LAST_RUN_DURATION,MAX_RUN_DURATION,RUN_COUNT,COMMENTS FROM  DBA_SCHEDULER_JOBS;

USER TABLES
set colsep “|”
set linesize 32767
set pagesize 10000
COLUMN TABLE_NAME FORMAT A20
COLUMN TABLESPACE_NAME FORMAT A20
COLUMN INT_TRANS FORMAT 999
COLUMN STATUS FORMAT A10
COLUMN CACHE FORMAT A10
COLUMN MONITORING FORMAT A10
COLUMN COMPRESSION FORMAT A10
COLUMN IOT_TYPE FORMAT A10
COLUMN LAST_ANALYZED FORMAT A10
COLUMN PARTITIONED FORMAT A10
SELECT TABLE_NAME,TABLESPACE_NAME,INI_TRANS,STATUS,CACHE,MONITORING,COMPRESSION,IOT_TYPE,LAST_ANALYZED,PARTITIONED FROM USER_TABLES;

Count Tables list

SET SERVEROUTPUT ON
DECLARE
V_CNT INT:=0;
STMT VARCHAR(4000);
BEGIN

FOR CNT IN (SELECT TNAME FROM TAB ORDER BY TNAME)
LOOP
DBMS_OUTPUT.PUT_LINE(‘SELECT COUNT(1) FROM ‘|| CNT.TNAME);
STMT := ‘SELECT COUNT(1) FROM ‘|| CNT.TNAME;
EXECUTE IMMEDIATE STMT INTO V_CNT;
DBMS_OUTPUT.PUT_LINE(‘COUNT:==> ‘|| V_CNT);
END LOOP;

END;
/

Check the Total cursors open, by session
===========
RAC
===========
set colsep “|”
set linesize 32767
set pagesize 10000
COLUMN username FORMAT A20
COLUMN sid FORMAT 99999999
COLUMN serial FORMAT A10
COLUMN value FORMAT 99999999
select s.username, s.sid, s.serial#,a.value
from gv$sesstat a, gv$statname b, gv$session s
where a.inst_id = b.inst_id
and a.inst_id = s.inst_id
and a.statistic# = b.statistic# and s.sid=a.sid
and b.name = ‘opened cursors current’
order by s.username;

=================
Single Instance
=================
set colsep “|”
set linesize 32767
set pagesize 10000
COLUMN username FORMAT A20
COLUMN sid FORMAT 99999999
COLUMN serial FORMAT A10
COLUMN value FORMAT 99999999
select s.username, s.sid, s.serial#,a.value
from gv$sesstat a, gv$statname b, gv$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = ‘opened cursors current’
order by s.username;

Alert Log Monitoring
you can easily check for recent incidents from adrci with

adrci exec="show incident -all -orderby CREATE_TIME"

adrci> set home diag/rdbms/orcl/orcl adrci> SHOW ALERT -TAIL 3 -F
2013-07-31 13:18:43.041000 +05:30
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
2013-07-31 13:18:45.316000 +05:30
Starting background process QMNC
QMNC started with pid=31, OS id=30727
2013-07-31 13:18:47.587000 +05:30
Completed: ALTER DATABASE OPEN
2013-07-31 13:18:49.735000 +05:30
db_recovery_file_dest_size of 3882 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Starting background process CJQ0
CJQ0 started with pid=34, OS id=30741
2013-07-31 13:24:00.346000 +05:30
Starting background process SMCO
SMCO started with pid=22, OS id=30819

Syntax for Oracle10g

tail -100 $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
Example
tail -100 /u01/app/oracle/admin/eliteaaa/bdump/alert_eliteaaa.log

Syntax for Oracle11g

tail -100f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log
Example
tail -100f /u01/app/oracle/diag/rdbms/aaadb/aaadb1/trace/alert_airchn1.log

Identifying Open Uncommitted Transactions

==== RAC ==== select s.INST_ID, s.SID, s.SERIAL#,s.USERNAME,s.MACHINE from gv$transaction t , gv$session s where t.INST_ID = s.INST_ID and t.ses_addr = s.SADDR;

Check the Deadlock
======= RAC ======= prompt CTIME is in Seconds set lines 120 col BLOCK for 9 col LMODE for 9 col INST_ID for 9 col REQUEST for 9 col SID for 999999 select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK from gv$lock where (ID1,ID2,TYPE) in
(select ID1,ID2,TYPE from gv$lock where request>0);

DB Features used/not

Set feedback on
Set linesize 122
Col name             format a45     heading "Feature"
Col version          format a10     heading "Version"
Col detected_usages  format 999,990 heading "Detected|usages"
Col currently_used   format a06     heading "Curr.|used?"
Col first_usage_date format a10     heading "First use"
Col last_usage_date  format a10     heading "Last use"
Col nop noprint
Break on nop skip 1 on name
Select decode(detected_usages,0,2,1) nop,
       name, version, detected_usages, currently_used,
       to_char(first_usage_date,'DD/MM/YYYY') first_usage_date,
       to_char(last_usage_date,'DD/MM/YYYY') last_usage_date
from dba_feature_usage_statistics
where currently_used='TRUE'
order by nop, 1, 2
/

DB Options installed/not installed

Col name  format a50 heading "Option"
Col value format a5  heading "?"      justify center wrap
Break on value dup skip 1
Spool option
Select parameter name, value
from v$option
order by 2 desc, 1
/

DB active_session_history

set colsep “|”
set linesize 32767
set pagesize 10000
col event format a30
set pagesize 10000col sample_time format a25
select session_id, sample_time, session_state, event, wait_time, time_waited, sql_id, sql_child_number CH#
from v$active_session_history
where
user_id IN (SELECT USER_ID FROM DBA_USERS WHERE USERNAME ='&schema_name')
and sample_time between
    to_date('&from_date','dd-MON-yy hh24:mi:ss')
       and
    to_date('&to_date','dd-MON-yy hh24:mi:ss')
order by session_id, sample_time;

Find Invalid Objects

set colsep “|”
set linesize 32767
set pagesize 10000
COLUMN owner       FORMAT A25
COLUMN object_type FORMAT A25
COLUMN object_name FORMAT A25
COLUMN status      FORMAT A25
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

SELECT owner,
       object_type,
       status,
       count(1) CNT
FROM   dba_objects
WHERE  status = 'INVALID'
GROUP BY owner, object_type,status;

http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php

Find the Location of Datafile

SQL>
SELECT SUBSTR(NAME,1,INSTR(NAME,'/',-1)) DATA_FILE_LOCATION FROM V$DATAFILE WHERE NAME = (SELECT FILE_NAME FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME='&1');
Enter value for 1: USERS
old   2: WHERE TABLESPACE_NAME='&1')
new   2: WHERE TABLESPACE_NAME='USERS')

DATA_FILE_LOCATION
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/

SQL> /
Enter value for 1: SYSTEM
old   2: WHERE TABLESPACE_NAME='&1')
new   2: WHERE TABLESPACE_NAME='SYSTEM')

DATA_FILE_LOCATION
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/

SQL for DBA’s

dataguard
scheduler-jobs
generic-administration
performance

3 thoughts on “Oracle DB Admin-Data Dictionary”

chetangondaliya on March 1, 2014 at 9:39 am said:

this post is very useful to solver my query
thank u very much

Reply ↓
- hiteshgondalia on March 11, 2014 at 10:18 am said:
  
  Thanks Chetan
  
  Reply ↓
MT on February 4, 2016 at 12:52 am said:

======= Identifying Open Uncommitted Transactions with SQL Command ============
SELECT
O.OBJECT_NAME,
S.SID,
S.SERIAL#,
P.SPID,
S.PROGRAM,
SQ.SQL_FULLTEXT,
S.LOGON_TIME
FROM
V$LOCKED_OBJECT L,
DBA_OBJECTS O,
V$SESSION S,
V$PROCESS P,
V$SQL SQ
WHERE
L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;

— MT

Reply ↓

ORACLE-LEARN

Welcome to our oracle learning world !

Oracle DB Admin-Data Dictionary

How to check that deployed database setup is the RAC(Cluster Database) or NON-RAC(Single Instance) Database?

SQL for DBA’s

3 thoughts on “Oracle DB Admin-Data Dictionary”

Leave a Reply Cancel reply

How to check that deployed database setup is the RAC(Cluster Database) or NON-RAC(Single Instance) Database?

SQL for DBA’s

Like this:

3 thoughts on “Oracle DB Admin-Data Dictionary”

Leave a Reply Cancel reply