Home » SQL & PL/SQL » SQL & PL/SQL » What query do I use to list the primary key (Oracle 9i)
What query do I use to list the primary key [message #305238] Mon, 10 March 2008 04:03 Go to next message
*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
anyone Knows

What query do I use to list the primary key and foreign key? with references table names?

and

SQL> conn system
Enter password: ********
Connected.
SQL> DESC DBa_USERS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

----------------------------------------------------------------

SQL> SELECT USERNAME,USER_ID FROM DBA_USERS WHERE USERNAME LIKE 'P%';

USERNAME                          USER_ID
------------------------------ ----------
PROJECT                                65
PM                                     48

------------------------------------------------------------
SQL> DELETE FROM DBA_USERS WHERE USERNAME ='PROJECT' AND USER_ID=65;
DELETE FROM DBA_USERS WHERE USERNAME ='PROJECT' AND USER_ID=65
            *
ERROR at line 1:
ORA-01031: insufficient privileges

------------------------------------------------------------
SQL> GRANT DBA TO SYSTEM ;

Grant succeeded.
------------------------------------------------------------
SQL>  DELETE FROM DBA_USERS WHERE USERNAME ='PROJECT' AND USER_ID=65;
 DELETE FROM DBA_USERS WHERE USERNAME ='PROJECT' AND USER_ID=65
             *
ERROR at line 1:
ORA-01031: insufficient privileges


WHEN IM TRYING TO DELETE USER FROM DBA USERS ITS SHOWS INSUFFICIENT PRIVILAGES EVEN THOUGH I GRANT DBA TO SYSTEM AFTER THAN SAME PROBLEM ANYONE EXPLAIN WHY THIS HAPPENS ?

Regards

Munnabhai
Re: What query do I use to list the primary key [message #305243 is a reply to message #305238] Mon, 10 March 2008 04:11 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
What exactly is your question?how is your subject related to what you have actually posted?

1- to find the primary/foreign keys for tables
USER_CONSTRAINTS

2- to drop a user you need to use the DROP command and not the DELETE.And i don't see any reason why are you granting the DBA privs to SYSTEM.The SYSTEM user has already the DBA privs.

And i request you to read the oracle docs you will find all the answers for your questions.

regards,
Re: What query do I use to list the primary key [message #305246 is a reply to message #305238] Mon, 10 March 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't delete from Oracle catalog.
Please read SQL Reference.

Regards
Michel
Re: What query do I use to list the primary key [message #305249 is a reply to message #305238] Mon, 10 March 2008 04:19 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You cannot (or are not supposed to be able to) delete from dictionary views like DBA_USERS. Try this instead:

DROP USER PROJECT CASCADE;


PS: Run a backup first!!!
Re: What query do I use to list the primary key [message #305251 is a reply to message #305238] Mon, 10 March 2008 04:21 Go to previous messageGo to next message
*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
Thanks Guyz user is deleted actually it is connected thatz why not deleting....



Regards
Re: What query do I use to list the primary key [message #305255 is a reply to message #305251] Mon, 10 March 2008 04:29 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
user is deleted actually it is connected thatz why not deleting....

No, the reason is that you can't delete a user with "delete dba_users" but with "drop user"

Regards
Michel
Previous Topic: sql query
Next Topic: Variable formatting
Goto Forum:
  


Current Time: Tue Dec 06 12:29:01 CST 2016

Total time taken to generate the page: 0.25024 seconds