CODEWITHSUNDEEP
oracle-databaseoracle12c
user5381300
user5381300

Reputation:

How get Tables from current Session/User

I'm struggling to get tables of given username or current session/user; I'm able to get all tables by using select table_name from all_tables; but it also retrieve default created tables. I just want tables that we created.

Even when I retrieve tables from Pluggable database it also fetch all tables(default-tables-with-$-sign-name) but when I connect pluggable-user in ORACLE SQL DEVELOPER then it just show (user-created)tables without default tables.

I want all queries that show tables,users without default($ sign name) table,users; for sqlplus. I'm using ORACLE-12C.

Upvotes: 1

Views: 1934

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

You could query the view [DBA|ALL|USER]_TABLES depending on the privilege you have.

Querying USER_TABLES would provide all the tables owned by the current owner you have logged into.

For example,

SQL> show user
USER is "LALIT"
SQL> SELECT table_name FROM user_tables;

TABLE_NAME
--------------------------------------------------------------------------------

no rows selected

SQL> create table t(a number);

Table created.

SQL> SELECT table_name FROM user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
T

While using DBA/ALL you must filter the OWNER in UPPER CASE.

For example,

SQL> show user
USER is "SYS"
SQL> SELECT table_name FROM dba_tables WHERE OWNER='LALIT';

TABLE_NAME
--------------------------------------------------------------------------------
T

Upvotes: 1

StephaneM
StephaneM

Reputation: 4899

Just filter on the owner:

select table_name from all_tables where owner='YOUR OWNER NAME';

Upvotes: 1

Related Questions