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).

[REMI@DB01.REMIDIAN.COM]
SQL> create table demo(k int);
 
Table created.
 
[REMI@DB01.REMIDIAN.COM]
SQL> drop table demo;
 
Table dropped.
 
[REMI@DB01.REMIDIAN.COM]
SQL> select * from cat;
TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
BIN$HE/mKBNyRajgRAADuhSWrA==$0 TABLE
 
[REMI@DB01.REMIDIAN.COM]
SQL> 
[REMI@DB01.REMIDIAN.COM]
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
 
[REMI@DB01.REMIDIAN.COM]
SQL> purge user_recyclebin;
SP2-0734: unknown command beginning "purge user..." - rest of line ignored.
[REMI@DB01.REMIDIAN.COM]
SQL> prompt &_SQLPLUS_RELEASE
902000700

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

Purge recycle bin with a pre 10G SQL*Plus client

[REMI@DB01.REMIDIAN.COM]
SQL> begin 
  2    execute immediate 'purge user_recyclebin';
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
[REMI@DB01.REMIDIAN.COM]
SQL> select * from cat;
 
no rows selected