Home » RDBMS Server » Server Administration » DBMS_SPACE expects partition (Oracle9i)
DBMS_SPACE expects partition [message #329823] Thu, 26 June 2008 11:55 Go to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
I am trying to run the dbms_space.space_usage procedure for one of my table which has partitions.. Here is the error i am getting.

SQL> DECLARE 
  2  v_unformatted_blocks number; 
  3  v_unformatted_bytes number; 
  4  v_fs1_blocks number; 
  5  v_fs1_bytes number; 
  6  v_fs2_blocks number; 
  7  v_fs2_bytes number; 
  8  v_fs3_blocks number; 
  9  v_fs3_bytes number; 
 10  v_fs4_blocks number; 
 11  v_fs4_bytes number; 
 12  v_full_blocks number; 
 13  v_full_bytes number; 
 14  BEGIN 
 15  dbms_space.space_usage ('ADMIN', 'CONENT', 'TABLE', v_unformatted_blocks, v_unformatted_bytes
, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_f
s4_bytes, v_full_blocks, v_full_bytes); 
 16  dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks); 
 17  dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks); 
 18  dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks); 
 19  dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks); 
 20  dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks); 
 21  dbms_output.put_line('Full Blocks = '||v_full_blocks); 
 22  end;
 23  /
DECLARE
*
ERROR at line 1:
ORA-14107: partition specification is required for a partitioned object
ORA-06512: at "SYS.DBMS_SPACE", line 97
ORA-06512: at line 15



Again i specified the partition name and still getting same error.

SQL> DECLARE 
  2  v_unformatted_blocks number; 
  3  v_unformatted_bytes number; 
  4  v_fs1_blocks number; 
  5  v_fs1_bytes number; 
  6  v_fs2_blocks number; 
  7  v_fs2_bytes number; 
  8  v_fs3_blocks number; 
  9  v_fs3_bytes number; 
 10  v_fs4_blocks number; 
 11  v_fs4_bytes number; 
 12  v_full_blocks number; 
 13  v_full_bytes number; 
 14  BEGIN 
 15  dbms_space.space_usage ('ADMIN', 'P2003_3', 'TABLE PARTITION', v_unformatted_blocks, v_unfor
matted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4
_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes); 
 16  dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks); 
 17  dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks); 
 18  dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks); 
 19  dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks); 
 20  dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks); 
 21  dbms_output.put_line('Full Blocks = '||v_full_blocks); 
 22  end;
 23  /
DECLARE
*
ERROR at line 1:
ORA-03205: partition name is required when partitioned type is specified
ORA-06512: at "SYS.DBMS_SPACE", line 97
ORA-06512: at line 15


SQL> 


Any help appreciated...
Re: DBMS_SPACE expects partition [message #329825 is a reply to message #329823] Thu, 26 June 2008 12:04 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Do a describe on dbms_space package and you will understand what information is missing.

Regards

Raj
Re: DBMS_SPACE expects partition [message #329828 is a reply to message #329825] Thu, 26 June 2008 12:17 Go to previous message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Thanks Raj. It worked.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE 
  2  v_unformatted_blocks number; 
  3  v_unformatted_bytes number; 
  4  v_fs1_blocks number; 
  5  v_fs1_bytes number; 
  6  v_fs2_blocks number; 
  7  v_fs2_bytes number; 
  8  v_fs3_blocks number; 
  9  v_fs3_bytes number; 
 10  v_fs4_blocks number; 
 11  v_fs4_bytes number; 
 12  v_full_blocks number; 
 13  v_full_bytes number; 
 14  BEGIN 
 15  dbms_space.space_usage ('ADMIN', 'CONTENT', 'TABLE PARTITION', v_unformatted_blocks, v_unforma
tted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_b
locks, v_fs4_bytes, v_full_blocks, v_full_bytes,'P2003_3'); 
 16  dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks); 
 17  dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks); 
 18  dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks); 
 19  dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks); 
 20  dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks); 
 21  dbms_output.put_line('Full Blocks = '||v_full_blocks); 
 22  end;
 23  /
Unformatted Blocks = 0
FS1 Blocks = 801
FS2 Blocks = 3851
FS3 Blocks = 2371
FS4 Blocks = 153
Full Blocks = 2

PL/SQL procedure successfully completed.

Previous Topic: how to find a table located in particular datafile
Next Topic: What do these parameter mean in the trace file?
Goto Forum:
  


Current Time: Wed Dec 07 05:04:25 CST 2016

Total time taken to generate the page: 0.16583 seconds