usefull query for database

set lines 100 pages 999
col ID format a15

select username

,      sid || ',' || serial# "ID"
,      status
,      last_call_et "Last Activity"
from   v$session
where  username is not null
order by status desc
,        last_call_et desc
/

Time since last user activity

set lines 100 pages 999

select username
,      floor(last_call_et / 60) "Minutes"
,      status
from   v$session
where  username is not null
order by last_call_et
/

Sessions sorted by logon time
 

set lines 100 pages 999

col ID               format a15
col osuser        format a15
col login_time format a14
select   username
,           osuser
,           sid || ',' || serial# "ID"
,           status
,           to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
,           last_call_et
from    v$session
where  username is not null
order   by login_time
/
 

Show user info including os pid

col "SID/SERIAL" format a10

col username format a15
col osuser format a15
col program format a40
select   s.sid || ',' || s.serial# "SID/SERIAL"
,           s.username
,           s.osuser
,           p.spid "OS PID"
,           s.program
from    v$session s,     v$process p
Where s.paddr = p.addr
order   by to_number(p.spid)
/

Show a users current sql

Select sql_text

from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value
        from v$session
        where username like '&username')
/

 

Session status associated with the specified os process id

select   s.username

,           s.sid
,           s.serial#
,           p.spid
,           last_call_et
,           status
from    V$SESSION s,   V$PROCESS p
where  s.PADDR = p.ADDR
and      p.spid='&pid'
/

 

All active sql

set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
for x in
(select username||'('||sid||','||serial#||') ospid = '|| process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address,
sql_hash_value
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null ) loop
for y in (select sql_text
from v$sqlarea
where address = x.sql_address ) loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/

 

Display any long operations

set lines 100 pages 999

col username format a15
col message format a40
col remaining format 9999

select   username
,           to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
,           time_remaining remaining
,           message
from    v$session_longops
where  time_remaining = 0
order by time_remaining desc
/

List open cursors per user

set pages 999

select   sess.username
,           sess.sid
,           sess.serial#
,           stat.value cursors
from    v$sesstat stat, v$statname sn, v$session sess
where  sess.username is not null
and      sess.sid = stat.sid
and      stat.statistic# = sn.statistic#
and      sn.name = 'opened cursors current'
order by value

/

DATABASE HEALTH CHECK REPORT

set feedback off
set serverout on
set wrap off
set pages 300
set lines 150
col file_name for a50
col name for a50
col member for a50
col file_id for a5
col "Percent Used" for a20
col segment_name for a30
col tablespace_name for a30
col STATUS for a16
col owner for a20
col table_name for a35
col index_name for a35
col owner for a20
col object_type for a20
col object_name for a30
column date_column new_value today_var
/
select to_char(sysdate,'yyyy-mm-dd-hh-mi-ssAM')date_column from dual
/
spool /home/oracle/DB_&today_var
PROMPT =============================================================
PROMPT DATABASE HEALTH CHECK REPORT
PROMPT =============================================================
PROMPT
PROMPT
PROMPT CURRENT DATE and TIME
PROMPT ======================
Select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "Current Date/Time" from dual;
PROMPT
PROMPT
PROMPT VERSION
PROMPT =================
select * from v$version;
PROMPT
PROMPT
PROMPT DATABASE NAME
PROMPT =================
Select name from v$database;
PROMPT
PROMPT
PROMPT Last Startup Time
PROMPT ========================
select to_char(STARTUP_TIME,'DD-Mon-YYYY  HH:MI AM') as last_start_time from v$instance;
PROMPT
PROMPT
PROMPT Users logged information
PROMPT ===============================
select status,count(*) from v$session where username not in ('SYS','SYSTEM') group by status;
PROMPT
PROMPT
PROMPT ToDay New Object Created count information
PROMPT ===============================
select count (*) "Total"  from dba_objects where  to_char(created,'DD-Mon-YY') = to_char(sysdate,'DD-Mon-YY');
PROMPT
PROMPT
PROMPT Previous Day New Object Created count information
PROMPT ===============================
select count (*) "Total"  from dba_objects where  to_char(created,'DD-Mon-YY') = to_char(sysdate-1,'DD-Mon-YY');
select owner,object_type,count(*) from dba_objects where  to_char(created,'DD-Mon-YY') = to_char(sysdate-1,'DD-Mon-YY') group by owner,object_type order by owner;
PROMPT
PROMPT
PROMPT Previous Day  New Object Created details
PROMPT ===============================
select owner,object_name,object_type,created from dba_objects where  to_char(created,'DD-Mon-YY') = to_char(sysdate-1,'DD-Mon-YY') order by owner;
PROMPT
PROMPT
PROMPT ToDay COmpiled objects count information
PROMPT ===============================
select count(*) from dba_objects where  to_char(last_ddl_time,'DD-Mon-YY') = to_char(sysdate,'DD-Mon-YY');
PROMPT
PROMPT
PROMPT Previous Day COmpiled objects count information
PROMPT ===============================
select count(*) from dba_objects where  to_char(last_ddl_time,'DD-Mon-YY') = to_char(sysdate-1,'DD-Mon-YY');
select owner,object_type,count(*) from dba_objects where  to_char(last_ddl_time,'DD-Mon-YY') = to_char(sysdate-1,'DD-Mon-YY') group by owner,object_type order by owner;
PROMPT
PROMPT
PROMPT  Previous Day COmpiled objects detailed information
PROMPT ======================================================
select owner,object_name,object_type,last_ddl_time from dba_objects where  to_char(last_ddl_time,'DD-Mon-YY')=to_char(sysdate-1,'DD-Mon-YY')order by owner;
PROMPT
PROMPT
PROMPT Previous day archive logs generation
PROMPT ======================================
select count(*) from v$archived_log where to_char(COMPLETION_TIME,'DD-Mon-YY')=to_char(sysdate-1,'DD-Mon-YY');
PROMPT
PROMPT
PROMPT latest Archive No.
PROMPT ======================================
archive log list
PROMPT
PROMPT
PROMPT Instance Pools and memory companents detail
PROMPT =============================================
Show parameter pool
show parameter pga
show parameter sort
sho parameter cache
PROMPT
PROMPT
PROMPT CONTROL FILES
PROMPT ==================
select * from v$controlfile;
PROMPT
PROMPT
PROMPT LOG FILE GROUPS
PROMPT ==================
select * from v$logfile;
PROMPT
PROMPT
PROMPT LOG FILE MEMBERS
PROMPT ==================
select * from v$log;
PROMPT
PROMPT
PROMPT TABLESPACES AND DATAFILES
PROMPT ============================
select TABLESPACE_NAME,to_char(file_id,'999')"File_id",FILE_NAME,BYTES/1024/ 1024 "Size in MB",STATUS from dba_data_files order by TABLESPACE_NAME,file_name;
PROMPT
PROMPT
PROMPT UTILIZATION OF TABLESPACES
PROMPT ============================
PROMPT
PROMPT
PROMPT TABLESPACES utl >85
PROMPT ============================
COLUMN TABLESPACE FORMAT A15
select t.tablespace,t.totalspace as "Totalspace(MB)",round((t.totalspace-nvl (fs.freespace,0)),2) as "Used Space(MB)",nvl(fs.freespace,0)as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as"%Used",round ((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free" from(select round(sum (d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name)t,(select round(sum(f.bytes)/ (1024*1024))as freespace,f.tablespace_name tablespace from dba_free_space f
group by f.tablespace_name)fs where t.tablespace=fs.tablespace (+)and round (((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2)>=85 order by t.tablespace
/
PROMPT
PROMPT TABLES THAT CANNOT GET NEXT EXTENT DUE TO EXCEEDING MAXEXTENTS IN TABLE STORAGE PARAMETER
PROMPT =========================================================================================
rem Check whether next extent size excede maxextents value in table storage parameter
select dt.owner,dt.table_name, ds.next_extent, dt.max_extents
from dba_segments ds, dba_tables dt
where ds.segment_name = dt.table_name and
ds.next_extent > dt.max_extents * (select value from v$parameter
where name = 'db_block_size');
PROMPT
PROMPT
PROMPT INDEXES THAT CANNOT GET NEXT EXTENT DUE TO EXCEEDING MAXEXTENTS IN INDEX STORAGE PARAMETER
PROMPT =========================================================================================
rem Check whether next extent size excede maxextents value in index storage parameter
select di.owner,di.index_name, ds.next_extent, di.max_extents
from dba_segments ds, dba_indexes di
where ds.segment_name = di.index_name and
ds.next_extent > di.max_extents * (select value from v$parameter
where name = 'db_block_size');
PROMPT
PROMPT
PROMPT OBJECTS WHOSE STATUS ARE INVALID
PROMPT ===================================
Rem Check object status
select OBJECT_NAME, owner, object_type, STATUS from all_objects where
object_type in
('FUNCTION','INDEX', 'LIBRARY','PACKAGE','PACKAGE BODY',
'PROCEDURE', 'SEQUENCE','SYNONYM','TABLE','TRIGGER',
'TYPE','UNDEFINED','VIEW')
and status = 'INVALID'
and OWNER not in ('SYS','SYSTEM')
and status is not null ;
PROMPT
PROMPT
PROMPT FILES THAT NEEDS RECOVERY
PROMPT =============================
Rem Check wether there is any data file that needs media recovery
select * from v$recover_file;
PROMPT
PROMPT
PROMPT LIBRARY CACHE HIT RATIO. THIS VALUE SHOULD BE GREATER 95%
PROMPT ===========================================================
rem get library cache hit ratio
select sum(pins)/(sum(pins)+sum(reloads))*100 "Hit Ratio"
from v$librarycache;
PROMPT
PROMPT
PROMPT DICTIONARY HIT RATIO. THIS VALUE SHOULD BE GREATER 85%
PROMPT ==========================================================
rem get dictionary hit ratio. Keep sum(gets)/sum(getmisses)
rem greater than 85%
select (1-(sum(getmisses)/sum(gets)))*100 "Hit Ratio"
from v$rowcache;
PROMPT
PROMPT
PROMPT DICTIONARY CACHE PIN HIT RATIO STATISTICS
PROMPT ===========================================
select parameter,gets,getmisses,scans,scanmisses from v$rowcache;
PROMPT
PROMPT
PROMPT DATABASE BUFFER HIT RATIO. THIS VALUE SHOULD BE GREATER 95%
PROMPT =============================================================
select name,value from v$sysstat
where name in ('db block gets','consistent gets','physical reads');
PROMPT
DECLARE
phy_read INTEGER;
db_get INTEGER;
con_get INTEGER;
db_ratio NUMBER(7,4);
BEGIN
SELECT VALUE INTO phy_read FROM V$SYSSTAT WHERE name = 'physical reads';
SELECT VALUE INTO db_get FROM V$SYSSTAT WHERE name = 'db block gets';
SELECT VALUE INTO con_get FROM V$SYSSTAT WHERE name = 'consistent gets';
db_ratio := (1.0 - (phy_read / (db_get + con_get)))*100;
DBMS_OUTPUT.put_line('DB Buffer Get Ratio: '||TO_CHAR(db_ratio));
END;
/
PROMPT
PROMPT
PROMPT Rollback HIT RATIO. THIS VALUE SHOULD BE GREATER 95%
PROMPT ===========================================================
select b.NAME,round(((GETS-WAITS)*100)/GETS,2) hit_ratio
from v$rollstat a,v$rollname b
where a.USN = b.USN;
PROMPT
PROMPT
PROMPT STATISTICS OF SORTS
PROMPT =======================
rem get statistics of sorts
select name,value from v$sysstat where name in
('sorts (memory)', 'sorts (disk)');
PROMPT
PROMPT
PROMPT Database users detail
PROMPT =====================
select username,default_tablespace,temporary_tablespace from dba_users;
PROMPT
PROMPT
PROMPT Tables Statistics detail
PROMPT =====================
select owner as "SCHEMA", min(last_analyzed) as "Last Analyzed Date" from dba_tables
where owner not like '%SYS%' group by owner;
PROMPT
PROMPT
PROMPT Indexes Statistics detail
PROMPT =====================
select owner as "SCHEMA", min(last_analyzed) as "Last Analyzed Date" from dba_indexes
where owner not like '%SYS%' group by owner;
PROMPT
PROMPT
PROMPT  Total Objects
PROMPT =========================
select owner as "SCHEMA",count(*) from dba_objects
group by owner;
PROMPT
PROMPT
PROMPT Invalid Objects detail
PROMPT =========================
select owner as "SCHEMA", object_type,count(*) from dba_objects
where status='INVALID' group by owner,object_type;
PROMPT
PROMPT
PROMPT Invalid Objects detail
PROMPT =========================
select owner as "SCHEMA", object_type,object_name from dba_objects
where status='INVALID' group by owner,object_type;
select count(*)  "TOTAL INVALID OBJECTS" from dba_objects where status='INVALID';
PROMPT
PROMPT
PROMPT  Session Detail
PROMPT ===========================
select count(*) "Total Session" from v$session;
select count(*) "Active Session" from v$session where status='ACTIVE';
PROMPT
PROMPT
PROMPT SGA SUMMARY
PROMPT ==============
rem check sga
select * from v$sga;
PROMPT
PROMPT
PROMPT SGA STATISTICS
PROMPT =================
rem get more detail of SGA
select * from v$sgastat;
PROMPT
PROMPT
PROMPT ARCHIVE SUMMARY
PROMPT =================
Total Archive Gentarion
select count(*) "Total Archive ", round((sum(blocks*block_size)/1024/1024),2)||' MB' "Size MB"
from v$archived_log where  completion_time > sysdate-1;
Spool off


 

Logical Backup for Oracle Database using export and import Utility

Export (exp), Import (imp) are Oracle utilities which allow you to write data in an ORACLE-binary format from the database into operating system files and to read data back from those operating system files.

Imports/Exports Modes:--

1)Full Export/Import
2)Tablespace
3)User and
4)Table (With partition).

Export and Import Modes

The Export and Import utilities support four modes of operation:

Full: Exports and imports a full database. Only users with the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles can use this mode. Use the FULL parameter to specify this mode.

Tablespace: Enables a privileged user to move a set of tablespaces from one Oracle database to another. Use the TRANSPORT_TABLESPACE parameter to specify this mode.

User: Enables you to export and import all objects that belong to you (such as tables, grants, indexes, and procedures). A privileged user importing in user mode can import all objects in the schemas of a specified set of users. Use the OWNER parameter to specify this mode in Export, and use the FROMUSER parameter to specify this mode in Import.

Table: Enables you to export and import specific tables and partitions. A privileged user can qualify the tables by specifying the schema that contains them. Use the TABLES parameter to specify this mode.



*** Before executing the exp/imp scripts use the nohup command in $ prompt.***
$nohup

If the size of the undo tablespace is small then change the size of the undo tablespace i.e increase the size of the undo tablespace.

Options for Exp Utility:--

Exp Option Default Value Description

userid =======>> username/password

buffer ========>> Specifies the size, in bytes, of the buffer (array) used to insert the data OR Specifies the size, in bytes, of the buffer used to fetch the rows. If 0 is specified, only one row is fetched at a time. This parameter only applies to conventional (non direct) exports.

"buffer_size = rows_in_array * maximum_row_size"

compress ======>> Y When “Y”, export will mark the table to be loaded as one extent OR import into one extent (Y).


Consistent =======>> N Specifies the set transaction read only statement for export, ensuring data consistency.

This option should be set to “Y” if activity is anticipated while the exp command is executing. If ‘Y’ is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error. Or cross-table consistency(N).



Constraints ========>> Y Specifies whether table constraints should be exported with table data.

direct ===========>> N Determines whether to use direct or conventional path export. Direct path exports bypass the SQL command, thereby enhancing performance (Direct=Y).

File ===============>> The name of the export file. Multiple files can be listed, separated by commas. When export fills the filesize, it will begin writing to the next file in the list.

Full =========>> N The entire database is exported,if full=y.
grants ======>>> Y Specifies object grants to export.
log =========>>> log file of screen output
owner ==========>>> Only the owner’s objects will be exported.
parfile ===========>>> parameter filename .
query ==========>>> Allows a subset of rows from a table to be exported, based on a SQL, where clause.

rows ==========>>> Y export data rows. If (rows=N),it will export the objects without data.

statistics ===========>>> ESTIMATE Indicates the level of statistics generated when the data is imported. Other options include COMPUTE and NONE.

tables =======>>> Indicates that the type of export is tablespace-mode, in which all tables assigned to the listed tablespaces will be exported. This option requires the EXP_FULL_DATABASE role.
OR
list of table names.

You can see all the parameters (options) of the exp utility with the help of this command:--
$exp help=Y

for Imp Utility:--

Options:--

USERID =========>> username/password
BUFFER ========>> size of data buffer
IGNORE =========>> ignore create errors (N), Mostly Ignore=Y is taken.

If IGNORE=y, object creation errors are not reported. The database object is not replaced. If the object is a table, rows are imported into it. Note that only object creation errors are ignored; all other errors (such as operating system, database, and SQL errors) are reported and processing may stop.

COMMIT=======> y prevents rollback segments from growing inordinately large and improves the performance of large imports. COMMIT=y is advisable if the table has a uniqueness constraint

default commit========>> N.



FILE ==========>> input files (EXPDAT.DMP).
FILESIZE =============>> maximum size of each dump file.
FROMUSER ==========>> list of owner usernames.
TOUSER ============>> list of usernames.
LOG ========>>> log file of screen output.
PARFILE =======>>> parameter filename.

These above are the important parameters of Exp/imp utility options.

ORA-01555: Snapshot Too Old

ORA-01555: Snapshot Too Old

UNDO is the backbone of the READ CONSISTENCY mechanism provided by Oracle. Multi-User Data Concurrency and Read Consistency mechanism make Oracle stand tall in Relational Database Management Systems (RDBMS) world .

Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries. This is done by setting the UNDO_RETENTION parameter. The default is 900 seconds (5 minutes), and we can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.
Why ORA-01555 error occur?

1.) Oracles does this by reading the "before image" of changed rows from the online undo segments. If we have lots of updates, long running SQL i.e , rollback records needed by a reader fo consistent read are overwritten by other writers.

2.) It may also due small size of undo and small undo_retention period .

To solve this issues we need to increase the undo tablepsace and undo retention period. Now the issue is how much should be the optimal value of undo retention and undo tablespace. For this we use the advisor. By using OEM, it is quite easy to estimate the size and time duration of undo.

Calculate Optimal Undo_Retention :
The following query will help us to optimize the UNDO_RETENTION parameter :


Optimal Undo Retention = Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_SEC)

To calculate Actual Undo Size :

SQL> SELECT SUM(a.bytes)/1024/1024 "UNDO_SIZE_MB"
FROM v$datafile a, v$tablespace b,dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

Undo Blocks per Second :

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"

FROM v$undostat ;
DB Block Size :

SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [Byte]" FROM v$parameter
WHERE name = 'db_block_size';

We can do all in one query as

SQL> SELECT d.undo_size/(1024*1024) “ACT_UNDO_SIZE [MB]“,
SUBSTR(e.value,1,25) “ UNDO_RTN [Sec] “,
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) “OPT_UNDO_RET[Sec]”
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
ACT_UNDO_SIZE [MB] UNDO_RTN [Sec] OPT_UNDO_RET[Sec]

------------------------ ---------------------- ----------------------------
50 900 24000

Calculate Needed UNDO Size :

If we are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for us (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity :

Formula :Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_PER_SEC

Here again we can find in a single :

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/

We can avoid ORA-01555 error as follows :

1.) Do not run discrete transactions while sensitive queries or transactions are running, unless we are confident that the data sets required are mutually exclusive.

2.) Schedule long running queries and transactions out of hours, so that the consistent gets will not need to rollback changes made since the snapshot SCN. This also reduces the work done by the server, and thus improves performance.
3.) Code long running processes as a series of restartable steps.

4.) Shrink all rollback segments back to their optimal size manually before running a sensitive query or transaction to reduce risk of consistent get rollback failure due to extent deallocation.

5.) Use a large optimal value on all rollback segments, to delay extent reuse.

6.) Don't fetch across commits. That is, don't fetch on a cursor that was opened prior to the last commit, particularly if the data queried by the cursor is being changed in the current session.

7.) Commit less often in tasks that will run at the same time as the sensitive query, particularly in PL/SQL procedures, to reduce transaction slot reuse.