Users Online

· Guests Online: 32

· Members Online: 0

· Total Members: 188
· Newest Member: meenachowdary055

Forum Threads

Newest Threads
No Threads created
Hottest Threads
No Threads created

Latest Articles

Articles Hierarchy

Database OAM

Database OAM

 

 

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

  1. Attributes of RAC Database

su – oracle

OS# sqlplus / as sysdba

SQL> show parameter cluster_database 		
                          TRUE
SQL> show parameter cluster_database_instances      	
                         2
  1. 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

 

Advertisements
REPORT THIS AD

 

 

 

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

 

 

How to check the Database Alert logs?

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

 

 

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)

 

 

Identifying Open Uncommitted Transactions

Note: RAC Database

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;

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 DataPump.

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

 

 

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;

Comments

No Comments have been Posted.

Post Comment

Please Login to Post a Comment.

Ratings

Rating is available to Members only.

Please login or register to vote.

No Ratings have been Posted.
Render time: 0.75 seconds
10,798,762 unique visits