overcome SP2-0734 when using purge command

  • Published on Aug 30 2006
  • # 412
  • 10g

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> [strong]purge user_recyclebin;
SP2-0734: unknown command beginning "purge user..." - rest of line ignored.[/strong]
[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

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

  1. Sharat Chandran says:

    Hey!! this really helped.. thanks a million!! :)

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>