DBMS_SPACE.UNUSED_SPACE schema usage

[grapes12]

Good-day

I'm using the following procedure, to determine total blocks and unused blocks within a schema.
Currently I am inserting one table at a time,
HOW CAN I SELECT ALL TABLES WITHIN THE SCHEMA, is there a way of doing it,
ANY help will be much appreciated

et serveroutput on
set verify off 
DECLARE
 v_total_blocks number;
 v_total_bytes number;
 v_unused_blocks number;
 v_unused_bytes number;
 v_last_used_extent_file_id number;
 v_last_used_extent_block_id number;
 v_last_used_block number;
BEGIN 
    dbms_space.unused_space(segment_owner 				=> UPPER('&OWNER'), 
	      	segment_name  				=> UPPER('&NAME'), 
		segment_type 				=> UPPER('&TYPE'),
		total_blocks  				=> v_total_blocks, 
		total_bytes  				=> v_total_bytes, 
		unused_blocks 				=> v_unused_blocks, 
		unused_bytes  				=> v_unused_bytes,
		last_used_extent_file_id    => v_last_used_extent_file_id, 
		last_used_extent_block_id   => v_last_used_extent_block_id,
		last_used_block				=> v_last_used_block);

dbms_output.put_line('v_total_blocks   				:' || v_total_blocks);
dbms_output.put_line('v_unused_blocks				:' || v_unused_blocks);
END;

/

 

[LKBrwnDBA]

Try this:

SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF
DECLARE
  v_total_blocks                NUMBER;
  v_total_bytes                 NUMBER;
  v_unused_blocks               NUMBER;
  v_unused_bytes                NUMBER;
  v_last_used_extent_file_id    NUMBER;
  v_last_used_extent_block_id   NUMBER;
  v_last_used_block             NUMBER;
BEGIN
  FOR t IN (SELECT *
              FROM dba_tables t
             WHERE t.owner = 'SCOTT'
               AND NOT EXISTS
                     (SELECT '?'
                        FROM dba_external_tables x
                       WHERE x.owner = t.owner
                         AND x.table_name = t.table_name))
  LOOP
    DBMS_SPACE.
     unused_space (
                   segment_owner               => t.owner
                 , segment_name                => t.table_name
                 , segment_type                => 'TABLE'
                 , total_blocks                => v_total_blocks
                 , total_bytes                 => v_total_bytes
                 , unused_blocks               => v_unused_blocks
                 , unused_bytes                => v_unused_bytes
                 , last_used_extent_file_id    => v_last_used_extent_file_id
                 , last_used_extent_block_id   => v_last_used_extent_block_id
                 , last_used_block             => v_last_used_block
                  );
    DBMS_OUTPUT.put_line ( RPAD('+++++ ' || t.owner || '.' || t.table_name ,40)
                             || ', total_blocks: ' || v_total_blocks
                             || ', unused_blocks: ' || v_unused_blocks);
  END LOOP;
END;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[grapes12]

Thank you very much will try this.
Will the same apply for the following procedure or not?

set serveroutput on 

declare
 v_unformatted_blocks number;
 v_unformatted_bytes number;
 v_fs1_blocks number;
 v_fs1_bytes number;
 v_fs2_blocks number;
 v_fs2_bytes number;
 v_fs3_blocks number;
 v_fs3_bytes number;
 v_fs4_blocks number;
 v_fs4_bytes number;
 v_full_blocks number;
 v_full_bytes number;
 
begin
      dbms_space.space_usage ( segment_owner 				=> UPPER('&OWNER'), 
							   segment_name  				=> UPPER('&NAME'), 
							   segment_type 				=> UPPER('&TYPE'),
							   unformatted_blocks			=> v_unformatted_blocks,
                               unformatted_bytes            => v_unformatted_bytes, 
                               fs1_blocks                   => v_fs1_blocks, 
                               fs1_bytes                    => v_fs1_bytes, 
                               fs2_blocks                   => v_fs2_blocks, 
                               fs2_bytes                    => v_fs2_bytes,
                               fs3_blocks                   => v_fs3_blocks, 
                               fs3_bytes                    => v_fs3_bytes, 
                               fs4_blocks                   => v_fs4_blocks, 
                               fs4_bytes                    => v_fs4_bytes, 
                               full_blocks                  => v_full_blocks, 
							   full_bytes					=> v_full_bytes);
 
     dbms_output.put_line('Unformatted Blocks              = '||v_unformatted_blocks);
     dbms_output.put_line('Blocks with 00-25% free space   = '||v_fs1_blocks);
     dbms_output.put_line('Blocks with 26-50% free space   = '||v_fs2_blocks);
     dbms_output.put_line('Blocks with 51-75% free space   = '||v_fs3_blocks);
     dbms_output.put_line('Blocks with 76-100% free space  = '||v_fs4_blocks);
     dbms_output.put_line('Full Blocks                     = '||v_full_blocks);
end;
 /

I am currently also just inserting one table at a time.
Any help will be much appreciated.

 

[grapes12]

after running the procedure I realised that i had forgotten that we have a number of parttion tables.
So I added the partition options to the procedure. But recieved the following errors

Error at line 3
ORA-14107: partition specification is required for a partitioned object
ORA-06512: at "SYS.DBMS_SPACE", line 176
ORA-06512: at line 20

The procedure looks like this now with the partition options added.

SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF
DECLARE
  v_total_blocks                NUMBER;
  v_total_bytes                 NUMBER;
  v_unused_blocks               NUMBER;
  v_unused_bytes                NUMBER;
  v_last_used_extent_file_id    NUMBER;
  v_last_used_extent_block_id   NUMBER;
  v_last_used_block             NUMBER;
  v_partition_name             VARCHAR2(60);
    BEGIN
  FOR t IN (SELECT *
              FROM dba_tables t
             WHERE t.owner = 'MAC'
               AND NOT EXISTS
                     (SELECT '?'
                        FROM dba_external_tables x
                       WHERE x.owner = t.owner
                         AND x.table_name = t.table_name))
  LOOP
    DBMS_SPACE.
     unused_space (
                   segment_owner               => t.owner
                 , segment_name                => t.table_name
                 , segment_type                => 'TABLE'
                 , total_blocks                => v_total_blocks
                 , total_bytes                 => v_total_bytes
                 , unused_blocks               => v_unused_blocks
                 , unused_bytes                => v_unused_bytes
                 , last_used_extent_file_id    => v_last_used_extent_file_id
                 , last_used_extent_block_id   => v_last_used_extent_block_id
                 , last_used_block             => v_last_used_block
                 , partition_name               => v_partition_name 
                  );
    DBMS_OUTPUT.put_line ( RPAD('+++++ ' || t.owner || '.' || t.table_name ,40)
                             || ', total_blocks: ' || v_total_blocks
                             || ', unused_blocks: ' || v_unused_blocks);
  END LOOP;
END;
/

Is this correct?

 

[grapes12]

morning Guys,

Any help will be much appreciated with the above.

Thanks

 

[LKBrwnDBA]

You need to provide the partition or sub partition name!
Try this:

SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF
DECLARE
  v_owner                       VARCHAR2 (60);
  v_table_name                  VARCHAR2 (60);
  v_total_blocks                NUMBER;
  v_total_bytes                 NUMBER;
  v_unused_blocks               NUMBER;
  v_unused_bytes                NUMBER;
  v_last_used_extent_file_id    NUMBER;
  v_last_used_extent_block_id   NUMBER;
  v_last_used_block             NUMBER;
  v_count                       NUMBER;
  v_partition_name              VARCHAR2 (60);
  v_segment_type                VARCHAR2 (100) := 'TABLE';

  PROCEDURE get_space
  IS
  BEGIN
    DBMS_SPACE.unused_space 
                 (
                   segment_owner               => v_owner
                 , segment_name                => v_table_name
                 , partition_name              => v_partition_name
                 , segment_type                => v_segment_type
                 , total_blocks                => v_total_blocks
                 , total_bytes                 => v_total_bytes
                 , unused_blocks               => v_unused_blocks
                 , unused_bytes                => v_unused_bytes
                 , last_used_extent_file_id    => v_last_used_extent_file_id
                 , last_used_extent_block_id   => v_last_used_extent_block_id
                 , last_used_block             => v_last_used_block
                 );
  END;

  PROCEDURE print_line
  IS
    txt   VARCHAR2 (4000);
  BEGIN
    txt := v_owner ||'.' ||v_table_name || '|' || v_partition_name 
        || '|' ||v_total_blocks ||'|' || v_unused_blocks||'|';

    DBMS_OUTPUT.put_line ( txt);
  END;
BEGIN
  DBMS_OUTPUT.put_line ( 'owner.table_name|partition_name|total_blocks|unused_blocks');
  FOR t IN (SELECT *
              FROM dba_tables t
             WHERE t.owner = 'MAC'
               --AND table_name like 'A%'
               AND NOT EXISTS
                     (SELECT '?'
                        FROM dba_external_tables x
                       WHERE x.owner = t.owner
                         AND x.table_name = t.table_name))
  LOOP
    v_owner            := t.owner;
    v_table_name       := t.table_name;
    v_partition_name   := '';

    IF t.partitioned = 'YES'
    THEN
      SELECT COUNT ( *)
        INTO v_count
        FROM dba_tab_subpartitions
       WHERE table_owner = t.owner
         AND table_name = t.table_name;

      IF v_count = 0
      THEN
        v_segment_type   := 'TABLE PARTITION';

        FOR p IN (SELECT partition_name
                    FROM dba_tab_partitions
                   WHERE table_owner = t.owner
                     AND table_name = t.table_name)
        LOOP
          v_partition_name   := p.partition_name;
          get_space;
          print_line;
        END LOOP;
      ELSE
        v_segment_type   := 'TABLE SUBPARTITION';

        FOR p IN (SELECT subpartition_name
                    FROM dba_tab_subpartitions
                   WHERE table_owner = t.owner
                     AND table_name = t.table_name)
        LOOP
          v_partition_name   := p.subpartition_name;
          get_space;
          print_line;
        END LOOP;
      END IF;
    ELSE
      v_segment_type   := 'TABLE';
      get_space;
      print_line;
    END IF;
  END LOOP;
END;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb