Feeds:
Posts
Comments

Posts Tagged ‘Free space’

Fragmentation is use to happen for tabl as well as for tablespace.Fragmentation is nothing but waisted space(After deletion of data).

set linesize 150
column tablespace_name format a20 heading ‘Tablespace’
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading ‘Total| Size(Mb)’
column Tot_Free format 999,999,999 heading ‘Total Free(MB)’
column Pct_Free format 999.99 heading ‘% Free’
column Chunks_Free format 9999 heading ‘No Of Ext.’
column Max_Free format 999,999,999 heading ‘Max Free(Kb)’
set echo off
PROMPT FREE SPACE AVAILABLE IN TABLESPACES
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1048576) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by pct_free;

How to avoide tablespace fragmentation please reffer

How to find and avoide table fragmentation please reffer

Expert are always welcome for their valuable comment or suggestion for the above post.

Advertisements

Read Full Post »

While working on oracle I found some scripts which are mandatory for any DBA.I would like to share those scripts with you.

Scripts Every DBA Should Have
I. Display the Current Archivelog Status :

ARCHIVE LOG LIST;

II. Creating a Control File Trace File

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

III. Tablespace Free Extents and Free Space

column Tablespace_Name format A20

column Pct_Free format 999.99

select Tablespace_Name,

Max_Blocks,

Count_Blocks,

Sum_Free_Blocks,

100*Sum_Free_Blocks/Sum_Alloc_Blocks AS Pct_Free

from

(select Tablespace_Name, SUM(Blocks) Sum_Alloc_Blocks

from DBA_DATA_FILES

group by Tablespace_Name),

(select Tablespace_Name FS_TS_NAME,

MAX(Blocks) AS Max_Blocks,

COUNT(Blocks) AS Count_Blocks,

SUM(Blocks) AS Sum_Free_Blocks

from DBA_FREE_SPACE

group by Tablespace_Name)

where Tablespace_Name = FS_TS_NAME;

IV. Display Allocated Space & Quota by User

select * from DBA_TS_QUOTAS

order by Tablespace_Name, Username;

V. Show Allocated Storage for All Objects

column Segment_Name format A40

select Segment_Name, Extents, Blocks

from DBA_SEGMENTS

where Segment_Type = ‘&segment_type’

order by Segment_Name;

VI. Map a Tablespace’s Used and Free Space

select Segment_Name, File_Id, Block_Id, Blocks

from DBA_EXTENTS where Tablespace_Name = ‘&&tablespacename’

UNION

select ‘Free Space’, File_Id, Block_Id, Blocks

from DBA_FREE_SPACE where Tablespace_Name = ‘&&tablespacename’

order by 2,3;

VII. Blocks Used by Data in a Table

select COUNT(DISTINCT(SUBSTR(ROWID,1,8)||SUBSTR(ROWID,15,4)))

Blocks_Used from &table;

VIII. Reset a User’s Password

select ‘ALTER USER &&1 IDENTIFIED BY VALUES

‘||””||Password||””||’;’

from DBA_USERS where Username = UPPER(‘&&1’);

IX. Query V$PARAMETER

column Name format A50

column Value format A28

select Name, Value from V$PARAMETER;

X. Show Statement Execution Path

select LPAD(‘ ‘,2*LEVEL)||Operation||’ ‘||Options

||’ ‘||Object_Name Q_PLAN

from PLAN_TABLE where Statement_ID = ‘TEST’

Read Full Post »