Home » SQL & PL/SQL » SQL & PL/SQL » how to know partticular user existed or not in datbase? (oracle 11g)
icon5.gif  how to know partticular user existed or not in datbase? [message #582701] Mon, 22 April 2013 01:12 Go to next message
mailtoyvkr@gmail.com
Messages: 1
Registered: April 2013
Location: hyderabad
Junior Member
May i want to know SQL command to find particular table name in a particular user account existed or not in oracle 11g database
Re: how to know partticular user existed or not in datbase? [message #582702 is a reply to message #582701] Mon, 22 April 2013 01:23 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Query ALL_TABLES; for example:
SQL> select table_name
  2  from all_tables
  3  where owner = 'SCOTT'
  4    and table_name = 'EMP';

TABLE_NAME
------------------------------
EMP

SQL>
Re: how to know partticular user existed or not in datbase? [message #582708 is a reply to message #582701] Mon, 22 April 2013 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can check if a catalog view exists for your need either in checking Database Reference or querying DICT view, for instance:
SQL> select * from dict
  2  where lower(comments) like '%user%tables%' or comments like '%tables%accessible to%user%'
  3  order by 1
  4  /
TABLE_NAME                     COMMENTS
------------------------------ ----------------------------------------------------------------------
ALL_ALL_TABLES                 Description of all object and relational tables accessible to the user
ALL_CATALOG                    All tables, views, synonyms, sequences accessible to the user
ALL_CONS_OBJ_COLUMNS           List of types an object column or attribute is constrained to in the t
                               ables accessible to the user
ALL_EXTERNAL_LOCATIONS         Description of the external tables locations accessible to the user
ALL_EXTERNAL_TABLES            Description of the external tables accessible to the user
ALL_INDEXES                    Descriptions of indexes on tables accessible to the user
ALL_IND_STATISTICS             Optimizer statistics for all indexes on tables accessible to the user
ALL_INTERNAL_TRIGGERS          Description of the internal triggers on the tables accessible to the u
                               ser
ALL_LOBS                       Description of LOBs contained in tables accessible to the user
ALL_MVIEW_DETAIL_RELATIONS     Description of the materialized view detail tables accessible to the u
                               ser
ALL_NESTED_TABLES              Description of nested tables in tables accessible to the user
ALL_OBJECT_TABLES              Description of all object tables accessible to the user
ALL_OBJ_COLATTRS               Description of object columns and attributes contained in the tables a
                               ccessible to the user
ALL_PARTIAL_DROP_TABS          All tables with patially dropped columns accessible to the user
ALL_PENDING_CONV_TABLES        All tables accessible to the user which are not upgraded to the latest
                                type version
ALL_QUEUE_TABLES               All queue tables accessible to the user
ALL_REFS                       Description of REF columns contained in tables accessible to the user
ALL_REPAUDIT_COLUMN            Information about columns in all shadow tables for replicated tables w
                               hich are accessible to the user
ALL_REPCOLUMN_GROUP            All column groups of replicated tables which are accessible to the use
                               r
ALL_REPCONFLICT                All conflicts with available resolutions for user's replicated tables
ALL_REPGROUPED_COLUMN          Columns in the all column groups of replicated tables which are access
                               ible to the user
ALL_REPPARAMETER_COLUMN        All columns used for resolving conflicts in replicated tables which ar
                               e accessible to the user
ALL_REPRESOLUTION              Description of all conflict resolutions for replicated tables which ar
                               e accessible to the user
ALL_REPRESOLUTION_STATISTICS   Statistics for conflict resolutions for replicated tables which are ac
                               cessible to the user
ALL_REPRESOL_STATS_CONTROL     Information about statistics collection for conflict resolutions for r
                               eplicated tables which are accessible to the user
ALL_SOURCE_TABLES              Synonym for USER_SOURCE_TABLES
ALL_SUBSCRIBED_TABLES          Synonym for USER_SUBSCRIBED_TABLES
ALL_TABLES                     Description of relational tables accessible to the user
ALL_TAB_COLS                   Columns of user's tables, views and clusters
ALL_TAB_COLUMNS                Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS         Columns of user's tables, views and clusters
ALL_TAB_COMMENTS               Comments on tables and views accessible to the user
ALL_TAB_STATISTICS             Optimizer statistics for all tables accessible to the user
ALL_TRIGGER_COLS               Column usage in user's triggers or in triggers on user's tables
ALL_TSTZ_TABLES                Description of tables accessible to the user, which have column(s) def
                               ined on timestamp with time zone data type or ADT type containing attr
                               ibute(s) of timestamp with time zone data type
ALL_TSTZ_TAB_COLS              Columns of user's tables, which have column(s) defined on timestamp wi
                               th time zone data type or ADT type containing attribute(s) of timestam
                               p with time zone data type
ALL_UNUSED_COL_TABS            All tables with unused columns accessible to the user
ALL_VARRAYS                    Description of varrays in tables accessible to the user
DBA_TAB_COLS                   Columns of user's tables, views and clusters
DBA_TAB_COLUMNS                Columns of user's tables, views and clusters
DBA_TAB_COL_STATISTICS         Columns of user's tables, views and clusters
DBA_VARRAYS                    Description of varrays in tables accessible to the user
TABS                           Synonym for USER_TABLES
USER_COL_COMMENTS              Comments on columns of user's tables and views
USER_CONSTRAINTS               Constraint definitions on user's own tables
USER_EXTERNAL_LOCATIONS        Description of the user's external tables locations
USER_EXTERNAL_TABLES           Description of the user's own external tables
USER_FLASHBACK_ARCHIVE_TABLES  Information about the user tables that are enabled for Flashback Archi
                               ve
USER_FREE_SPACE                Free extents in tablespaces accessible to the user
USER_IND_COLUMNS               COLUMNs comprising user's INDEXes and INDEXes on user's TABLES
USER_IND_EXPRESSIONS           Functional index expressions in user's indexes and indexes on user's t
                               ables
USER_INTERNAL_TRIGGERS         Description of the internal triggers on the user's own tables
USER_LOBS                      Description of the user's own LOBs contained in the user's own tables
USER_LOG_GROUPS                Log group definitions on user's own tables
USER_NESTED_TABLES             Description of nested tables contained in the user's own tables
USER_OBJECT_TABLES             Description of the user's own object tables
USER_OBJ_AUDIT_OPTS            Auditing options for user's own tables and views with atleast one opti
                               on set
USER_PARTIAL_DROP_TABS         User tables with unused columns
USER_PENDING_CONV_TABLES       All user's tables which are not upgraded to the latest type version
USER_REFS                      Description of the user's own REF columns contained in the user's own
                               tables
USER_REPAUDIT_COLUMN           Information about columns in all shadow tables for user's replicated t
                               ables
USER_REPCOLUMN_GROUP           All column groups of user's replicated tables
USER_REPFLAVOR_COLUMNS         Replicated columns from current user's tables in flavors
USER_REPGROUPED_COLUMN         Columns in the all column groups of user's replicated tables
USER_REPPARAMETER_COLUMN       All columns used for resolving conflicts in user's replicated tables
USER_REPRESOLUTION             Description of all conflict resolutions for user's replicated tables
USER_REPRESOLUTION_STATISTICS  Statistics for conflict resolutions for user's replicated tables
USER_REPRESOL_STATS_CONTROL    Information about statistics collection for conflict resolutions for u
                               ser's replicated tables
USER_TABLES                    Description of the user's own relational tables
USER_TAB_COLS                  Columns of user's tables, views and clusters
USER_TAB_COLUMNS               Columns of user's tables, views and clusters
USER_TAB_COL_STATISTICS        Columns of user's tables, views and clusters
USER_TAB_HISTOGRAMS            Histograms on columns of user's tables
USER_TAB_STATISTICS            Optimizer statistics of the user's own tables
USER_TSTZ_TABLES               Description of the user's own tables, which have column(s) defined on
                               timestamp with time zone data type or ADT type containing attribute(s)
                                of timestamp with time zone data type
USER_TSTZ_TAB_COLS             Columns of user's tables, which have column(s) defined on timestamp wi
                               th time zone data type or ADT type containing attribute(s) of timestam
                               p with time zone data type
USER_UNUSED_COL_TABS           User tables with unused columns
USER_VARRAYS                   Description of varrays contained in the user's own tables
USER_XML_TABLES                Description of the user's own XMLType tables
USER_XML_TAB_COLS              Description of the user's own XMLType tables
Re: how to know partticular user existed or not in datbase? [message #582768 is a reply to message #582701] Mon, 22 April 2013 08:39 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Hi mailtoyvkr@gmail.com,

As you said ' i want to know SQL command to find particular table name in a particular user account existed or not in oracle 11g database '

There are two data dictionaries avaliable for this task, and It's depend on what type of privilege you have assigned,

1) DBA_TABLES -- It will be available for DBA privilege users.
2) ALL_TABLES -- It is for normal users.

From ALL_TABLES you can see only tables on which you have priviledge granted by users on tables.
through DBA_TABLES you can see all users tables in your database.

I am assuming you as normal user and both Dictionary have same structure you can use it only little modification in FORM clause

SELECT table_name , owner 
FROM all_tables 
WHERE table_name = &tabname AND owner =&username ;


Cool
Re: how to know partticular user existed or not in datbase? [message #582775 is a reply to message #582768] Mon, 22 April 2013 09:09 Go to previous messageGo to next message
joy_division
Messages: 4515
Registered: February 2005
Location: East Coast USA
Senior Member
ashwani0301 wrote on Mon, 22 April 2013 09:39

SELECT table_name , owner 
FROM all_tables 
WHERE table_name = &tabname AND owner =&username ;



This will not work. Strings variables need to be in single quotes (unless you include the quotes as part of the value).
Re: how to know partticular user existed or not in datbase? [message #582781 is a reply to message #582775] Mon, 22 April 2013 09:21 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Mad Once again i did syntax mistake Sorry Sir Smile ,

Thank you sir,

 SELECT table_name , owner 
FROM all_tables 
WHERE table_name = '&tabname' AND owner ='&username' ;



[Updated on: Mon, 22 April 2013 09:22]

Report message to a moderator

Re: how to know partticular user existed or not in datbase? [message #582788 is a reply to message #582781] Mon, 22 April 2013 10:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2031
Registered: January 2010
Senior Member
Just curious what any of replies have to do with OP's question which is "May i want to know SQL command to find particular table name in a particular user account existED or not in oracle 11g database"?

SY.

[Updated on: Mon, 22 April 2013 10:46]

Report message to a moderator

Re: how to know partticular user existed or not in datbase? [message #582810 is a reply to message #582788] Mon, 22 April 2013 15:06 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's my reason: it is obvious that I'm not a native English speaker. Most of us aren't. Therefore: someone who says "may I want to know" + composition of the rest of the sentence made me switch my built-in brain translator to "I feel lucky" option and the outcome was: that man wants to know whether that very table exists somewhere in his database or not (but said that awkwardly, kind of).

I'd also put a fair amount of money betting that my sentences sound stupid to people who were born (or live long enough) in, say, Manchester, New York US of A, Boston or Sydney.

Anyway: as the OP never replied to any of our attempts, we can't tell for sure what he really meant.
Re: how to know partticular user existed or not in datbase? [message #582831 is a reply to message #582810] Mon, 22 April 2013 23:07 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Well said littlefoot Laughing
Previous Topic: how to list all tables that are connected to each other with constraints and list them all together
Next Topic: show number of days between two date
Goto Forum:
  


Current Time: Sat Sep 20 19:37:50 CDT 2014

Total time taken to generate the page: 0.10011 seconds