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 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
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 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;
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);
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 /
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;
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
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/
this post is very useful to solver my query
thank u very much
Thanks Chetan
======= 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