Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: TABLESPACE: Tablespace management Jump to:  
Category: >> Oracle PL/SQL >> TABLESPACE: Tablespace management Bookmark and Share

<< lastnext >>

Snippet Name: TABLESPACE: Tablespace management

Description: Statements for tablespace management.

Also see:
» TABLESPACE: Show contiguous space
» TABLESPACE: SYSAUX tablespace
» TABLESPACE: List tablespaces, files, ...
» TABLESPACE: Dropping Tablespaces
» TABLESPACE: Alter Permanent Tablespace
» TABLESPACE: Transportable tablespaces
» TABLESPACE: Tempfile operations
» TABLESPACE: Create temp tablespace
» TABLESPACE: Change UNDO tablespace
» TABLESPACE: Undo Tablespace
» TABLESPACE: SYSAUX Tablespace
» TABLESPACE: Set default tablespace type
» TABLESPACE: Oracle Managed Auto-extend...
» TABLESPACE: Permanent Tablespace Using...
» TABLESPACE: Using Raw Devices
» TABLESPACE: permanent tablespace

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 12th, 2009

///////////////////////////////////////////////
-- statement for tablespace management #1
///////////////////////////////////////////////
 
SET linesize 121
 
SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
  SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
  0 TOTAL_MB, 0 MAX_MB
  FROM dba_free_space
  GROUP BY tablespace_name
  UNION
  SELECT tablespace_name, 0 CURRENT_MB,
  SUM(bytes)/1024/1024 TOTAL_MB,
  SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
  FROM dba_data_files
  GROUP BY tablespace_name)
GROUP BY tablespace_name;
 
 
///////////////////////////////////////////////
-- statement for tablespace management #2
///////////////////////////////////////////////
 
col tablespace_name format a15
col alloc_size format 999.999
col pct_used format 999.999
col free_space format 999.999
col maxnext format 999.999
col definitsz format 999.999
col defnextsz format 999.999
 
SELECT a.tablespace_name, a.datafile_sz, b.alloc_size,
(b.alloc_size)/a.datafile_sz*100 PCT_USED,
(a.datafile_sz-b.alloc_size) FREE_SPACE,
b.next_extent/1024/1024 MAXNEXT,
a.initial_extent/1024/1024 DEFINITSZ,
a.next_extent/1024/1024 DEFNEXTSZ
FROM (
  SELECT a.tablespace_name, SUM(b.bytes)/1024/1024
  DATAFILE_SZ, a.initial_extent, a.next_extent
  FROM dba_tablespaces a, dba_data_files b
  WHERE a.tablespace_name = b.tablespace_name
  GROUP BY a.tablespace_name, a.initial_extent, a.next_extent) A,
    (
  SELECT a.tablespace_name, SUM(c.bytes)/1024/1024
  ALLOC_SIZE, MAX(c.next_extent) NEXT_EXTENT
  FROM dba_tablespaces a, dba_segments c
  WHERE a.tablespace_name = c.tablespace_name
  GROUP BY a.tablespace_name) B
WHERE a.tablespace_name = b.tablespace_name (+)
ORDER BY 1;
 
 
///////////////////////////////////////////////
-- statement for tablespace management #3
///////////////////////////////////////////////
SELECT dfs.tablespace_name, ddf.total_size,
ddf.total_size - dfs.total_free TOTAL_USED,
dfs.total_free,
(ddf.total_size-dfs.total_free)/ddf.total_size * 100 CAP,
dfs.total_chunks, dfs.largest_chunk
FROM (
  SELECT a.tablespace_name,
  SUM(a.bytes)/1024/1024 TOTAL_FREE,
  COUNT(a.bytes) TOTAL_CHUNKS,
  MAX(a.bytes)/1024/1024 LARGEST_CHUNK
  FROM dba_free_space a
  GROUP BY a.tablespace_name) dfs,
    (
  SELECT b.tablespace_name,
  SUM(b.bytes)/1024/1024 TOTAL_SIZE
  FROM dba_data_files b
  GROUP BY b.tablespace_name) ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name;


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 95 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?