Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1405
  • Last Modified:

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

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
epifanio67
Asked:
epifanio67
  • 4
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
YZlatCommented:
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
Technology Partners: 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!

 
slightwv (䄆 Netminder) Commented:
>>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
 
epifanio67Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
epifanio67Author Commented:
Thank you experts...<br />I really appreciate your help
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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