Oracle Tablespaces And Datafiles

Contents of this Post

1. Check Tablespace Size Details
2. DataFile AUTOEXTENSIBLE Info
3. Check Max/Min Size Of DataFile In Tablespace
4. Tablespace & Datafile Creation Time
5. Tablespace Report With Space MARK
6. TEMPORARY Tablespace
7. UNDO Tablespace

Permanent Tablespace Size

set lines 200 pages 200
col TABLESPACE_NAME for a25
col "FREE%" for a10
col "USED%" for a10
col STATUS for a7

Select a.TABLESPACE_NAME,round(a.BYTES/1073741824,2) AVAIL_GB,round(b.BYTES/1073741824,2) USED_GB,round(c.BYTES/1048576,2) FREE_MB,
round((b.BYTES*100)/a.BYTES,2)||'%' "USED%", round((c.BYTES*100)/a.BYTES,2)||'%' "FREE%"
,NVL2(to_char(a.BYTES),'ONLINE','OFFLINE') STATUS
from sm$ts_avail a,sm$ts_used b,sm$ts_free c
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
and a.TABLESPACE_NAME=c.TABLESPACE_NAME(+);

OR

select TABLESPACE_NAME, sum(BYTES)/(1024*1024) SIZE_MB from dba_free_space group by TABLESPACE_NAME;

select sum(BYTES)/(1024*1024) SIZE_MB from dba_free_space where TABLESPACE_NAME='UNDOTBS';

Datafile Location
column FILE_NAME for A55
select FILE_ID,FILE_NAME, bytes/(1024*1024) Size_MB from dba_data_files where TABLESPACE_NAME='UNDOTBS' order by FILE_ID;

Resize or Add Datafile to a Tablespace
ALTER TABLESPACE DATA_B ADD DATAFILE '/u02/oradata/SOA/PROD_soainfra14.dbf' SIZE 2G; (9i DB doesn't support size in GB)

ALTER DATABASE DATAFILE '/u02/oradata/SOA/PROD_soainfra13.dbf' RESIZE 5G;


DataFile + AUTOEXTENSIBLE Info

col TABLESPACE_NAME for a15
col "File Name" for a60
col "AutoEx Remaining GB" for a19
col STATUS for a10
col "AutoEx" for a6

SELECT to_char(FILE_ID,99)||') '||FILE_NAME "File Name",TABLESPACE_NAME,round(BYTES/1024/1024,2) "Size MB", STATUS, AUTOEXTENSIBLE "AutoEx"
,round(USER_BYTES/1024/1024,2) "UsrMB",(INCREMENT_BY*8192)/1024/1024 "AutoEx By Mb"
,round(MAXBYTES/1024/1024/1024,2) "AutoEx MaxGB", DECODE(AUTOEXTENSIBLE,'NO','N/A'
,round((MAXBYTES-BYTES)/1024/1024/1024,2)) "AutoEx Remaining GB"
from dba_data_files
--where TABLESPACE_NAME='VWSE_DATA'
--Where AUTOEXTENSIBLE ='YES' -- in ('DST_ADMIN_DATA','DST_ADMIN_INDEX')
Order by TABLESPACE_NAME,FILE_ID;

Identify Max/Min Size Of DataFile In Tablespace

col FILE_NAME for a50
col ID for 9999
col TABLESPACE_NAME for a20

select TO_CHAR(FILE_ID,99) ID

,FILE_NAME,TABLESPACE_NAME,Tot_Gb,AUTOEXTENSIBLE

,INCREMENT_BY,Max_Gb,Usr_Mb
,DECODE(Min_File_Size,1,'Min Size DataFile','Max Size DataFile') "File_Desc"
from (
select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1073741824 Tot_Gb, AUTOEXTENSIBLE,INCREMENT_BY,
MAXBYTES/1073741824 Max_Gb,USER_BYTES/1048576 Usr_Mb
,ROW_NUMBER () OVER(PARTITION BY TABLESPACE_NAME ORDER BY BYTES) Min_File_Size
,ROW_NUMBER () OVER(PARTITION BY TABLESPACE_NAME ORDER BY BYTES Desc) Max_File_Size
from dba_data_files
--WHERE TABLESPACE_NAME in ('DATA','INDEX','EX_DATA'))
WHERE Min_File_Size = 1                                        -- Enable To Select Minimun Size Of Datafile
--WHERE Max_File_Size=1                                      -- Enable To Select Maximun Size Of Datafile
ORDER BY TABLESPACE_NAME;


Tablespace & Datafile Creation Time

SET LINE 150 pages 200
col FileNo for 99999
col "Tablespace Name" for a20
col "Datafile Name" for a50
col "DataFile Size" for a13
col "Size In MB" noprint
BREAK ON "Tablespace_Created"
TTITLE CENTER 'T A B L E S P A C E  A N D  D A T A F I L E  C R E A T I O N  T I M E' SKIP 1 CENTER ===================================================================== SKIP 1

SELECT FILE# FileNo,b.NAME "Tablespace Name"

,to_char(c.Tblsp_Creation_Time,'ddth-Mon-YY hh24:mi') "Tablespace_Created",
a.NAME "Datafile Name"

, TO_CHAR(CREATION_TIME,'ddth-Mon-YY hh24:mi') "Datafile_Created"

, BYTES/1024/1024 "Size In MB",
decode(sign(length(BYTES)-10),-1,lpad(BYTES/1024/1024,3)||' MB'

,lpad(BYTES/1024/1024/1024,3)||' GB') "DataFile Size",
STATUS,ENABLED
from v$datafile a, v$tablespace b,
    (select b.NAME Tablespace

    ,min(CREATION_TIME) Tblsp_Creation_Time from v$datafile a

    ,v$tablespace b where a.TS#=b.TS# group by b.NAME) c
WHERE a.TS#=b.TS#
AND   b.NAME=c.Tablespace
order by b.NAME,CREATION_TIME;
clear breaks;

Data File Status And Creation Time

col TABLESPACE for a12
Select    a.NAME

             ,b.NAME TABLESPACE

             ,TO_CHAR(CREATION_TIME,'dd-Mon-yy hh24:mi:ss') CREATION_TIME

             ,STATUS,ENABLED

             ,LAST_TIME

             ,ONLINE_TIME
From    v$datafile a, v$tablespace b

Where  a.TS# = b.TS#

And      b.NAME ='UNDOTBS';


Tablespace Report With Space MARK

set lines 200 pages 200
col TABLESPACE_NAME for a25
col "FREE%" for a10
col "USED%" for a10
col STATUS for a7

Select a.TABLESPACE_NAME

,round(a.BYTES/1073741824,2) AVAIL_GB

,round(b.BYTES/1073741824,2) USED_GB

,round(c.BYTES/1048576,2) FREE_MB

,round((b.BYTES*100)/a.BYTES,2)||'%' "USED%"

, round((c.BYTES*100)/a.BYTES,2)||'%' "FREE%"
,NVL2(to_char(a.BYTES),NULL,'OFFLINE') STATUS

,DECODE(sign((TO_NUMBER(c.BYTES)/1048576) -100) ,-1,'<<==== '||c.TABLESPACE_NAME,NULL) LOW_SPACE_MARK
from sm$ts_avail a,sm$ts_used b,sm$ts_free c
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
and a.TABLESPACE_NAME=c.TABLESPACE_NAME(+)
Order by LOW_SPACE_MARK;


Temporary Tablespace (Check separate Post for TEMPORARY Tablespace)

select
FILE_NAME
,TABLESPACE_NAME,BYTES/1024/1024/1024 "GB"
,STATUS
,AUTOEXTENSIBLE
,(INCREMENT_BY*8192)/1024/1024 INCREMENT_Mb
,MAXBYTES/1024/1024/1024 "MaxGB"
,USER_BYTES/1024/1024/1024 "UsrGB"
from dba_temp_files where TABLESPACE_NAME = 'DST_TEMP';

Check For Auto extendable
select tablespace_name, substr(file_name,1,60) file_name,  status,  bytes, AUTOEXTENSIBLE
FROM dba_data_files where tablespace_name ='STATSPACK';


UNDO TABLESPACE (Check separate Post for UNDO Tablespace)

select * from dba_data_files where tablespace_name like '%UNDO%';

select sum(BYTES)/1024/1024/1024 "UNDO DATAFILE In GB"

from dba_data_files where tablespace_name ='UNDO';

Unset UNDO Tablespace
alter system set undo_tablespace = '';
alter system set undo_tablespace = '' scope=memory;

Drop UNDO Tablespace
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

drop tablespace undo_old including contents;

Create/Manage UNDO Tablespace

create undo tablespace UNDO datafile '/apps/data/undo02.dbf' size 3072m;
alter tablespace UNDO add datafile '/apps/data/undo02.dbf' size 3072m;

alter database datafile '/apps/data/undo06.dbf' offline drop;
alter database datafile '/apps/data/undo06.dbf' online;
recover datafile '/apps/data/undo06.dbf';

 

Scripts For Oracle Segments/Tables

Contents of this Post

1. Schema Size
2. Table Size
3. BLOB column Size
4. Data File for Table
5. Last DML on Table
6. Missing Columns in 2 Table
7. Extract TABLE Creation Scripts
8. TOP Table/Index Reads
9. MAX_EXTENTS of Table/Index

What is Segment, Tablespaces, Schema And Datafiles

A segment is a database object that has space allocated to it. It is a logical structure of the database Segment is nothing but a table/index/mview etc. A segment consists of one or more extents allocated within a tablespace. Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.

Although databases, tablespaces, datafiles, and segments are closely related, they have important differences:

Databases and Tablespaces : An Oracle database is comprised of one or more logical storage units called tablespaces. The database's data is collectively stored in the database's tablespaces.

Tablespaces and Datafiles : Each tablespace in an Oracle database is comprised of one or more operating system files called datafiles. A tablespace's datafiles physically store the associated database data on disk.

Databases and Datafiles : A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. A more complicated database might have three tablespaces, each comprised of two datafiles (for a total of six datafiles).

Schema objects, Segments, and Tablespaces : When a schema object such as a table or index is created, its segment is created within a designated tablespace in the database. For example, suppose you create a table in a specific tablespace using the CREATE TABLE command with the TABLESPACE option. Oracle allocates the space for this table's data segment in one or more of the datafiles that constitute the specified tablespace. An object's segment allocates space in only one tablespace of a database. See Chapter 3, "Data Blocks, Extents, and Segments", for more information about extents and segments and how they relate to tablespaces.

Types of segment:-

• CLUSTER
• INDEX
• INDEX PARTITION
• LOB PARTITION
• LOBINDEX
• LOBSEGMENT
• NESTED TABLE
• ROLLBACK
• TABLE/DATA
• TABLE PARTITION
• TYPE2 UNDO

SCHEMA Size

set lines 200 pages 200
column Bytes format 9,999,999,999;
break on owner skip 1;
compute Sum of "Size In MB" on OWNER
select owner, segment_type, sum(bytes)/1024/1024 "Size In MB"
from dba_segments
--where owner in ('SCOOT','ADMIN')
group by owner, segment_type
order by 1;

clear breaks
clear compute


Table Size

SELECT    owner, table_name, TRUNC(sum(bytes)/1024/1024) SIZE_In_MB
FROM     (SELECT segment_name table_name, owner, bytes 
    FROM dba_segments  WHERE segment_type = 'TABLE' 
    UNION ALL 
    SELECT i.table_name, i.owner, s.bytes 
    FROM dba_indexes i, dba_segments s 
    WHERE s.segment_name = i.index_name 
    AND   s.owner = i.owner 
    AND   s.segment_type = 'INDEX' 
    UNION ALL 
    SELECT l.table_name, l.owner, s.bytes 
    FROM dba_lobs l, dba_segments s 
    WHERE s.segment_name = l.segment_name 
    AND   s.owner = l.owner 
    AND   s.segment_type = 'LOBSEGMENT' 
    UNION ALL 
    SELECT l.table_name, l.owner, s.bytes 
    FROM dba_lobs l, dba_segments s 
    WHERE s.segment_name = l.index_name 
    AND   s.owner = l.owner 
    AND   s.segment_type = 'LOBINDEX')
WHERE owner = '&USERNAME'                       -- Disable this line for all Table size in DB
AND table_name = '&TABLE_NAME'                -- Disable this line for all Table size in DB
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10                /* Ignore really small tables < 10 MB */
ORDER BY owner,SUM(bytes) desc;

OR

select OWNER,TABLE_NAME,ROW_SIZE_KB,BLOCK_SIZE_KB,(ROW_SIZE_KB*BLOCKS)/1024 Tot_size_MB
from     (
    select OWNER,TABLE_NAME, ROUND((AVG_ROW_LEN * NUM_ROWS / 1024), 2) ROW_SIZE_KB, (BLOCKS * 8)  BLOCK_SIZE_KB,BLOCKS
    from DBA_TABLES
    where OWNER not in ('SYS','SYSTEM','ORDSYS','DBSNMP','CTXSYS','XDB','OLAPSYS','WMSYS','WKSYS','MDSYS','EXFSYS','SYSMAN')
    )
Where ROW_SIZE_KB <> 0
and (ROW_SIZE_KB*BLOCKS)/1024 > 10
order by OWNER,TABLE_NAME;


BLOB column Size

select a.*,round(dbms_lob.getlength(a.bin)/1024,2) "Blob_Size_KB" from OWNER.BLOB_TABLE_NAME a where rownum < 20;

select round(sum(dbms_lob.getlength(bin))/(1024*1024),2) "All_Blob_Size_MB" from OWNER.BLOB_TABLE_NAME;


Query to get data file name of a given table (Output depends on Size of the Table)

column FILE_NAME for a60
select file_name, round( bytes/1024/1024 ) mbytes, round( ratio_to_report(bytes) over () * 100, 2 ) pct
from (    select sum(a.bytes) bytes, b.file_name
    from dba_extents a, dba_data_files b
    where a.owner = 'RMAN'
    and a.segment_name = 'DB'
    and a.segment_type = 'TABLE'
    and a.file_id = b.file_id
    group by b.file_name
     )
order by file_name;


Last DML Of the TABLE

select TABLE_OWNER,TABLE_NAME,INSERTS,UPDATES,DELETES,to_char(TIMESTAMP,'dd-Mon-yyyy hh24:mi:ss') TIMESTAMP,TRUNCATED,DROP_SEGMENTS
from DBA_TAB_MODIFICATIONS
where TABLE_OWNER in ('SCXDOWNLOAD')
and TABLE_NAME ='HISMES0';


Use below processes:-

STEP 1:- Alter Table Tabname Monitoring;
STEP 2:- Select * from User_Tab_Modifications;
STEP 3:- exec dbms_stats.flush_database_monitoring_info;
STEP 4:- select * from dba_tab_modifications;
STEP 5:- alter table tablename nomonitoring;


Compare Columns between 2 Tables Over DB_LINK
Outer table should be the base table and inner is the derive.

select OWNER,TABLE_NAME,COLUMN_NAME
from dba_tab_cols@DB_LINK
where table_name='TABLE'
and COLUMN_NAME not in (select COLUMN_NAME from dba_tab_cols where table_name='TABLE');


Extract TABLE Creation Scripts

set long 999999
select dbms_metadata.get_ddl('TABLE',table_name,owner) || '/' from dba_tables where owner='RMAN' and table_name='DB'
union all
select dbms_metadata.get_ddl('INDEX',index_name,owner) || '/' from dba_indexes where owner='RMAN' and table_name='DB';


TOP 10 Table Reads

PHYSICAL_READS

Whenever you execute a query, Oracle has to go and fetch data to give you the result of the query execution. Here, data means the actual data in data blocks. Whenever a new data block is requested, it has to be fetched from the physical datafiles residing on the physical disks. This fetching of data blocks from the physical disk involves an I/O operation known as physical I/O. By virtue of this physical I/O, now the block has been fetched and read into the memory area called buffer cache. This is a default action.  

We know that a data block might be requested multiple times by multiple queries.

So what happens when the same data block is requested again by the some other user? (See LOGICAL READS)

col segment_name format a30
col owner format a30

select owner,segment_name,object_type,total_physical_reads
from ( select owner,object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
And OBJECT_TYPE = 'TABLE'                                              -- Top 10 Table Access
-- And OBJECT_TYPE = 'INDEX'                                           -- Top 10 Index Access
And OWNER in ('ERMOWNER','INSIGHT','SNAPMGR')
order by total_physical_reads desc)
where rownum <11;

LOGICAL_READS

Once a physical I/O has taken place and the block has been read into the memory, the next request for the same data block won’t require the block to be fetched from the disk and hence avoiding a physical I/O. So now to return the results for the select query requesting the same data block, the block will be fetched from the memory and is called a Logical I/O.

Whenever the quantum of Logical I/O is calculated, two kinds of reads are considered - Consistent reads and Current reads. Jointly, these 2 statistics are known as Logical I/O performed by Oracle.

Let us look at these separately to better understand the concept of Logical I/O.

See More : http://www.dba-oracle.com/t_oracle_logical_io_physical_io.htm

col segment_name format a30
col owner format a30

select owner,segment_name,object_type,total_logical_reads
from ( select owner,object_name as segment_name,object_type,
value as total_logical_reads
from v$segment_statistics
where statistic_name in ('logical reads')
And OBJECT_TYPE = 'TABLE'                                             -- Top 10 Table Access
-- And OBJECT_TYPE = 'INDEX'                                          -- Top 10 Index Access
And OWNER in ('ERMOWNER','INSIGHT','SNAPMGR')
order by total_logical_reads desc)
where rownum <11;


Increase Table/Index MAX_EXTENTS

select OWNER,INDEX_NAME,TABLESPACE_NAME,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,INITIAL_EXTENT,NEXT_EXTENT
from dba_indexes
Where INDEX_NAME ='IDX2'
order by 4 desc;

select OWNER,TABLE_NAME,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,INITIAL_EXTENT,NEXT_EXTENT
from dba_tables
where OWNER='ELLIPSE'
And MAX_EXTENTS < 1000
order by 4 desc;


Increase Extents

ALTER TABLE ELLIPSE.MSF096_STD_VOLAT STORAGE (MAXEXTENTS 25000);
ALTER INDEX ELLIPSE.MSF096_STD_VOLAT STORAGE (MAXEXTENTS 25000);
ALTER TABLE tablename STORAGE (NEXT 1M PCTINCREASE 0);

Increase Extents for Multiple Tables/Index

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' STORAGE (MAXEXTENTS 1000);'
from dba_tables
where OWNER='TAB_OWNER'
And MAX_EXTENTS < 1000;

select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' STORAGE (MAXEXTENTS 1000);'
from dba_indexes
where OWNER='TAB_OWNER'
and MAX_EXTENTS < 1000;

Database Info Report (Size, Up-time, Setting And Growth Reports)

Contents of this Post

1. List of Database Files
2. Up-time For Database
3. PMON Up-time Of Database
4. Database Last Restore/Recover Time
5. Database is started with spfile or pfile?
6. Default Setting Of DATABASE
7. Total Database Size + Free Space + Used Space Reports
8. Tablespace Wise Size DB Size
9. Database Growth Report (Database Wise & Tablespace Wise)
10. Growth Report Using dbms_space.OBJECT_GROWTH_TREND

List of Database Files
set lines 200 pages 200
column "DataBase Files" for a60
select 1 "Sl No",'>>>>>> DATA FILES <<<<<<' "DataBase Files" from dual
union
select 1.1,name from v$datafile
union
select 2,chr(10) from dual union select 2,'>>>>>> CONTROL FILES <<<<<<' from dual
union
select 2.1,name from v$controlfile
union
select 3,chr(10) from dual union select 3,'>>>>>> REDOLOG FILES <<<<<<' from dual
union
select 3.1,member from v$logfile
union
select 4,chr(10) from dual union select 4,'>>>>>> TEMPORARY FILES <<<<<<' from dual
union
select 4.1,NAME from v$tempfile
order by 1;

================================================================================

Up-time For Database

col "Host Name" for a20
col "Instance Name" for a30
col UPTIME for a55
select
   'Hostname : ' || host_name As "Host Name"
   ,'Instance Name : ' || instance_name "Instance Name"
   ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') As "DB Start Time"
   ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
   trunc( 24*((sysdate-startup_time) -
   trunc(sysdate-startup_time))) || ' hour(s) ' ||
   mod(trunc(1440*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
   mod(trunc(86400*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from
   sys.v_$instance;

PMON Up-time
SELECT TO_CHAR(logon_time, 'DD-MM-YYYY HH24:MI:SS') "PMON Started"
FROM v$session
--where sid=1
WHERE program LIKE '%PMON%';

================================================================================

Last Restore/Recover Time
select RESETLOGS_CHANGE#
          ,to_char(RESETLOGS_TIME
          ,'DD-Mon-YYYY HH:MI:SS AM') RESETLOGS_TIME
          ,PRIOR_RESETLOGS_CHANGE#
          ,to_char(PRIOR_RESETLOGS_TIME
          ,'DD-Mon-YYYY HH:MI:SS AM') PRIOR_RESETLOGS_TIME
from V$DATABASE_INCARNATION;

================================================================================

Database is started with spfile or pfile?
col VALUE for a77
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type", value FROM sys.v_$parameter WHERE name = 'spfile';

Default Setting Of DATABASE
set lines 200 pages 200
column PROPERTY_VALUE for a30
column DESCRIPTION for a50
select * from database_properties where PROPERTY_NAME like 'DEFAULT%';

================================================================================

DATABASE SIZE

Total Database Size + Free Space + Used Space Reports:
set lines 200 pages 200
col "Total Database Size" format a20
col "Free Space (Logical)" format a20
col "Used Space (Logical)" format a20

select    round(sum(used.bytes)/1024/1024/1024,2) || ' GB' "Total Database Size",
            round(free.p/1024/1024/1024,2) || ' GB' "Free Space (Logical)",
            round(segment.use/1024/1024/1024,2) || ' GB' "Used Space (Logical)"
from     (select bytes from v$datafile
            union all
            select bytes from v$tempfile
            union all
            select bytes from v$log) used,
            (select sum(bytes) as p from dba_free_space) free,
            (SELECT SUM(bytes) use FROM sm$ts_used) segment
group by free.p,segment.use
/

Tablespace Wise Size
set lines 200 pages 200
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF AVAIL_GB USED_GB FREE_MB ON REPORT

select a.TABLESPACE_NAME, round(a.BYTES/1073741824,2) AVAIL_GB, round(b.BYTES/1073741824,2) USED_GB, round(c.BYTES/1048576,2) FREE_MB
from sm$ts_avail a, sm$ts_used b, sm$ts_free c
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME=c.TABLESPACE_NAME;

CLEAR BREAKS
CLEAR COMPUTES
CLEAR COLUMNS

================================================================================

DATABASE GROWTH
Note:- This report will provide the approx value

Growth Database Wise
select     sum(z.USED_MEG)/sum(1) "Growth_per_day MB"
           ,(sum(z.USED_MEG)) "Total_Growth MB",sum(1) Total_Days_cnt
           ,min(DATE_OF_TEST)||' To '||max(DATE_OF_TEST) "Report Date From"
from       (select x.DATE_OF_TEST DATE_OF_TEST,y.TOT_MEG - x.TOT_MEG TOT_MEG
           ,y.USED_MEG-x.USED_MEG USED_MEG, y.FREE_MEG - x.FREE_MEG FREE_MEG
from       (select trunc(DATE_OF_TEST) DATE_OF_TEST,round(sum(TOT_MEG),2) TOT_MEG
           ,round(sum(USED_MEG),2) USED_MEG, round(sum(FREE_MEG),2) FREE_MEG
from       ADMIN.TABLESPACE_DETAILS
where      TABLESPACE_NAME <> 'UNDOTBS1'
group by   trunc(DATE_OF_TEST)) x
           ,(select trunc(DATE_OF_TEST) DATE_OF_TEST
           ,round(sum(TOT_MEG),2) TOT_MEG
           ,round(sum(USED_MEG),2) USED_MEG
           , round(sum(FREE_MEG),2) FREE_MEG
from       ADMIN.TABLESPACE_DETAILS
where      TABLESPACE_NAME <> 'UNDOTBS1'
group by   trunc(DATE_OF_TEST))y
where      x.DATE_OF_TEST = y.DATE_OF_TEST-1) z;


Growth Tablespace Wise (10g and Above)
select b.tsname tablespace_name
          ,MAX(b.used_size_mb) cur_used_size_mb
          ,round(AVG(inc_used_size_mb),2)avg_increas_mb
from  ( SELECT a.days,a.tsname
          ,used_size_mb
          ,used_size_mb - LAG (used_size_mb,1)
          OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from (SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
         ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage  tsu
         , dba_hist_tablespace_stat  ts
         ,dba_hist_snapshot  sp
         , dba_tablespaces  dt
where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days ) a
) b
GROUP BY b.tsname
ORDER BY b.tsname;


[10g Above]
SQL> select * from table(dbms_space.OBJECT_GROWTH_TREND('RMAN','DB','TABLE'));

TIMEPOINT                                                                   SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
17-JAN-14 06.00.44.974958 AM                                                       4927       65536 INTERPOLATED
18-JAN-14 06.00.44.974958 AM                                                       4927       65536 GOOD
19-JAN-14 06.00.44.974958 AM                                                       4927       65536 PROJECTED

SQL Session Info

Contents of this Post

1. Count Current session
2. Session Info

Count Current session
SELECT   'Currently, '   
    || (SELECT COUNT(*) FROM V$SESSION)
    || ' out of '
    || VP.VALUE
    || ' connections are used.' AS USAGE_MESSAGE
FROM V$PARAMETER VP
WHERE VP.NAME = 'sessions';

Session Info
set linesize 200 pages 1000
set head on
set feedback on
col sid head "Sid" form 9999 trunc
col serial# form 99999 trunc head "Ser#"
col username form a10 trunc
col osuser form a9 trunc
col machine form a20 trunc head "Client|Machine"
col program form a35 trunc head "Client|Program|Module"
col login form a11
col "last call"  form 9999999 trunc head "Last Call|In Min"
col status form a6 trunc
col KILL_SESSION_SCRIPT for a50
col "Last Activity" for a13
col "Idle_Time" for 99,999.00

select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser,substr(program||'|'||module,1,25) program
,substr(machine,1,22) machine, to_char(logon_time,'ddMon hh24:mi') login, PROCESS,    -- Operating system client system process ID
to_char(sysdate-last_call_et/86400,'ddMon hh24:mi') "Last Activity"
,round(((sysdate-last_call_et/86400)-logon_time),2)*24 "Idle_Time",last_call_et/60 "last call",status
--,'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' Kill_Session_Script
from v$session
Where TYPE <> 'BACKGROUND'
--And status='INACTIVE' --'KILLED'
--And to_number(round(((sysdate-last_call_et/86400)-logon_time),2)*24) > 10    -- Session Idle from lsat n Hrs
--AND upper(USERNAME) like '&USERNAME%'
--And SID=116
--And logon_time < sysdate-1 -- < sysdate-(1/24)
--And program = 'ArcSOC.exe'
--Order by logon_time, "Last Activity", "Idle_Time", status, login
order by "Idle_Time" asc;

Long Running SQL (V$SESSION_LONGOPS)

 Contents of this Post

1. Active SQL
2. Find Long Operations
3. Check Time Remaining Of Export/Import
4. Monitor Index Creation
5. Queries currently running for more than 60 seconds
6. Oracle Locks

The V$SESSION_LONGOPS view displays the status of various operations that run for longer than 6 seconds.

These operations currently include…

• Backup and recovery functions
• Statistics gathering
• Query execution
• Import progress
• Index Creation etc..

To monitor query execution progress, you must be using the cost-based optimizer and you must:

·         Set the TIMED_STATISTICS or SQL_TRACE parameter to true
·         Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package

You can add information to this view about application-specific long-running operations by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure.


ACTIVE SQL

set lines 200 pages 200
col SQL_TEXT for a77
select S.USERNAME, s.sid, s.osuser, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
--AND USERNAME='&USERNAME'
order by s.sid,t.piece;


Find Long Operations (e.g. full table scans, RMAN, Insert, Import)

select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
         rows_processed,
         round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) Minutes,
         trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) Rows_Per_Minute
from   sys.v_$sqlarea
where  sql_text like 'INSERT %INTO "%'
and  command_type = 2
and  open_versions > 0;

OR

set lines 200 pages 200
col username format a20
col message format a70
--col remaining format 9999
select    username||'-'||sid||','||SERIAL# username
,    to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
,    time_remaining remaining_Sce
,    ELAPSED_SECONDS
,    round((sofar/totalwork)* 100,2) "COMPLETE%"
,    message
from    v$session_longops
where    time_remaining <> 0
--and TARGET like '%&USERNAME.%'
order by time_remaining desc;


Check Time Remaining Of Export/Import

SELECT  table_name
    ,rows_processed
    ,Minutes,Rows_Per_Minute
    ,(1/Rows_Per_Minute)*(147515763-rows_processed) Time_Remaining_Min From
    (select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
     rows_processed,
     round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) Minutes,
     trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) Rows_Per_Minute
from       sys.v_$sqlarea
where      sql_text like 'INSERT %INTO "%'
and      command_type = 2
and      open_versions > 0);

Monitor Index Creation

col sid format 9999
col start_time format a5 heading "Start|time"
col elapsed format 9999 heading "Mins|past"
col min_remaining format 9999 heading "Mins|left"
col message format a81
select sid
, to_char(start_time,'hh24:mi') start_time
, elapsed_seconds/60 elapsed
, round(time_remaining/60,2) "min_remaining"
, message
from v$session_longops where time_remaining > 0
AND MESSAGE like '%&TABLE_NAME.%';
--AND MESSAGE like '%&USWENAME.TABLE_NAME.%';


Queries currently running for more than 60 seconds (For Procedure & Package)

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
 where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece;

Oracle Locks

select
  object_name,
  object_type,
  session_id,
  type,                 -- Type or system/user lock
  lmode,        -- lock mode in which session holds lock
  request,
  block,
  ctime                 -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/

Oracle Session Memory Usage

 Contents of this Post

1. Viewing Memory Use for Each User Session
2. Memory Usage In Detail

Viewing Memory Use for Each User Session

set lines 200 pages 200
col "Current UGA memory" for a30

SELECT    USERNAME
                  ,sess.SID
                  ,sess.SERIAL#
                  ,VALUE || ' bytes = ' ||round(VALUE/1024/1024,2) ||' MB '  "Current UGA memory"
FROM       V$SESSION sess
                  ,V$SESSTAT stat
                  ,V$STATNAME name
WHERE     sess.SID = stat.SID
AND          stat.STATISTIC# = name.STATISTIC#
AND          name.NAME = 'session uga memory'
--AND       USERNAME= 'ORACLE'
--And         Value >= 10485760                (All Session Usage > 10MB)
order by VALUE;



Memory Usage In Detail
A  script to display the memory used by any specific session

select
    to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username,
    nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) ssession,
    to_char(prc.spid, '999999999')                       pid_thread,
    to_char((se1.value / 1024) / 1024, '999g999g990d00') current_size_mb,
    to_char((se2.value / 1024) / 1024, '999g999g990d00') maximum_size_mb
from
    v$statname  stat1,
    v$statname  stat2,
    v$session   ssn,
    v$sesstat   se1,
    v$sesstat   se2,
    v$bgprocess bgp,
    v$process   prc,
    v$instance  ins
where    stat1.name = 'session pga memory'
and    stat2.name = 'session pga memory max'
and     se1.sid = ssn.sid
and     se2.sid = ssn.sid
and     se2.statistic# = stat2.statistic#
and     se1.statistic# = stat1.statistic#
and     ssn.paddr = bgp.paddr(+)
and     ssn.paddr = prc.addr(+)
--and    ssn.sid in (select sid from v$session where username=    'ORACLE')
order by MAXIMUM_SIZE_MB;

Oracle Profile And Password Verify Function

1. Create PROFILE
2. Alter PROFILE
3. Drop PROFILE
4. PROFILE Parameters ( Resource Limits &  Password Limits)
5. PASSWORD_VERIFY_FUNCTION

CONN /AS SYSDBA


Create PROFILE

CREATE PROFILE DBA_USER LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;


Alter PROFILE
ALTER PROFILE DBA_USER LIMIT FAILED_LOGIN_ATTEMPTS 4;

Drop PROFILE
DROP PROFILE <Profile_Name> CASCADE;

PROFILE PARAMETERS

Setting Profile Resource Limits
SESSIONS_PER_USER         5                                  --INTGR/UNLIMITED/DEFAULT       
[The user can have any number of concurrent sessions.]

CPU_PER_SESSION            2160000                        --INTGR/UNLIMITED/DEFAULT       
[In a single session, the user can consume an 6 Hr (6*60*60)*100 of CPU time. (100ths of a second)]

CPU_PER_CALL            3000                                     --INTGR/UNLIMITED/DEFAULT       
[A single call made by the user cannot consume more than 30 seconds of CPU time. It is 100ths of a second]

CONNECT_TIME            45                                      --INTGR/UNLIMITED/DEFAULT       
[A single session cannot last for more than 45 minutes.]

IDLE_TIME            10                                                --INTGR/UNLIMITED/DEFAULT       
[Specify the permitted periods of continuous inactive time during a session, expressed in minutes.
Long-running queries and other operations are not subject to this limit.]

LOGICAL_READS_PER_SESSION    300000        --INTGR/UNLIMITED/DEFAULT       
[In a single session, the number of data blocks read from memory and disk is subject to the limit specified in the DEFAULT profile.]

LOGICAL_READS_PER_CALL        1000               --INTGR/UNLIMITED/DEFAULT       
[A single call made by the user cannot read more than 1000 data blocks from memory and disk.]

COMPOSITE_LIMIT            5000000                       --INTGR/UNLIMITED/DEFAULT       
[In a single session, the total resource cost cannot exceed 5 million service units.
The formula for calculating the total resource cost is specified by the ALTER RESOURCE COST statement.]

PRIVATE_SGA            200K                                        --SIZE_CLAUSE/UNLIMITED/DEFAULT       
[A single session cannot allocate more than 15 kilobytes of memory in the SGA.] (useful for systems using multi-threaded server MTS)


Setting Profile Password Limits
FAILED_LOGIN_ATTEMPTS        3                           --UNLIMITED/DEFAULT           
[The number of failed attempts to log in to the user account before the account is locked.]

PASSWORD_LIFE_TIME        15                                --UNLIMITED/DEFAULT           
[The number of days the same password can be used for authentication]

PASSWORD_REUSE_TIME        365                          --UNLIMITED/DEFAULT           
[The number of days between reuses of a password]

PASSWORD_REUSE_MAX        5                               --UNLIMITED/DEFAULT           
[The number of times a password must be changed before it can be reused]

PASSWORD_LOCK_TIME        .00139                       --UNLIMITED/DEFAULT           
[the number of days an account will be locked after the specified number of consecutive
failed login attempts defined by FAILED_LOGIN_ATTEMPTS] Here 2 mins

PASSWORD_GRACE_TIME        5                              --UNLIMITED/DEFAULT           
[The number of days after the grace period begins during which a warning is issued and login is allowed.
If the password is not changed during the grace period, the password expires]

PASSWORD_VERIFY_FUNCTION    NULL               --VERIFY_FUNCTION/NULL/DEFAULT
[Verify passwords for length, content, and complexity.]


Create Function for PASSWORD_VERIFY_FUNCTION


CREATE OR REPLACE FUNCTION SYS.verify_function
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52); BEGIN
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray:='!"#$%&amp;()``*+,-/:;&lt;=&gt;?_';
   -- Check if the password is same as the username
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20001, 'Password same as or similar to user');
   END IF;
   -- Check for the minimum length of the password
   IF length(password) &lt; 4 THEN
      raise_application_error(-20002, 'Password length less than 4');
   END IF;
   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
      raise_application_error(-20002, 'Password too simple');
   END IF;
   -- Check if the password contains at least one letter, one digit and one
   -- punctuation mark.
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
   END IF;
   -- 2. Check for the character
   &lt;&lt;findchar&gt;&gt;
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO findpunct;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one \
              digit, one character and one punctuation');
   END IF;
      -- 3. Check for the punctuation
   &lt;&lt;findpunct&gt;&gt;
   ispunct:=FALSE;
   FOR i IN 1..length(punctarray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(punctarray,i,1) THEN
            ispunct:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ispunct = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one \
              digit, one character and one punctuation');
   END IF;
   &lt;&lt;endsearch&gt;&gt;
   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password IS NOT NULL THEN
     differ := length(old_password) - length(password);
     IF abs(differ) &lt; 3 THEN
       IF length(password) &lt; length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;
       differ := abs(differ);
       FOR i IN 1..m LOOP
         IF substr(password,i,1) != substr(old_password,i,1) THEN
           differ := differ + 1;
         END IF;
       END LOOP;
       IF differ &lt; 3 THEN
         raise_application_error(-20004, 'Password should differ by at \
         least 3 characters');
       END IF;
     END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/

TOP 10 SQL Query (SqlArea)

TOP 10 SQL Query (SqlArea)


Top-N queries provide a method for limiting the number of rows returned from ordered sets of data. They are extremely useful when you want to return the top or bottom "N" number of rows from a set or when you are paging through data.


spool sqlarea.lst
set pagesize 66 linesize 180 trimspool on
set echo off
column executions      heading "Execs"      format 99999999
column rows_processed  heading "Rows Procd" format 99999999
column loads           heading "Loads"      format 999999.99
column buffer_gets     heading "Buffer Gets"
column disk_reads     heading "Disk Reads"
column elapsed_time   heading "Elasped Time" format 999999999
column cpu_time       heading "CPU Time" format 999999999
column sql_text        heading "SQL Text"   format a60 wrap
column avg_cost        heading "Avg Cost"   format 99999999
column etime_per_exec   heading "ETime Per Exec"   format 999999999
column ctime_per_exec   heading "CPU Time Per Exec"   format 999999999
column gets_per_exec   heading "Gets Per Exec"   format 99999999
column reads_per_exec   heading "Read Per Exec"   format 99999999
column rows_per_exec   heading "Rows Per Exec"   format 99999999

break on report
compute sum      of rows_processed     on report
compute sum      of executions         on report
compute avg      of avg_cost           on report
compute avg      of etime_per_exec      on report
compute avg      of ctime_per_exec      on report
compute avg      of gets_per_exec      on report
compute avg      of reads_per_exec      on report
compute avg      of row_per_exec      on report

PROMPT
PROMPT Top 10 most expensive SQL (Elapsed Time (secs) )...
PROMPT
select rownum as rank, a.*
from (
select elapsed_Time/1000000 elapsed_time,
executions,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text
from  v$sqlarea
where elapsed_time/1000000 > 5
order by elapsed_time desc) a
where rownum < 11
/

PROMPT
PROMPT Top 10 most expensive SQL (Elapsed Time (secs) Per Exec )...
PROMPT
select rownum as rank, a.*
from (
select elapsed_Time/1000000 elapsed_time,
executions,
elapsed_Time / (1000000 * decode(executions,0,1, executions) ) etime_per_exec,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text
from  v$sqlarea
where elapsed_time/1000000 > 5
order by etime_per_exec desc) a
where rownum < 11
/

PROMPT
PROMPT Top 10 most expensive SQL (CPU Time (secs) )...
PROMPT
col SQL_TEXT for a99
select rownum as rank, a.*
from (
select cpu_time/1000000 cpu_time,
executions,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text
from  v$sqlarea
where cpu_time/1000000 > 5
order by cpu_time desc) a
where rownum < 11
/

PROMPT
PROMPT Top 10 most expensive SQL (CPU Time (secs) per Exec)...
PROMPT
select rownum as rank, a.*
from (
select cpu_time/1000000 cpu_time,
executions,
cpu_time / (1000000 * decode(executions,0,1, executions)) ctime_per_exec,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text
from  v$sqlarea
where cpu_time/1000000 > 5
order by ctime_per_exec desc) a
where rownum < 11
/


PROMPT
PROMPT Top 10 most expensive SQL (Buffer Gets)...
PROMPT
select rownum as rank, a.*
from (
select buffer_gets,
executions,
buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
hash_value,
sql_text
from  v$sqlarea
where buffer_gets > 50000
order by buffer_gets desc) a
where rownum < 11
/

PROMPT
PROMPT Top 10 most expensive SQL (Buffer Gets by Executions)...
PROMPT
select rownum as rank, a.*
from (
select buffer_gets,
executions,
buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
hash_value,
sql_text
from  v$sqlarea
where buffer_gets > 50000
order by gets_per_exec desc) a
where rownum < 11
/

PROMPT
PROMPT Top 10 most expensive SQL (Physical Reads)...
PROMPT
select rownum as rank, a.*
from (
select disk_reads,
executions,
disk_reads / decode(executions,0,1, executions) reads_per_exec,
hash_value,
sql_text
from  v$sqlarea
where disk_reads > 10000
order by disk_reads desc) a
where rownum < 11
/

PROMPT Top 10 most expensive SQL (Physical Reads by Executions)...
PROMPT
select rownum as rank, a.*
from (
select disk_reads,
executions,
disk_reads / decode(executions,0,1, executions) reads_per_exec,
hash_value,
sql_text
from  v$sqlarea
where disk_reads > 10000
order by reads_per_exec desc) a
where rownum < 11
/

PROMPT Top 10 most expensive SQL (Rows Processed by Executions)...
PROMPT
select rownum as rank, a.*
from (
select rows_processed,
executions,
rows_processed / decode(executions,0,1, executions) rows_per_exec,
hash_value,
sql_text
from  v$sqlarea
where rows_processed > 10000
order by rows_per_exec desc) a
where rownum < 11
/

PROMPT
PROMPT Top 10 most expensive SQL (Buffer Gets vs Rows Processed)...
PROMPT
select rownum as rank, a.*
from (
select buffer_gets, lpad(rows_processed ||
decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",
executions, loads,
(decode(rows_processed,0,1,1)) *
buffer_gets/ decode(rows_processed,0,1,
rows_processed) avg_cost,
sql_text
from  v$sqlarea
where decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000
order by 5 desc) a
where rownum < 11
/

rem Check to see if there are any candidates for procedures or
rem for using bind variables. Check this by comparing UPPER
rem
rem This May be a candidate application for using the init.ora parameter
rem CURSOR_SHARING = FORCE|SIMILAR

select rownum as rank, a.*
from (
select     upper(substr(sql_text, 1, 65)) sqltext, count(*)
from     v$sqlarea  
group by upper(substr(sql_text, 1, 65))
having count(*) > 1
order by count(*) desc) a
where rownum < 11
/
prompt Output spooled to sqlarea.lst
spool off

Query Behind SQL Session

Get Full SQL From Oracle SID/OS PID (Find Long running oracle PIDs)


set lines 200 pages 200
col SQL_TEXT for a65
col "SID|SERIAL|PROCESS" for a15
col USERNAME for a9
col OSUSER for a14
col MACHINE for a18
col PROGRAM for a22
col COMMAND for a7
col TERMINAL for a20
col MODULE for a20
BREAK ON "SID|SERIAL|PROCESS" SKIP PAGE ON USERNAME SKIP 1 ON OSUSER SKIP 1 ON MACHINE SKIP 1 ON PROGRAM SKIP 1 ON COMMAND SKIP 1

select a.SQL_TEXT
,s.sid||'|'||s.serial#||'|'||p.spid "SID|SERIAL|PROCESS"
,s.USERNAME
--,s.STATUS,
,s.OSUSER
,s.MACHINE
--,s.TERMINAL
,s.PROGRAM
--,s.MODULE
--,s.ACTION
,ct.NAME Command
FROM v$session s, v$process p, v$sqltext a, audit_actions ct
WHERE s.paddr = p.addr
AND p.background is null
AND a.COMMAND_TYPE = ct.ACTION
AND a.ADDRESS=s.SQL_ADDRESS
and a.HASH_VALUE=s.SQL_HASH_VALUE
and s.sid in (33,116)                                                   -- Provide Oracle SID
--and p.spid in (25752,2589,26349)                          -- Provide OS Process ID From TOP/GLANCE
order by s.SID,a.PIECE;

Clear Shared Memory And Semaphore (ORA-27121)

What Is Shared Memory and Semaphores

Shared memory and semaphores are two important resources for an Oracle instance on UNIX.
An instance cannot start if it is unable to allocate what it needs.

Shared memory is a memory region that can be shared between different processes. Oracle uses shared memory for implementing the SGA, which needs to be visible to all database sessions.  Shared memory is also used in the implementation of the SQL*Net V1 Fast driver as a means of communicating between the application and shadow process. Each shadow process stores its PGA in a shared memory segment. In the latter two cases, Oracle allocates the shared memory dynamically as opposed to the allocation of the SGA, which occurs at instance STARTUP.

Semaphores can be thought of as flags. They are either on or off state.  A process can turn ON, the flag or turn it OFF. If the flag is already on, processes who try to turn on the flag will sleep until the flag is off.  Upon awakening, the process will reattempt to turn the flag on, possibly succeeding or possibly sleeping again.  Such behaviour allows semaphores to be used in implementing a post-wait driver - a system where processes can wait for events (i.e. wait on turning on a semaphore) and post events (i.e. turning of a semaphore).  This mechanism is used by Oracle to maintain concurrency control over the SGA, since it is writeable by all processes attached. Also, for the same reasons, use of the Fast Driver requires additional semaphores.  However, these semaphores will be allocated dynamically instead of at instance STARTUP.

Configuring Shared Memory and Semaphores

http://docs.oracle.com/cd/E19575-01/821-0182/abdco/index.html

Clear Shared Memory And Semaphore (HP-UX)

Sometimes it happens that after a crash or after an abnormal shutdown the oracle database instance does not release the shared memory or semaphores.

Error
##################################################
SQL> startup
ORA-27121: unable to determine size of shared memory segment
Error 13: Permission denied
##################################################

Cause
SHUTDOWN ABORT
CLUSTER FAILOVER
KILL PMON

Action
Shared Memory:
# ipcs -pmb | egrep 'oracle|orar12'

m  997425174 0xf5039ea8 --rw-rw----     oracle       dba
m     163864 0x4c17eb4c --rw-------        oracle       dba
m     786457 0x00000000 --rw-rw----      orar12      dba

Clear Shared Memory

ipcrm -m <SharedMemory ID>

Semaphores:
# ipcs -s |egrep 'oracle|orar12'

s   10321961 0xd9da4e50 --ra-------    oracle       dba
s   77168682 0x709a05d8 --ra-ra----    oracle       dba
s    2162731 0xd9da4ff0 --ra-------       orar12      dba

Clear Semaphores
ipcrm -s <SemaphoreID>

Allocate More Semaphores in KERNEL Parameter File

If Oracle Database creates more segments for the instance than the number of protection keys, then increase the value of the shmmax kernel parameter.


How To identify the Semaphores for a particular Oracle Instance
Get the shared memory and semaphore IDs

In Oracle 8i
$ export ORACLE_SID=ORCL
$ svrmgrl
SVRMGR> connect internal
SVRMGR> oradebug ipc
Information written to trace file.
SVRMGR> exit


In 9i & Above

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ cd $ORACLE_HOME/bin
$ sysresv
$ sysresv ORCL

IPC Resources for ORACLE_SID "ORCL" :
Shared Memory:
ID              KEY
324468764       0x0f73169c
Semaphores:
ID              KEY
22085675        0x13c503f0
Oracle Instance alive for sid "ORCL"


Check the Semaphores ID and compare with above

ps -ef | grep ORCL| grep LOCAL

  oracle 14848     1  0 07:11:10 ?         2:05 oracleORCL (LOCAL=NO)

SQL> oradebug setospid 14848
SQL> oradebug ipc

In udump location

List of Semaphore ID as below

Number of semaphore sets:        = 1
Semaphore identifiers:           = 1
Semaphore List=
35684397
-------------- system semaphore information -------------
IPC status from /dev/kmem as of Tue Oct  1 14:21:45 2013
T         ID     KEY        MODE        OWNER     GROUP   CREATOR    CGROUP NSEMS   OTIME    CTIME
Semaphores:
s          0 0x4f20037e --ra-------      root      root      root      root     1 14:20:40 19:48:16
s          1 0x412011f3 --ra-ra-ra-      root      root      root      root     1 16:32:00 19:48:17