Solved

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

Posted on 2013-01-03
7
1,315 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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create a Calendar table 29 43
Queries: Select, then Append, then Delete 8 40
oracle query 4 30
Access 2003, find all instances of database ODBC 3 48
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

734 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