Learn how to a build a cloud-first strategyRegister Now


View tables in oracle 11g DB

Posted on 2011-05-07
Medium Priority
Last Modified: 2016-12-08

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
  • 2
  • 2
LVL 40

Accepted Solution

mrjoltcola earned 2000 total points
ID: 35714453
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

ID: 35714506

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

mrjoltcola earned 2000 total points
ID: 35714519
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

ID: 35714568
thanks. I was able to add a user from backend.


Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month20 days, 17 hours left to enroll

810 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