Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Monday, September 12, 2011

Oracle tablespace status report ....

ACCEPT tbsp PROMPT 'Enter the Tablespace name in Alert:'
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN ALLOCATED_MB FORMAT 999,999,990.00
COLUMN FREE_MB FORMAT 99,999,990.00
COLUMN MAX_MB FORMAT 999,999,990.00
COLUMN PCT_FREE FORMAT 90.00
COLUMN PCT_MAX_FREE FORMAT 999,999,990.00
COLUMN AVAILABLE_MB FORMAT 999,999,990.00
COLUMN NEXT_MB FORMAT 999,999,990.00
COLUMN tbsp FORMAT A30 heading "TABLESPACE"
COLUMN file_name FORMAT A70 HEADING "DATA FILE(S)"
COLUMN AUTOEXTENSIBLE FORMAT A7 HEADING "AUTOEXT"
COLUMN BLS NEW_VALUE BLOCK_SIZE
set verify off
SELECT BLOCKSIZE BLS FROM SYS.TS$ WHERE NAME='&tbsp' ;
set lines 136 pages 50

SELECT T.TABLESPACE_NAME tbsp,
ROUND(T.MB_TOTAL,2) "ALLOCATED_MB",
ROUND(F.MB_FREE,2) "FREE_MB",
ROUND(F.MB_FREE/ROUND(T.MB_TOTAL)*100,2) PCT_FREE,
ROUND(T.MB_MAX,2) "MAX_MB",
ROUND((T.MB_MAX-(T.MB_TOTAL-F.MB_FREE))/ROUND(T.MB_MAX)*100,2) PCT_MAX_FREE,
ROUND(F.MB_FREE + T.MB_MAX - T.MB_TOTAL) AVAILABLE_MB
FROM
(
SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) MB_FREE
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) MB_TOTAL,
SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/(1024*1024) MB_MAX
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE T.TABLESPACE_NAME=F.TABLESPACE_NAME(+)
AND T.TABLESPACE_NAME='&tbsp'
ORDER BY pct_max_free, PCT_FREE, AVAILABLE_MB, ALLOCATED_MB ;
SELECT FILE_NAME, ROUND(BYTES/1024/1024,2) "ALLOCATED_MB", ROUND(MAXBYTES/1024/1024,2) "MAX_MB", AUTOEXTENSIBLE ,
INCREMENT_BY*&&BLOCK_SIZE/1024/1024 "NEXT_MB"
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '&tbsp' ;

No comments: