Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1353
  • Last Modified:

SP2-0311: string expected but not found

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>SQLPLUS/NOLOG

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 25 09:15:07 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> CONNECT DB_ADMIN/DB_ADMIN@DB101 AS SYSDBA
Connected to an idle instance.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  293601280 bytes
Fixed Size                  1248624 bytes
Variable Size              92275344 bytes
Database Buffers          197132288 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.
SQL> CREATE TABLE BDROP (COLA NUMBER);

Table created.

SQL> INSERT INTO BDROP VALUES(99);

1 row created.

SQL> INSERT INTO BDROP VALUES(100);

1 row created.

SQL> SELECT * FROM BDROP;

      COLA
----------
        99
       100

SQL> DROP TABLE BDROP;

Table dropped.

SQL> SELECT * FROM BDROP;
SELECT * FROM BDROP
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> SHOW RECYCLEBIN ;
SP2-0311: string expected but not found
SQL>
0
lojayn
Asked:
lojayn
  • 7
  • 5
  • 4
  • +1
1 Solution
 
lojaynAuthor Commented:
what causes the error , how to fix it?
0
 
sdstuberCommented:
show is to expose the values of sqlplus environment settings.

recyclebin isn't a setting
0
 
johnsoneSenior Oracle DBACommented:
My understanding is that RECYCLEBIN is a synonym for USER_RECYCLEBIN.  You would have to select from it:

SELECT OBJECT_NAME, ORIGINAL_NAME FROM RECYCLEBIN;

Or to see the everything:

SELECT OBJECT_NAME, ORIGINAL_NAME FROM DBA_RECYCLEBIN;
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
lojaynAuthor Commented:
SQL> SELECT OBJECT_NAME, ORIGINAL_NAME FROM DBA_RECYCLEBIN;

no rows selected

SQL> SELECT * FROM DBA_RECYCLEBIN WHERE ORIGINAL_NAME='BDROP';

no rows selected

SQL>


0
 
slightwv (䄆 Netminder) Commented:
show recyclebin is valid in 10g sqplus:
www.orafaq.com/wiki/Recycle_bin

Now what version of the database are you connecting to?

select * from v$version;
0
 
sdstuberCommented:
do you have the recycle bin enabled?
0
 
sdstuberCommented:
select value from v$parameter where name  = 'recyclebin'
0
 
lojaynAuthor Commented:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select value from v$parameter where name  = 'recyclebin'
  2  ;

VALUE
--------------------------------------------------------------------------------

on

SQL>
0
 
slightwv (䄆 Netminder) Commented:
I am running 10.2.0.3 and dropped a table and "show recyclebin" worked for me.

Selecting form the view should definitely work.

You might be hitting a bug in 10.2.0.1.
0
 
johnsoneSenior Oracle DBACommented:
OK, I learned something today too.  Apparently SHOW RECYCLEBIN should work in 11g as well:

http://download.oracle.com/docs/cd/E11882_01/server.112/e16604/ch_twelve041.htm#i2699447

I never used it, I would always select from DBA_RECYCLEBIN.
0
 
sdstuberCommented:
sorry, I didn't notice your sql*plus version was 10.2.0.1 earlier.

nor did I notice this...

SQL> CONNECT DB_ADMIN/DB_ADMIN@DB101 AS SYSDBA


"as sysdba" means you are SYS  and SYS uses the SYSTEM tablespace and those objects don't use the recyclebin
0
 
sdstuberCommented:
so,   create your objects in some schema other than SYSTEM  (which is recommended practice anyway)

also,  if you don't need to be sysdba,  then don't be.  I recognize you were doing a startup in this example.  
0
 
lojaynAuthor Commented:
I always connect as sysdba.
I am trying to connect as user , what is wrong in the way of connection .

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>SQLPLUS/NOLOG

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 25 10:07:21 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> CONNECT MEDUSER/MEDUSER;
ERROR:
ORA-12560: TNS:protocol adapter error


SQL>
0
 
slightwv (䄆 Netminder) Commented:
you still need the sqlnet database alias:
CONNECT MEDUSER/MEDUSER@DB101
0
 
sdstuberCommented:
>>>I always connect as sysdba.
that is bad practice





you need to specify the database

meduser/meduser@somedatabase
0
 
slightwv (䄆 Netminder) Commented:
To clairfy:  As soon as Oracle sees 'as sysdba' it ignores whatever username and password you provide and tries to connect you as the SYS user.



you can verify this by picking a username that doesn't exist:
sqlplus fred/flintstone@db101 as sysdba
SQL> show user
0
 
lojaynAuthor Commented:
thanks It works fine

SQL> CONNECT MEDUSER/MEDUSER@DB101
Connected.
SQL> CREATE TABLE BDROP(COLA NUMBER);

Table created.

SQL> INSERT INTO BDROP VALUES (99);

1 row created.

SQL> INSERT INTO BDROP VALUES (100);

1 row created.

SQL> SELECT * FROM BDROP;

      COLA
----------
        99
       100

SQL> DROP TABLE BDROP;

Table dropped.

SQL> SELECT * FROM USER_RECYCLEBIN
  2  WHERE ORIGINAL_NAME='BDROP';

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION
------------------------------ -------------------------------- ---------
TYPE                      TS_NAME                        CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN
------------------- ---------- -------------------------------- --- ---
   RELATED BASE_OBJECT PURGE_OBJECT      SPACE
---------- ----------- ------------ ----------
BIN$gnSDUdcLQq+Ntfd5RWHDtw==$0 BDROP                            DROP
TABLE                     USERS                          2011-10-25:10:27:32
2011-10-25:10:29:08    1025756                                  YES YES
     51556       51556        51556          8


SQL>
0
 
sdstuberCommented:
glad I could help
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now