Script: Current Allocation and Usage of Tablespace - Revision history

Ittichai at 12:42, 16 November 2023

2023-11-16T12:42:19Z

Ittichai at 10:43, 7 March 2011

2011-03-07T10:43:32Z

Ittichai at 10:43, 7 March 2011

2011-03-07T10:43:09Z

Ittichai: Created page with "

 -- ======================================================================================== -- Find the current allocation and usage of tablespaces (comma separated) -- Lis..."

2011-03-07T10:42:30Z

Created page with "<pre> -- ======================================================================================== -- Find the current allocation and usage of tablespaces (comma separated) -- Lis..."

New page

<pre>
-- ========================================================================================
-- Find the current allocation and usage of tablespaces (comma separated)
-- List the data file names associated with specified tablespaces
-- ========================================================================================
SET pages 200
SET lines 200
COLUMN name FORMAT A10 HEAD "Tablespace Name"
COLUMN Pct_used FORMAT 999.9 HEAD "Percent Used(%)"
COLUMN kbytes_used FORMAT 999,999,999 HEAD "Allocated Space (MB)"
COLUMN used FORMAT 999,999,999 HEAD "Used Space (MB)"
COLUMN free FORMAT 999,999,999 HEAD "Free Space (MB)"

SELECT
NVL(FULL.tablespace_name, nvl(FREE.tablespace_name, 'UNKOWN')) Name
, kbytes_used
, kbytes_used - NVL(kbytes_free,0) Used
, NVL(kbytes_free,0) Free
, ((kbytes_used-nvl(kbytes_free,0)) / kbytes_used)*100 Pct_used
FROM
(
SELECT
SUM(bytes)/(1024*1024) kbytes_free
, MAX(bytes)/(1024*1024) max_free
, tablespace_name
FROM
sys.DBA_FREE_SPACE
GROUP BY
tablespace_name ) FREE,
(
SELECT
SUM(bytes)/(1024*1024) Kbytes_used
, tablespace_name
FROM
sys.DBA_DATA_FILES
WHERE
tablespace_name in (&&TBS_NAMES_IN_QUOTE_COMMA_SEP)
GROUP BY tablespace_name ) FULL
WHERE
FREE.tablespace_name (+) = FULL.tablespace_name
;

Enter value for tbs_names_in_quote_comma_sep: 'GO_DATA'
old 24: tablespace_name in (&&TBS_NAMES_IN_QUOTE_COMMA_SEP)
new 24: tablespace_name in ('GO_DATA')

Tablespace Allocated Space (MB) Used Space (MB) Free Space (MB) Percent Used(%)
---------- -------------------- --------------- --------------- ---------------
GO_DATA 3,072 2,470 603 80.4

COLUMN tablespace_name FORMAT A10
COLUMN file_name FORMAT A50
select tablespace_name, file_name, bytes/1024/1024 "Size_MB", AUTOEXTENSIBLE from dba_data_files
where tablespace_name in (&TBS_NAMES_IN_QUOTE_COMMA_SEP)
;

TABLESPACE FILE_NAME Size_MB AUT
---------- -------------------------------------------------- ---------- ---
GO_DATA +PRDW_DG1/prdw/datafile/go_data.285.730419055 3072 NO

</pre>

← Older revision		Revision as of 10:43, 7 March 2011
Line 64:		Line 64:

	</pre>		</pre>
		+
		+	[[Category:Oracle_Scripts]]

@@ Line 6: / Line 6: @@
 SET pages 200
 SET lines 200
-COLUMN name     	FORMAT A10         HEAD "Tablespace Name"
+COLUMN name     	FORMAT A20         HEAD "Tablespace Name"
 COLUMN Pct_used 	FORMAT 999.9       HEAD "Percent Used(%)"
 COLUMN kbytes_used  FORMAT 999,999,999 HEAD "Allocated Space (MB)"

@@ Line 40: / Line 40: @@
 	FREE.tablespace_name (+) = FULL.tablespace_name
+;
+</pre>
 Enter value for tbs_names_in_quote_comma_sep: 'GO_DATA'
 old  24:                                tablespace_name in (&&TBS_NAMES_IN_QUOTE_COMMA_SEP)
@@ Line 49: / Line 50: @@
 GO_DATA                   3,072           2,470             603            80.4
+</pre>
 COLUMN tablespace_name     	FORMAT A10
 COLUMN file_name     		FORMAT A50
@@ Line 55: / Line 57: @@
 where tablespace_name in (&TBS_NAMES_IN_QUOTE_COMMA_SEP)
+;
+</pre>
 TABLESPACE FILE_NAME                                             Size_MB AUT
 ---------- -------------------------------------------------- ---------- ---