Newbie Questions (need your help) easy questions for you. 3

[Sina]

Hello everyone.
I have couple of newbie questions, if you could kindly help.
here they are:
1. what is the command to get a list of the table spaces in oracle ?
2. How do I get a list of all the primary keys on a table ?
3. How do I get a list of all the foriegn keys on a table ?
4. How do I get a list of all the indexes on a table ?
5. How do I get a description on a table in oracle (like field defenitions).
6. How do I find out the list of users with access to a specific table / table space?
7. How do I get a list of all the valid table fileds TYPES in oracle?

thank you all very much.

 

[amittuli]

Hi Sina
Right now, I'm able to answer two questions of yours. they are as follwos :
1. what is the command to get a list of the table spaces in oracle ?
select tablespace_name from dba_tablespaces;
5. How do I get a description on a table in oracle (like field defenitions).
desc schema_name.table_name.
Schema name is optional. eg desc dba_tables;

 

[amittuli]

Hi Sina
Some more commands for you.

2. How do I get a list of all the primary keys on a table ?
select COLUMN_NAME, a.table_name, constraint_type from all_cons_columns a, all_constraints b where b.CONSTRAINT_NAME= a.CONSTRAINT_NAME and a.CONSTRAINT_TYPE='P' a.table_name=<table_name>;

3. How do I get a list of all the foriegn keys on a table ?
select COLUMN_NAME, a.table_name, constraint_type from all_cons_columns a, all_constraints b where b.CONSTRAINT_NAME= a.CONSTRAINT_NAME and a.CONSTRAINT_TYPE='R' a.table_name=<table_name>;

For knowing about the FOREIGN KEYS, CONSTRAINT_TYPE will be R and for primary key, CONSTRAINT_TYPE will be P

4. How do I get a list of all the indexes on a table ?
select INDEX_NAME, INDEX_TYPE, TABLE_NAME from all_indexes where table_name=<table_name>;

Bye.
Amit

 

[jee]

Here are a couple more:

6) How do I find out the list of users with access to a specific table / table space?

For tables:
Select GRANTEE from DBA_TAB_PRIVS where TABLE_NAME = 'xxx';
Since GRANTEE can be a role, you will need to join with DBA_ROLE_PRIVS if you want the user names.

For tablespaces:
Select USERNAME from DBA_TS_QUOTAS where TABLESPACE_NAME = 'xxx';

7) How do I get a list of all the valid table fileds TYPES in oracle?
If you are referring to Oracle built-in datatypes, these are listed in the Oracle SQL Reference manual. If you don't have access to this manual, it is available online at the Oracle Technet site:

http://otn.oracle.com/doc/oracle8i_816/server.816/a76989/toc.htm

 

[Turkbear]

Here is another, and more valuable tip:
( and is not meant to be rude or inconsiderate of the difficulties with learning Oracle )

Read , read, and read some more of the Oracle Docs...

Oracle is NOT a tool you can use efficiently by just 'winging' it -

The link you have been provided to the Oracle Docs, as well as the docs on your install CD ( if you use one) are the best resource for basic questions like yours...

Knowing how to find information yourself is even better than knowing how to ask others...

happy hunting,

 

[Sina]

Thank you all for the best support

 

[Sina]

Thank you all for your wonderfull help.