-
how to get space allocated and used for tables space
Plesae guide me in this questio please.
I need to get the space allocated and used by table space?
How can i do that!!
-
create a table then and put some data in it
-
Originally Posted by davey23uk
create a table then and put some data in it
Interesting answer.
I think the person is asking how to find out how large a tablespace is and how much of it used. If so here is a sql script that should help.
Code:
SET ECHO OFF
SET SERVEROUTPUT ON
SET LINESIZE 140
DECLARE
CURSOR cur_ts IS
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024) totalMB,
TO_CHAR(ROUND(SUM(bytes)/1024/1024), '999,999') totalMBV
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name;
TYPE lineDetailRec IS RECORD
( HighWaterMarkMB VARCHAR2(32),
freeMB VARCHAR2(32),
largestFreeSpace VARCHAR2(32),
pctUsed VARCHAR2(32) );
lineDetail lineDetailRec;
lv_fs NUMBER := 0;
percent_fs NUMBER := 0;
large_fs NUMBER := 0;
blocksize NUMBER := 0;
lv_name v$database.name%TYPE;
BEGIN
DBMS_OUTPUT.enable(1000000);
SELECT value
INTO blocksize
FROM v$parameter
WHERE name = 'db_block_size';
SELECT name
INTO lv_name
FROM v$database;
DBMS_OUTPUT.PUT_LINE('*****************'||lv_name||
' Database -- Tablespace Summary - Freespace Report *********************');
DBMS_OUTPUT.PUT_LINE('Tablespace name ' ||
' Total Space(MB)' ||
' Highwater Mark ' ||
' Free Space(MB) ' ||
' LG Free(MB) ' ||
' %Free Space ');
DBMS_OUTPUT.PUT_LINE('********************* ' ||
' ************** ' ||
' ************** ' ||
' ************** ' ||
' ************* ' ||
' *************');
FOR lv_cur_ts IN cur_ts LOOP
SELECT TO_CHAR(hwm.HighWaterMarkMB, '999,999'),
TO_CHAR(fs.freeMB, '999,999'),
TO_CHAR(fs.largestFreeSpace, '999,999'),
TO_CHAR(ROUND( (freeMB/lv_cur_ts.totalMB)*100, 1), '9990.0')
INTO lineDetail
FROM ( SELECT ROUND(SUM(BYTES)/1024/1024) freeMB,
ROUND(MAX(bytes)/1024/1024) largestFreeSpace
FROM dba_free_space
WHERE tablespace_name = lv_cur_ts.tablespace_name ) fs,
( SELECT NVL(MAX(block_id), 0)*blocksize/1024/1024 HighWaterMarkMB
FROM dba_extents
WHERE tablespace_name = lv_cur_ts.tablespace_name ) hwm;
DBMS_OUTPUT.PUT_LINE(RPAD(lv_cur_ts.tablespace_name, 21) ||
LPAD(lv_cur_ts.totalMBV, 16) ||
LPAD(lineDetail.HighWaterMarkMB, 16) ||
LPAD(lineDetail.freeMB, 16) ||
LPAD(lineDetail.largestFreeSpace, 15) ||
LPAD(lineDetail.pctUsed, 10));
END LOOP;
END;
/
SET ECHO ON
-
Originally Posted by gandolf989
Interesting answer.
I think the person is asking how to find out how large a tablespace is and how much of it used. If so here is a sql script that should help.
Code:
SET ECHO OFF
SET SERVEROUTPUT ON
SET LINESIZE 140
DECLARE
CURSOR cur_ts IS
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024) totalMB,
TO_CHAR(ROUND(SUM(bytes)/1024/1024), '999,999') totalMBV
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name;
TYPE lineDetailRec IS RECORD
( HighWaterMarkMB VARCHAR2(32),
freeMB VARCHAR2(32),
largestFreeSpace VARCHAR2(32),
pctUsed VARCHAR2(32) );
lineDetail lineDetailRec;
lv_fs NUMBER := 0;
percent_fs NUMBER := 0;
large_fs NUMBER := 0;
blocksize NUMBER := 0;
lv_name v$database.name%TYPE;
BEGIN
DBMS_OUTPUT.enable(1000000);
SELECT value
INTO blocksize
FROM v$parameter
WHERE name = 'db_block_size';
SELECT name
INTO lv_name
FROM v$database;
DBMS_OUTPUT.PUT_LINE('*****************'||lv_name||
' Database -- Tablespace Summary - Freespace Report *********************');
DBMS_OUTPUT.PUT_LINE('Tablespace name ' ||
' Total Space(MB)' ||
' Highwater Mark ' ||
' Free Space(MB) ' ||
' LG Free(MB) ' ||
' %Free Space ');
DBMS_OUTPUT.PUT_LINE('********************* ' ||
' ************** ' ||
' ************** ' ||
' ************** ' ||
' ************* ' ||
' *************');
FOR lv_cur_ts IN cur_ts LOOP
SELECT TO_CHAR(hwm.HighWaterMarkMB, '999,999'),
TO_CHAR(fs.freeMB, '999,999'),
TO_CHAR(fs.largestFreeSpace, '999,999'),
TO_CHAR(ROUND( (freeMB/lv_cur_ts.totalMB)*100, 1), '9990.0')
INTO lineDetail
FROM ( SELECT ROUND(SUM(BYTES)/1024/1024) freeMB,
ROUND(MAX(bytes)/1024/1024) largestFreeSpace
FROM dba_free_space
WHERE tablespace_name = lv_cur_ts.tablespace_name ) fs,
( SELECT NVL(MAX(block_id), 0)*blocksize/1024/1024 HighWaterMarkMB
FROM dba_extents
WHERE tablespace_name = lv_cur_ts.tablespace_name ) hwm;
DBMS_OUTPUT.PUT_LINE(RPAD(lv_cur_ts.tablespace_name, 21) ||
LPAD(lv_cur_ts.totalMBV, 16) ||
LPAD(lineDetail.HighWaterMarkMB, 16) ||
LPAD(lineDetail.freeMB, 16) ||
LPAD(lineDetail.largestFreeSpace, 15) ||
LPAD(lineDetail.pctUsed, 10));
END LOOP;
END;
/
SET ECHO ON
hmm yeah, should read more closely
-
Originally Posted by davey23uk
hmm yeah, should read more closely
i usually read closely, but then again I'm nearsighted.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|