remidian.com

play Mount of Olives

remidian is remi visser, oracle dba contractor from the Netherlands you can browse my work related braindumps, read my resume or contact me

home oracle New Features 10g

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

[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

One Comment, Comment or Ping

  1. Sharat Chandran

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

Reply to “overcome SP2-0734 when using purge command”