select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name=’DATA’;

Database Size
February 8, 2011dbpostLeave a comment
dba_data_files ñ means total size of the data file.

The data file size is total of dba_free_space and dba_segments:
dba_data_files = dba_free_space + dba_segments

dba_segments ñ means used size of the data file

dba_free_space ñ means free size of the data file

Size Check Queries:

To get total datafiles size, free and used space in percentage:

select t2.total “TOTAL SIZE”,t1.free “FREE SPACE”,
(t1.free/t2.total)100 “FREE (%)”, (1-t1.free/t2.total)100 “USED (%)”
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total from dba_Data_files) t2;

To get the size of all Data files:

select sum(bytes)/1024/1024 “Data files size in MB” from dba_data_files;
To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 “Temp files size in MB” from dba_temp_files;
To get the size of the on-line redo-logs:

SELECT SUM(BYTES)/1024/1024 “Redo files size in MB” FROM SYS.V_$LOG;
Total Size and free size:

select round(sum(used.bytes) / 1024 / 1024 ) || ‘ MB’ “Database Size”
, round(free.p / 1024 / 1024) || ‘ MB’ “Free space”
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
group by free.p;

Individual tablespace size:

select tablespace_name as “Tablespace Name”,sum(bytes)/(1024*1024) as “Size” from dba_data_files group by tablespace_name ORDER BY 1;

Individual tablespace used size:

select tablespace_name as “Tablespace Name”,sum(bytes)/(1024*1024) as “Used Size” from dba_segments GROUP BY TABLESPACE_NAME order by 1;

Individual tablespace free space:

select tablespace_name as “Tablespace Name”, sum(bytes)/(1024*1024) as “Free Space” from dba_free_space GROUP BY TABLESPACE_NAME order by 1;

Query to find space used by a database user

Following query can be used to know the space used by the logged in user in MBs:

SELECT sum(bytes)/1024/1024 user_size FROM user_segments;

Query to find the space occupied by all the users in a database. This requires access to dba_segments table:

SELECT owner, sum(bytes)/1024/1024 total_size FROM dba_segments GROUP BY owner ORDER BY total_size DESC;

Total space occupied by all users:

SELECT sum(bytes)/1024/1024 total_size FROM dba_segments;

SELECT sum(bytes)/1024/1024 total_size FROM dba_free_space;

SELECT sum(bytes)/1024/1024 total_size FROM dba_data_files;

dba_data_files ñ means total size of the data file.

The data file size is total of dba_free_space and dba_segments:
dba_data_files = dba_free_space + dba_segments

dba_segments ñ means used size of the data file

dba_free_space ñ means free size of the data file

Getting the space left on the DEFAULT_TABLESPACE of the logged user

SELECT
ts.tablespace_name,
TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, ‘99,999,990.99’) AS MB_FREE
FROM
user_free_space fs,
user_tablespaces ts,
user_users us
WHERE
fs.tablespace_name(+) = ts.tablespace_name
AND ts.tablespace_name(+) = us.default_tablespace
GROUP BY
ts.tablespace_name;

You might be interested in this query. It tells you how much space is allocated for each table taking into account the indexes and any LOBs on the table. Often you are interested to know “How much spaces the the Purchase Order table take, including any indexes” rather than just the table itself. You can always delve into the details. Note that this requires access to the DBA_* views.

CALCULATE EACH TABLE SPACE USED

COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
owner,
table_name,
TRUNC(sum(bytes)/1024/1024) Meg,
ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type IN (‘TABLE’, ‘TABLE PARTITION’, ‘TABLE SUBPARTITION’)
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 IN (‘INDEX’, ‘INDEX PARTITION’, ‘INDEX SUBPARTITION’)
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 IN (‘LOBSEGMENT’, ‘LOB PARTITION’)
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 in UPPER(‘&owner’)
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc;

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

CREATE BIGFILE TABLESPACE big_ts NOLOGGING datafile ‘+FRA_DATA/demodb/big_ts.dbf’ SIZE 40G AUTOEXTEND ON NEXT 5G;

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

How to find Table Fragmentation in Oracle Database
How to find Table Fragmentation in Oracle Database

What is Oracle Table Fragmentation?
If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.

To understand it more clearly, we need to be clear on how oracle manages space for tables.

When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don’t have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

What are the reasons to reorganization of table?

a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.

Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.

How to find Table Fragmentation?

In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)). This all is due to fragmentation in the table or stats for table are not updated into dba_tables.

Steps to Check and Remove Table Fragmentation:-

1. Gather table stats:

To check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual fragmentation to DBA. So, We have to have updated stats on the table stored in dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.

exec dbms_stats.gather_table_stats(‘&schema_name’,’&table_name’);

2. Check Table size:

Now again check table size using and will find reduced size of the table.

select table_name,bytes/(102410241024) from dba_table where table_name=’&table_name’;

3. Check for Fragmentation in table:

Below query will show the total size of table with fragmentation, expected without fragmentation and how much % of size we can reclaim after removing table fragmentation. Database Administrator has to provide table_name and schema_name as input to this query.

set pages 50000 lines 32767
select owner,table_name,round((blocks8),2)||’kb’ “Fragmented size”, round((num_rowsavg_row_len/1024),2)||’kb’ “Actual size”, round((blocks8),2)-round((num_rowsavg_row_len/1024),2)||’kb’,
((round((blocks8),2)-round((num_rowsavg_row_len/1024),2))/round((blocks8),2))100 -10 “reclaimable space % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’
/
Note: This query fetch data from dba_tables, so the accuracy of result depends on dba_table stats.

If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.

4. How to reset HWM / remove fragemenation?

We have four options to reorganize fragmented tables:

Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-
(Depends upon the free space available in the tablespace)
Export and import the table:- (difficult to implement in production environment)
Shrink command (fron Oracle 10g)
(Shrink command is only applicable for tables which are tablespace with auto segment space management)

Here, I am following Options 1 and 3 option by keeping table availability in mind.

Option: 1 Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-

Collect status of all the indexes on the table:-

We will record Index status at one place, So that we get back them after completion of this exercise,

select index_name,status from dba_indexes where table_name like ‘&table_name’;

Move table in to same or new tablespace:

In this step we will move fragmented table to same tablespace or from one tablespace to another tablespace to reclaim fragmented space. Find Current size of you table from dba_segments and check if same or any other tablespace has same free space available. So, that we can move this table to same or new tablespace.

Steps to Move table in to same tablespace:

alter table move; ——> Move to same tablespace

Steps to Move table in to new tablespace:

alter table enable row movement;
alter table move tablespace ;

Now, get back table to old tablespaces using below command

alter table table_name move tablespace old_tablespace_name;

Now,Rebuild all indexes:

We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME

UNUSABLE INDEX_NAME ——-> Here, value in status field may be valid or unusable.

SQL> alter index rebuild online; ——-> Use this command for each index
Index altered.

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME

VALID INDEX_NAME ——-> Here, value in status field must be valid.

Gather table stats:

SQL> exec dbms_stats.gather_table_stats(‘&owner_name’,’&table_name’);
PL/SQL procedure successfully completed.

Check Table size:

Now again check table size using and will find reduced size of the table.

select table_name,bytes/(102410241024) from dba_table where table_name=’&table_name’;

Check for Fragmentation in table:

/

Option: 3 Shrink command (fron Oracle 10g):-

Shrink command:

Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.

This command is only applicable for tables which are tablespace with auto segment space management.

Before using this command, you should have row movement enabled.

SELECT tablespace_name, extent_management, segment_space_management FROM dba_tablespaces;

SQL> alter table enable row movement;
Table altered.

There are 2 ways of using this command.

1. Rearrange rows and reset the HWM:

Part 1: Rearrange (All DML’s can happen during this time)
SQL> alter table shrink space compact;
Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table shrink space;
Table altered.

2. Directly reset the HWM:

SQL> alter table shrink space; (Both rearrange and restting HWM happens in one statement)
Table altered.

Advantages over the conventional methods are:

Unlike “alter table move ..”,indexes are not in UNUSABLE state.After shrink command,indexes are updated also.
Its an online operation, So you dont need downtime to do this reorg.
It doesnot require any extra space for the process to complete.

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

Script: To Monitor Tablespaces/datafiles

Important Note: If any of the script in this blog is not running then please re-type it or try to retype quotation, command and braces (may be format is changed). I am using toad so if you are using SQL then try to fix column length before exectunig the script (if any).

To check Tablespace free space:

SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) “Size (MB)” FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
To check Tablespace by datafile:
SELECT tablespace_name, File_id, SUM(bytes/1024/1024)”Size (MB)” FROM DBA_FREE_SPACE
group by tablespace_name, file_id;

To Check Tablespace used and free space %:

SELECT /* + RULE */ df.tablespace_name “Tablespace”,
df.bytes / (1024 * 1024) “Size (MB)”, SUM(fs.bytes) / (1024 * 1024) “Free (MB)”,
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) – df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) – df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

To check Growth rate of Tablespace

Note: The script will not show the growth rate of the SYS, SYSAUX Tablespace. T
he script is used in Oracle version 10g onwards.

SELECT TO_CHAR (sp.begin_interval_time,’DD-MM-YYYY’) days,
ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(10241024),2) ) cur_size_MB, max(round((tsu.tablespace_usedsize dt.block_size )/(1024*1024),2)) usedsize_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 ts.tsname NOT IN (‘SYSAUX’,’SYSTEM’)
GROUP BY TO_CHAR (sp.begin_interval_time,’DD-MM-YYYY’), ts.tsname
ORDER BY ts.tsname, days;

List all Tablespaces with free space < 10% or full space> 90%

Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) – (sum(a.sumb)100))/85 )/1048576) Min_Add from (select tablespace_name,0 tots,sum(bytes) sumb from dba_free_space a group by tablespace_name union Select tablespace_name,sum(bytes) tots,0 from dba_data_files group by tablespace_name) a group by a.tablespace_name having sum(a.sumb)100/sum(a.tots) < 10
order by pct_free;
Script to find all object Occupied space for a Tablespace
Select OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024 “SZIE IN MB” from dba_segments
where TABLESPACE_NAME = ‘SDH_HRMS_DBF’
group by OWNER, SEGMENT_NAME;
Which schema are taking how much space
Select obj.owner “Owner”, obj_cnt “Objects”, decode(seg_size, NULL, 0, seg_size) “size MB”
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
(select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;

To Check Default Temporary Tablespace Name:

Select * from database_properties where PROPERTY_NAME like ‘%DEFAULT%’;
To know default and Temporary Tablespace for particualr User:
Select username,temporary_tablespace,default_tablespace from dba_users where username=’HRMS’;
To know Default Tablespace for All User:
Select default_tablespace,temporary_tablespace,username from dba_users;
To Check Datafiles used and Free Space:
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;

To check Used free space in Temporary Tablespace:

SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE FROM V$temp_space_header GROUP BY tablespace_name;

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
Sort (Temp) space used by Session
SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace, COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial;
Sort (Temp) Space Usage by Statement
SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
Who is using which UNDO or TEMP segment?
SELECT TO_CHAR(s.sid)||’,’||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, ‘None’) orauser,s.program, r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||’K’ “Undo”
FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = ‘db_block_size’;
Who is using the Temp Segment?
SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||’M’ “SIZE”,
a.sid||’,’||a.serial# SID_SERIAL, a.username, a.program
FROM sys.v_$session a,
sys.v_$sort_usage b, sys.v_$parameter p
WHERE p.name = ‘db_block_size’ AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
Total Size and Free Size of Database:
Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ‘ GB’ “Database Size”,
round(free.p / 1024 / 1024/1024) || ‘ GB’ “Free space”
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
group by free.p;

To find used space of datafiles:

SELECT SUM(bytes)/1024/1024/1024 “GB” FROM dba_segments;
IO status of all of the datafiles in database:
WITH total_io AS
(SELECT SUM (phyrds + phywrts) sum_io
FROM v$filestat)
SELECT NAME, phyrds, phywrts, ((phyrds + phywrts) / c.sum_io) * 100 PERCENT,
phyblkrd, (phyblkrd / GREATEST (phyrds, 1)) ratio
FROM SYS.v_$filestat a, SYS.v_$dbfile b, total_io c
WHERE a.file# = b.file#
ORDER BY a.file#;
Displays Smallest size the datafiles can shrink to without a re-organize.
SELECT a.tablespace_name, a.file_name, a.bytes AS current_bytes, a.bytes – b.resize_to AS shrink_by_bytes, b.resize_to AS resize_to_bytes
FROM dba_data_files a, (SELECT file_id, MAX((block_id+blocks-1)&v_block_size) AS resize_to FROM dba_extents GROUP by file_id) b WHERE a.file_id = b.file_id ORDER BY a.tablespace_name, a.file_name; Scripts to Find datafiles increment details: Select SUBSTR(fn.name,1,DECODE(INSTR(fn.name,’/’,2),0,INSTR(fn.name,’:’,1),INSTR(fn.name,’/’,2))) mount_point,tn.name tabsp_name,fn.name file_name, ddf.bytes/1024/1024 cur_size, decode(fex.maxextend, NULL,ddf.bytes/1024/1024,fex.maxextendtn.blocksize/1024/1024) max_size,
nvl(fex.maxextend,0)tn.blocksize/1024/1024 – decode(fex.maxextend,NULL,0,ddf.bytes/1024/1024) unallocated,nvl(fex.inc,0)tn.blocksize/1024/1024 inc_by
from sys.v_$dbfile fn, sys.ts$ tn, sys.filext$ fex, sys.file$ ft, dba_data_files ddf
where fn.file# = ft.file# and fn.file# = ddf.file_id
and tn.ts# = ft.ts# and fn.file# = fex.file#(+)
order by 1;

=================TEMP FILE FULL==========

SHRINK IT AND RESIZE IT

alter tablespace TEMP shrink space keep 256M;

alter database tempfile ‘+DB_DATA/TEST/temp01.dbf’ resize 2100M;

alter database datafile ‘/u01/app/oracle/TEST/users01.dbf’ resize 10000M;

alter database datafile ‘/Data/app/oracle/TEST/system01.dbf’ resize 3000M;

alter database datafile ‘+DATA/TEST/DATAFILE/system.257.991959855’ resize 2000M;
alter database datafile ‘+DATA/TEST/DATAFILE/sysaux.258.991959895’ resize 2000M;
alter database datafile ‘+DATA/TEST/DATAFILE/users.260.991959921’ resize 5000M;

alter tablespace USERS add datafile ‘+FRA’ size 10G ;

select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name=’FRA’;

Post Views: 362