-
Help with query
Since I no longer have access to Oracle Enterprise Manager, I wrote the following queries to pull back inofrmation about the tablespaces and table.
I would appreciate any constructive critisism and suggestions that people have. So far I haven't found an existing query that gives me the information that I want.
Thanks in advance.
Code:
COL "Table Name" FORMAT A20
COL "TS Name" FORMAT A10
SELECT dba_tablespaces.tablespace_name "TS Name",
dba_tablespaces.extent_management "TS Type",
ROUND(dba_tablespaces.initial_extent/1024, 2) "Init (KB)",
ROUND(dba_tablespaces.min_extlen/1024, 2) "Min (KB)",
dba_segments.KBUsed "Used (KB)",
NVL(dba_data_files.FreeKBytes, 0) "Free (KB)",
dba_data_files.MaxKBytes "Max (KB)",
dba_tablespaces.pct_increase "% Increase"
FROM dba_tablespaces,
( SELECT tablespace_name,
ROUND(SUM(bytes)/1024, 2) KBUsed
FROM dba_segments
GROUP BY tablespace_name ) dba_segments,
( SELECT dba_data_files.tablespace_name,
ROUND(SUM(maxbytes)/1024, 2) MaxKBytes,
ROUND(SUM(user_free_space.bytes)/1024, 2) FreeKBytes
FROM dba_data_files
LEFT OUTER JOIN
( SELECT tablespace_name, bytes
FROM dba_free_space ) user_free_space ON
dba_data_files.tablespace_name =
user_free_space.tablespace_name
GROUP BY dba_data_files.tablespace_name ) dba_data_files
WHERE dba_tablespaces.tablespace_name = dba_segments.tablespace_name AND
dba_tablespaces.tablespace_name = dba_data_files.tablespace_name
ORDER BY dba_tablespaces.tablespace_name;
SELECT all_tables.table_name "Table Name",
all_tables.tablespace_name "TS Name",
all_tables.pct_free "% Free",
all_tables.pct_used "% Used",
init_extents.InitKB "Init (KB)",
all_tables.next_extent "Next (KB)",
dba_extents.KBUsed "KB Used",
dba_extents.KBFree "KB Free",
dba_extents.TotalExtents "Total Extents",
all_tables.max_extents "Max Extents"
FROM all_tables,
( SELECT segment_name, ROUND(SUM(bytes)/1024, 2) InitKB
FROM dba_extents
WHERE segment_type = 'TABLE' AND
owner = 'FIN' AND
(segment_name, extent_id) IN
(SELECT segment_name, MIN(extent_id)
FROM dba_extents
WHERE segment_type = 'TABLE' AND
owner = 'FIN'
GROUP BY segment_name)
GROUP BY segment_name ) init_extents,
( SELECT segment_name,
ROUND(SUM(dba_extents.bytes)/1024, 2) KBUsed,
ROUND(SUM(user_free_space.bytes)/1024, 2) KBFree,
COUNT(*) TotalExtents
FROM dba_extents
LEFT OUTER JOIN
( SELECT block_id, bytes
FROM dba_free_space ) user_free_space ON
dba_extents.block_id =
user_free_space.block_id
WHERE segment_type = 'TABLE' AND
owner = 'FIN'
GROUP BY dba_extents.segment_name ) dba_extents
WHERE all_tables.table_name = init_extents.segment_name AND
all_tables.table_name = dba_extents.segment_name;
-
Try this link
PS: You still could have OEM access if you are granted the SELECT_CATALOG_ROLE.
Last edited by LKBrwn_DBA; 11-12-2004 at 02:10 PM.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
It depends on what you want!
One thing I calculate for tables is a "data space required" as AVG_ROW_LEN*NUM_ROWS/(1 - PCTFREE/100) and compare this to the total size to give a "wasted space" value. Big values here pointed me to some probs with Auto Seg Space Mgmnt only using 10% of each block.
-
Originally posted by LKBrwn_DBA
Try this link
PS: You still could have OEM access if you are granted the SELECT_CATALOG_ROLE.
Thanks!
-
Originally posted by DaPi
It depends on what you want!
One thing I calculate for tables is a "data space required" as AVG_ROW_LEN*NUM_ROWS/(1 - PCTFREE/100) and compare this to the total size to give a "wasted space" value. Big values here pointed me to some probs with Auto Seg Space Mgmnt only using 10% of each block.
That is an interesting formula, but unfortunately I need the dba's to compute stats to make use of it.
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
|