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;