Solved

Oracle 11g: sqlplus: command to display list of tables?

Posted on 2013-01-03
7
1,321 Views
Last Modified: 2013-01-03
Hello Experts,

via sqlplus is there a way to display all tables?
also, is there a way to find out when was the last time the db updated?

Regards,
0
Comment
Question by:epifanio67
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38740396
select table_name from all_tables;
or the dba_tables view if you have permission (user_tables for the ones the user owns).

>>the last time the db updated?

I don't think this is available unless you are auditing the tables.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38740417
There is a last_ddl_time in all_objects (same for the DBA nad USER views) that will show the last time the object was modofied if that helps?
0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 250 total points
ID: 38740510
you can use ORA_ROWSCN to find out when was the last time a particular table updated, by running

SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN )) FROM TableName;

Open in new window


or just

SELECT MAX(ORA_ROWSCN ) FROM TableName;

Open in new window

0
Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38740662
>>you can use ORA_ROWSCN

This is not a 100% guarantee.  It should be a good estimate though.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns007.htm#SQLRF50953

This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking. Refer to CREATE TABLE ... NOROWDEPENDENCIES | ROWDEPENDENCIES for more information on row-level dependency tracking.
0
 

Author Comment

by:epifanio67
ID: 38740734
Thank you experts,

the problem I am having is that I don't know the tables names... or have access to ERD... so, I can't run a simple query:

select table_name from all_tables

see what I mean?

I wanted to see if I could display all tables via command line...

Thanks experts...
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 38740745
>>I wanted to see if I could display all tables via command line...

Connect to the database using sqlplus (or sql developer or ???) and issue that select statement.

If you don't connect to the database, you cannot see the tables inside the database.
0
 

Author Closing Comment

by:epifanio67
ID: 38740911
Thank you experts...<br />I really appreciate your help
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question