overcome SP2-0734 when using purge command

Issue new Oracle 10G ‘purge‘ command results in ‘SP2-0734: unknown command beginning “purge user…” – rest of line ignored.’

Purge Command in pre 10G SQL*Plus release

Whenever you want to issue the ‘purge’ command in any pre 10G SQL*Plus client you will notice that SQL*Plus denies to send your command to the server and it will raise an ‘SP2-0734’ error. You can easiliy overcome this by making use of dynamic sql (or just upgrade your client of course).

SQL> create table demo(k int);

Table created.

SQL> drop table demo;

Table dropped.

SQL> select * from cat;
TABLE_NAME                     TABLE_TYPE
------------------------------ -----------

SQL> drop table "BIN$HE/mKBNyRajgRAADuhSWrA==$0";
drop table "BIN$HE/mKBNyRajgRAADuhSWrA==$0"
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

SQL> [strong]purge user_recyclebin;
SP2-0734: unknown command beginning "purge user..." - rest of line ignored.[/strong]

Solution is beautiful in it’s simplicity, use dynamic sql and off you go.

Purge recycle bin with a pre 10G SQL*Plus client
SQL> begin 
  2    execute immediate 'purge user_recyclebin';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from cat;

no rows selected
Posted in 10g

One thought on “overcome SP2-0734 when using purge command

Leave a Reply

Your email address will not be published. Required fields are marked *