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: 94750
  • Last Modified:

Show Tables?

How do you run a query to show all table in Oracle?
0
aedean
Asked:
aedean
1 Solution
 
ddandekarCommented:
A  view all_tables which can show required details of all tables. View description is as below :
Connected to:
Oracle8i Release 8.1.5.0.0 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production

SQL> desc all_tables
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 IOT_NAME                                           VARCHAR2(30)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(3)
 BACKED_UP                                          VARCHAR2(1)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 AVG_SPACE_FREELIST_BLOCKS                          NUMBER
 NUM_FREELIST_BLOCKS                                NUMBER
 DEGREE                                             VARCHAR2(10)
 INSTANCES                                          VARCHAR2(10)
 CACHE                                              VARCHAR2(5)
 TABLE_LOCK                                         VARCHAR2(8)
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 PARTITIONED                                        VARCHAR2(3)
 IOT_TYPE                                           VARCHAR2(12)
 TEMPORARY                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NESTED                                             VARCHAR2(3)
 BUFFER_POOL                                        VARCHAR2(7)
 ROW_MOVEMENT                                       VARCHAR2(8)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 SKIP_CORRUPT                                       VARCHAR2(8)
 MONITORING                                         VARCHAR2(3)

SQL>
0
 
baonguyen1Commented:
ddandekar,

You can use ALL_TABLES view but this view shows only the tables to which the user has rights. If you have DBA right, use the DBA_TABLES view.

SQL>select table_names, owner from dba_tables

Hope this helps
0
 
MathiasMagnussonCommented:
Even without ful DBA access you can use the view dict. One benefit is that it lists both tables and views, so you don't have to look in xxx_tables and xxx_views to see both.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DanielztCommented:

You can query these views:

user_tables -- user owned tables
all_tables -- all tables user has access
dba_tables -- all relataional tables
dba_all_tables --all relataional tables  and object tables
dba_object_tables -- object tables
dba_catalog  -- tables,views, synonyms and sequences
0
 
n4nazimCommented:
Hi,

if u are connected to SYS or SYSTEM user u can say :

SELECT * FROM DBA_TABLES.

However if u r looking out for tables owned by a specific user .. u can say

SELECT * FROM DBA_TABLES WHERE OWNER = "owner_name';


If are not connected to SYS or SYSTEM and are connected thru some different user ( the one which u might be using to connect yr application ). use:


SELECT * FROM ALL_TABLES;

This shd give u list of all tables.


Hope this helps,
Rgds,
NHM
0
 
apolozovCommented:
select table_name from user_tables ;  - to have a list of tables you own
select table_name from all_tables    ;  - to have a list of tables you can select (yours + you are granted to select)
select table_name from dba_tables   ;  - to have a list of all tables in the db (if you're granted to see them)

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now