1

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • 1
    just use select * from user_tables; – Florin Ghita Nov 26 '15 at 08:28
  • Possible duplicate of [Get list of all tables in Oracle?](http://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle) – Florin Ghita Nov 26 '15 at 08:29
  • @FlorinGhita I know, but this also retrieve such stupid tables that I not created. may be that could be default-tables. –  Nov 26 '15 at 08:29
  • @FlorinGhita I already created a user for given PDBORLC pluggable database and used there this command that still give me extra tables in sqlplus. but in ORACLE DEVELOPER just 4 tables, emp,dept, and 2 others. –  Nov 26 '15 at 08:35
  • @FlorinGhita should I create Local user or common user? mean username with c##? or just simple? –  Nov 26 '15 at 08:35
  • 1
    did you run **`select * from user_tables`** – Florin Ghita Nov 26 '15 at 08:36
  • @FlorinGhita yes I run that query, But I'm still getting some tables that I not created. Even for SYSTEM user(in CONTAINER) or scott user in PDBORCL. –  Nov 26 '15 at 08:39
  • 1
    system is not an user created by you. you should connect with a regular user, like scott(predefined) or another created by you. – Florin Ghita Nov 26 '15 at 08:42
  • Got it Man..!! but what query for users? mean how I get all users that created by my-user? and same from pluggable database that created given user? –  Nov 26 '15 at 08:46
  • that's another question. You can search on google. For example, I found this: http://www.orafaq.com/wiki/List_of_default_database_users – Florin Ghita Nov 26 '15 at 08:49
  • 2
    Please DO NOT use SYS or SYSTEM for your applications/tests/work. – Florin Ghita Nov 26 '15 at 08:51

2 Answers2

1

Just filter on the owner:

select table_name from all_tables where owner='YOUR OWNER NAME';
StephaneM
  • 4,779
  • 1
  • 16
  • 33
  • show `no row selected` error. even I created one table; –  Nov 26 '15 at 08:32
  • what user do you use? `select user from dual;` to find it. – Florin Ghita Nov 26 '15 at 08:32
  • I got SYSTEM. but it also retrieve tables that I not created. ( with some $ sign name ) even for SYSTEM user and SCOTT user for PDBORCL(Pluggable_databse); –  Nov 26 '15 at 08:37
1

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
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124