SELECT owner, table_name
FROM dba_tables
This is assuming that you have access to the DBA_TABLES
data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY
privilege or the SELECT_CATALOG_ROLE
role (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYS
and SYSTEM
which have large numbers of Oracle tables that you probably don't care about.
Alternatively, if you do not have access to DBA_TABLES
, you can see all the tables that your account has access to through the ALL_TABLES
view:
SELECT owner, table_name
FROM all_tables
Although, that may be a subset of the tables available in the database (ALL_TABLES
shows you the information for all the tables that your user has been granted access to).
If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES
:
SELECT table_name
FROM user_tables
Since USER_TABLES
only has information about the tables that you own, it does not have an OWNER
column – the owner, by definition, is you.
Oracle also has a number of legacy data dictionary views-- TAB
, DICT
, TABS
, and CAT
for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB
and CAT
views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES
views all filter those out. CAT
also shows information about materialized view logs with a TABLE_TYPE
of "TABLE" which is unlikely to be what you really want. DICT
combines tables and synonyms and doesn't tell you who owns the object.
I know this is an old thread, but I just stumbled upon it and I feel this has not been explained completely.
There is a huge difference in SQL*Plus between the meaning of a /
and a ;
because they work differently.
The ;
ends a SQL statement, whereas the /
executes whatever is in the current "buffer". So when you use a ;
and a /
the statement is actually executed twice.
You can easily see that using a /
after running a statement:
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:37:20 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options
SQL> drop table foo;
Table dropped.
SQL> /
drop table foo
*
ERROR at line 1:
ORA-00942: table or view does not exist
In this case one actually notices the error.
But assuming there is a SQL script like this:
drop table foo;
/
And this is run from within SQL*Plus then this will be very confusing:
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:38:05 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options
SQL> @drop
Table dropped.
drop table foo
*
ERROR at line 1:
ORA-00942: table or view does not exist
The /
is mainly required in order to run statements that have embedded ;
like CREATE PROCEDURE
,CREATE FUNCTION
,CREATE PACKAGE
statements and for any BEGIN...END
blocks.
Best Answer
Execute
is sql*plus syntax .. try wrapping your call in begin .. end like this:(Although Jeffrey says this doesn't work in APEX .. but you're trying to get this to run in SQLDeveloper .. try the 'Run' menu there.)