DBMS_SPACE bind error
563299Apr 27 2007 — edited Apr 27 2007hi all,
Am getting a BIND ERROR while running the following code.
set serveroutput on
declare
variable o1 number;
variable total_bytes number;
variable o3 number;
variable unused_bytes number;
variable o5 number;
variable o6 number;
variable o7 number;
used_bytes number:=0;
b long:=0;
TNAME varchar2:=&tname;
DBMS_SPACE.UNUSED_SPACE('XMLUSER','TNAME','TABLE',:o1,:total_bytes,:o3,:unused_bytes,:o5,:o6,:o7);
used_bytes:=total_bytes-unused_bytes; /*finding the memory used by a table*/
total_bytes:=total_bytes/(1024*1024);/*total allocated bytes in MB*/
used_bytes:=used_bytes/(1024*1024);/*used bytes in MB*/
unused_bytes:=unused_bytes/(1024*1024);/*unused bytes in MB*/
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||TNAME INTO b;
DBMS_OUTPUT.PUT_LINE('TOTAL SPACE IN MB :'||total_bytes);
DBMS_OUTPUT.PUT_LINE('USED SPACE IN MB :'||used_bytes);
DBMS_OUTPUT.PUT_LINE('UNUSED SPACE IN MB :'||unused_bytes);
DBMS_OUTPUT.PUT_LINE('ROW COUNT :'||b);
end;
/
SQL> @size_of_table.sql
Enter value for tname: cust
old 11: TNAME varchar2:=&tname;
new 11: TNAME varchar2:=cust;
SP2-0552: Bind variable "UNUSED_BYTES" not declared.
Can anybody help me to sort out this problem
thanks in advance
karthik.J