I have used DROP table command to table EMP in my INFO schema. But I can still see the table structure using DESC EMP command in toad.Why is this so.Is my toad misbehaving?
Can you please check the information about this table in dictionary USER_TABLES ?
Depending on Oracle version your table may exists in recyclebin with different system generated name, can you please check in RECYLEBIN?
Just to make sure can you check in ALL_TABLES and ensure you are DESC table in right schema ? as same table name can exists in different schemas.
yes the same table name exits in other schemas.Is that the reason?
On Wed, Sep 2, 2015 at 10:35 PM, Yasser Khan bounce-Yasser_Khan@toadworld.com wrote:
Reply by Yasser Khan
Can you please check the information about this table in dictionary USER_TABLES ?
Depending on Oracle version your table may exists in recyclebin with different system generated name, can you please check in RECYLEBIN?
Just to make sure can you check in ALL_TABLES and ensure you are DESC table in right schema ? as same table name can exists in different schemas.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Oracle notifications altogether.
Toad World - Oracle Discussion Forum
Flag this post as spam/abuse.
It could be the reason. Its always preferred to qualify schema name along with the table name to avoid such type of confusion. You can even create synonyms to make it easier.
Can you please describe the table with schema name and confirm ?
DESC INFO.EMP
CREATE TABLE EMP
(EMPNO NUMBER,ENAME VARCHAR2(10BYTE),JOB VARCHAR2(9BYTE),MGR INTEGER,HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2),
DEPT NUMBER);
Insert into EMP
(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(1,‘JOHNSON’,‘ADMIN’,6,TO_DATE(’12/17/1990 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),18000,NULL,4);
Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(2,‘HARDING’,‘MANAGER’,9,TO_DATE(’02/02/1998 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),52000,300,3);Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(3,‘TAFT’,‘SALES I’,2,TO_DATE(’01/02/1996 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),25000,500,3);
Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(4,‘HOOVER’,‘SALES I’,2,TO_DATE(’04/02/1990 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),27000,NULL,3);
Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(5,‘LINCOLN’,‘TECH’,6,TO_DATE(’06/23/1994 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),22500,1400,4);
Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(6,‘GARFIELD’,‘MANAGER’,9,TO_DATE(’05/01/1993 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),54000,NULL,4);
Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(7,‘POLK’,‘TECH’,6,TO_DATE(’09/22/1997 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),25000,NULL,4);
Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(8,‘GRANT’,‘ENGINEER’,10,TO_DATE(’03/30/1997 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),32000,NULL,2);
Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(9,‘JACKSON’,‘CEO’,NULL,TO_DATE(’01/01/1990 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),75000,NULL,4);
Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(10,‘FILLMORE’,‘MANAGER’,9,TO_DATE(’08/09/1994 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),56000,NULL,2);
Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(11,‘ADAMS’,‘ENGINEER’,10,TO_DATE(’03/15/1996 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),34000,NULL,2);
Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(12,‘WASHINGTON’,‘ADMIN’,6,TO_DATE(’04/16/1998 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),18000,NULL,4);Insert into EMP
(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(13,‘MONROE’,‘ENGINEER’,10,TO_DATE(’12/03/2000 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),30000,NULL,2);
Insert into EMP(EMPNO, ENAME,JOB, MGR, HIREDATE, SAL, COMM, DEPT)
Values(14,‘ROOSEVELT’,‘CPA’,9,TO_DATE(’10/12/1995 00:00:00′,‘MM/DD/YYYY HH24:MI:SS’),35000,NULL,1);
Commit;
I had problem running thie above script. I had to create the table first and then insert the data with some messages of invalid identifiers.After emptying the recyclebin I was still able to use the desc emp command.Next time I will drop the table using the schema name
Yes when I use desc info.emp; it says object not found.
what is the command used to view all_tables in the schema?
Error message “object not found” confirms that object no longer exists OR you do not have privilege to access this object.
USER_TABLES and ALL_TABLES are dictionary views. USER_TABLES will contain information of all the tables owned by the user by which you have connected. ALL_TABLES will contain information of all tables to which connected user has access.
There are many columns in each of these dictionary views, to select all the columns you can simply perform below queries.
SELECT * FROM USER_TABLES;
SELECT * FROM ALL_TABLES;