Solved

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

Posted on 2013-01-03
7
1,289 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
This video shows how to recover a database from a user managed backup

760 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

20 Experts available now in Live!

Get 1:1 Help Now