View tables in oracle 11g DB

Posted on 2011-05-07
Last Modified: 2012-06-22

I have a XML restored JIRA instance running on Linux with Oracle 11G database. I do need to add an admin account from backend (oracle db) and I do not know how to view some of tables like userbase,membershipbase from oracle database.  The oracle database name is, stagingdb

I issues select * from tab and select userbase from tab;

I can't see any tables that are associated with JIRA application. Can someone help me how to view the tables? (these tables normally JIRA writes to oracle while installing as JDBC data source)

Question by:luser9999
    LVL 40

    Accepted Solution

    Who are you connected as?

    TAB will only list the tables of the current user connected.

    You must not be logged in as the user that owns the JIRA tables. Not sure what the user is.. try

       select username from all_users

       select distinct owner from all_tables

    This will show you a list of users. Then maybe you can decide from that.

    If you dont know the username of the JIRA user, try looking in the global table list, DBA_TABLES or ALL_TABLES

      -- look for USERBASE table in all users
      select * from all_tables where table_name = 'USERBASE'

      -- Or if user is JIRA
      select * from all_tables where owner = 'JIRA'

    Lets assume the owner is JIRA and you logged in as SYSTEM, you need to describe the tables like this:

      describe JIRA.USERBASE

    Author Comment


    This is what I executed after I created new database (stgdb).
    create user jirauser identified by jirapasswd default tablespace system quota unlimited on system;
    grant connect to jirauser;
    grant create table to jirauser;

    . I am currently logged in as

    sqlplus / as sysdba

    I will try to execute your commmands and let you know
    LVL 40

    Assisted Solution

    So read what I wrote again. You must connect as the JIRAUSER to see his tables by default (without requiring to prepend the schema to table names).

    So connect as jirauser / jirapasswd

    Then things will work, like describe <table name>, or select * from <table name>

    While logged in as / (SYS) you are SYSDBA and can see all tables, like this:
       describe jirauser.<table name>

    Example: describe jirauser.userbase

    But SYSDBA is for DBA work only.  You need to connect as jirauser.


    Author Comment

    thanks. I was able to add a user from backend.


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Note: for this to work properly you need to use a Cross-Over network cable. 1. Connect both servers S1 and S2 on the second network slots respectively. Note that you can use the 1st slots but usually these would be occupied by the Service Provide…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now