Solved

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

Posted on 2013-01-03
7
1,292 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
  • 4
  • 2
7 Comments
 
LVL 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now