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

<< lastnext >>

Snippet Name: TABLESPACE: Show contiguous space

Description: Show a summary of contiguous space.

Also see:
» TABLESPACE: SYSAUX tablespace
» TABLESPACE: Tablespace management
» 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

CREATE TABLE t_contig_space (
tablespace_name VARCHAR2(30),
file_id NUMBER,
block_id NUMBER,
starting_file_id NUMBER,
starting_block_id NUMBER,
blocks NUMBER,
bytes NUMBER)
tablespace uwdata;
 
CREATE OR REPLACE VIEW v_contig_space AS
SELECT SUBSTR(tablespace_name,1,20) TABLESPACE_NAME,
starting_file_id, starting_block_id, SUM(blocks) sum_blocks,
COUNT(blocks) count_blocks, MAX(blocks) max_blocks,
SUM(bytes)/1024/1024 SUM_MB
FROM tl_contig_space
GROUP BY tablespace_name, starting_file_id, starting_block_id;
 
DECLARE
 CURSOR query IS
 SELECT *
 FROM dba_free_space
 ORDER BY tablespace_name, file_id, block_id;
 
 this_row     query%ROWTYPE;
 previous_row query%ROWTYPE;
 old_file_id  PLS_INTEGER;
 old_block_id PLS_INTEGER;
BEGIN
  OPEN query;
  FETCH query INTO this_row;
  previous_row := this_row;
  old_file_id := previous_row.file_id;
  old_block_id := previous_row.block_id;
 
  WHILE query%FOUND LOOP
    IF this_row.file_id = previous_row.file_id AND
      this_row.block_id = previous_row.block_id+previous_row.blocks
    THEN
      INSERT INTO tl_contig_space
      (tablespace_name, file_id, block_id, starting_file_id,
       starting_block_id, blocks, bytes)
      VALUES
      (previous_row.tablespace_name, previous_row.file_id,
       this_row.block_id, old_file_id, old_block_id, this_row.blocks,
       this_row.bytes);
    ELSE
      INSERT INTO tl_contig_space
      (tablespace_name, file_id, block_id, starting_file_id,
       starting_block_id, blocks, bytes)
      VALUES
      (this_row.tablespace_name, this_row.file_id,
       this_row.block_id, this_row.file_id, this_row.block_id,
       this_row.blocks, this_row.bytes);
 
      old_file_id := this_row.file_id;
      old_block_id := this_row.block_id;
    END IF;
    previous_row := this_row;
    FETCH query INTO this_row;
  END LOOP;
  COMMIT;
END;
/
 
col tablespace_name format a20
col sum_mb format 999.999
 
SELECT * FROM v_contig_space;


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 97 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?