Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2014
    Posts
    5

    Unanswered: Owner, Username, Tablename

    Hi;
    What is the sql query command in order to see the list of all all owner, list of all users created under owner and the list of tablename created either inside owner / username

    Looking forward to hear from you.

    Thank You

    Ujjwal Rana

  2. #2
    Join Date
    Oct 2014
    Posts
    5

    ujjwalrana

    Try this,

    SELECT DISTINCT T.OWNER, T.TABLE_NAME,AU.USERNAME,UT.MONITORING , UT.TABLESPACE_NAME
    FROM DBA_TABLES T,ALL_TAB_COLUMNS TC, ALL_USERS AU, USER_TABLES UT
    WHERE T.OWNER = TC.OWNER
    AND AU.USERNAME = T.OWNER
    AND T.TABLE_NAME = UT.TABLE_NAME;

  3. #3
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    What exactly do you mean by "all users created under owner"?
    I suggest that you first read up on Oracle database structure, paying particular attention to schemas. Once you've done that then these suggestions for queries may be of use to you: http://razorsql.com/articles/oracle_system_queries.html
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  4. #4
    Join Date
    Oct 2014
    Posts
    5
    Here is what i was looking for.

    For Example first of all i logged in as

    Sqlplus / as sysdba

    And Created a user say "ONE" AND THEN AFTER CREATING USER ONE , I LOGIN INSIDE THE USER ONE AND CREATED A TABLE NAME CALLED EMPLOYEES ONE:-

    CREATE USER ONE IDENTIFIED BY PASSWORD;


    AGAIN I LOGIN AS HR NOW

    SQLPLUS HR/HR

    And Created a another user say "TWO" THEN AFTER CREATING USER TWO , I LOGIN INSIDE THE USER TWO AND CREATED A TABLE NAME CALLED SAMPLE ONE:-

    NOW I WOULD LIKE TO SEE THE OUTPUT OF ABOVE WRITTEN QUERY IN THE FOLLOWING WAY...

    OWNER USERNAME TABLE NAME
    SYS ONE EMPLOYEES ONE
    HR TWO SAMPLE ONE

    Kindly let me know if the above shown output is possible to display

    Ujjwal

  5. #5
    Join Date
    Oct 2014
    Posts
    5

    Hi ujjwalrana

    Hi ..

    Its not given the correct result to you ????

    here I have use DBA_TABLES T, ALL_USERS, may be the query is not well tuned but as you said the output I can see.

    goodluck

  6. #6
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    SYS doesn't 'own' ONE, neither does HR 'own' TWO. You really need to read up on schemas.
    Try this for starters: http://dba.stackexchange.com/questio...user-vs-schema
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  7. #7
    Join Date
    Oct 2014
    Posts
    5

    Hi

    Ok , so establish DB link and fetch the record is only the option.

  8. #8
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Quote Originally Posted by subioracle View Post
    Ok , so establish DB link and fetch the record is only the option.
    What has this got to do with this thread?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  9. #9
    Join Date
    Oct 2014
    Posts
    5

    Hi cis_groupie

    Hi cis_groupie

    Please let us know the solution, if you have any ?

  10. #10
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    *sigh* subioracle, there isn't a solution, as there is no such thing in Oracle as users being owned by an owner. If you read the links that I have provided then you should understand this.
    And you still haven't said why you want to build a dblink. What has connecting to a database from another database got to do with users' accounts?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  11. #11
    Join Date
    Oct 2014
    Posts
    5

    Hi cis_groupie

    Good , I was impression to put dblink for join and fetch the all possibilities.

  12. #12
    Join Date
    Oct 2014
    Posts
    5
    Thanks for the solution

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •