Home » SQL & PL/SQL » SQL & PL/SQL » get all non system tables & views
get all non system tables & views [message #225668] Tue, 20 March 2007 16:51 Go to next message
darrylwhitmore
Messages: 4
Registered: March 2007
Junior Member
Hello, I'm a SQL Server guy but need to make a change to an Oracle query in our application, and I'm pretty lost.

The application presents a list of tables and views to the user, and allows selection. In our application, the user would never need to select any system (data dictionary) tables, and our current sql statements, shown below, populate the selection list with tons of system objects such that it is very difficult for the user to separate the wheat (non-system objects) from the chaffe (system objects):

SELECT table_name FROM all_tables
SELECT view_name FROM all_views

As a result, my job is to modify or create new sql queries that filter out all the system objects. I can't seem to find any flags in the all_tables or all_views views that would indicate that the row contains a system object. I've Googled and Googled and haven't found any samples that do the job. It doesn't look like it is a good idea to filter out objects with dollar signs ($) in the name, as there seems to be nothing preventing anyone from creating a table with a dollar sign in the name. Also, it doesn't seem like it is a good idea to filter out objects in the SYSTEM, SYSAUX or TEMP tablespaces, as it seems as if one could create objects in these namespaces.

I'd appreciate any help. I have to think that this is easy enough to do if you know what you're doing (and I don't!) Thanks in advance!



Re: get all non system tables & views [message #225673 is a reply to message #225668] Tue, 20 March 2007 17:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Why are you using all_* tables/views?
Use user_tables/views which will hold only the user owned objects. All_tables/views will list any table/data dictionary view that user is allowed to view ( may be from other users and that includes many common dictionary views owned by sys).
Re: get all non system tables & views [message #225785 is a reply to message #225673] Wed, 21 March 2007 08:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming your DBA has set the database up nicely, no users should have the SYSTEM tablespace as their default tablespace.

You can check this with
SELECT username 
FROM   dba_users
WHERE  default_tablepspace = 'SYSTEM'


If this is the case, you could use:
SELECT * 
FROM   ALL_TABLES 
WHERE owner NOT IN (SELECT username 
                    FROM   DBA_USERS 
                    WHERE  default_tablespace = 'SYSTEM');


Otherwise, slap your DBA, and get him to fix it. While you're waiting for this to happen, try
SELECT * 
FROM   ALL_TABLES 
WHERE owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
AND   owner NOT IN (SELECT username 
                     FROM   DBA_USERS 
                     WHERE  account_status != 'OPEN');
This assumes that all the various utility accounts that come with Oracle have been properly locked down. If they haven't, beat your DBA some more.
Re: get all non system tables & views [message #225817 is a reply to message #225785] Wed, 21 March 2007 10:43 Go to previous messageGo to next message
darrylwhitmore
Messages: 4
Registered: March 2007
Junior Member
JRowbottom, thanks so much for the help. Part of the problem is that we have no control over our customers' DBAs, so if they have not set things up according to established conventions, we may end up filtering out tables that the users would expect to see.

However, I have to think that it should be fairly safe to assume such commonsense conventions would be in place. So, I've come up with these queries, based on yours, that seem to produce the right results in our development Oracle database, at least:

SELECT * 
FROM   ALL_TABLES 
WHERE owner NOT IN (SELECT username 
                    FROM   DBA_USERS 
                    WHERE  default_tablespace in ('SYSTEM', 'SYSAUX') OR account_status != 'OPEN')

SELECT * 
FROM   ALL_VIEWS 
WHERE owner NOT IN (SELECT username 
                    FROM   DBA_USERS 
                    WHERE  default_tablespace in ('SYSTEM', 'SYSAUX') OR account_status != 'OPEN')


Let me know if you see anything awry, and thanks again!
Re: get all non system tables & views [message #225818 is a reply to message #225673] Wed, 21 March 2007 10:47 Go to previous messageGo to next message
darrylwhitmore
Messages: 4
Registered: March 2007
Junior Member
Quote:

Why are you using all_* tables/views?



Mahesh, we do need to allow selection of all tables/views that the user has access to, not just those that he or she created.
Re: get all non system tables & views [message #225823 is a reply to message #225817] Wed, 21 March 2007 11:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Those look ok to me.
Re: get all non system tables & views [message #225859 is a reply to message #225823] Wed, 21 March 2007 14:53 Go to previous messageGo to next message
darrylwhitmore
Messages: 4
Registered: March 2007
Junior Member
Drat, I had been testing using a DBA account, but the users running our application will not necessarily be DBAs and thus won't have access to DBA_USERS. Any other ideas for doing this without access to the DBA_ views?
Re: get all non system tables & views [message #225950 is a reply to message #225859] Thu, 22 March 2007 01:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
- Create a view over the DBA_ view(s) you need and grant select on that.
- Hide everything in a (packaged) procedure or function
- Create a view containing your logic and grant select on that.

Note: do not create these objects as SYS.

[Updated on: Thu, 22 March 2007 01:27]

Report message to a moderator

Re: get all non system tables & views [message #225991 is a reply to message #225950] Thu, 22 March 2007 03:52 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Uuse ALL_TABLES and ALL_VIEWS
Previous Topic: Tuning Query
Next Topic: Dealing with Foreign Language Characters
Goto Forum:
  


Current Time: Sat Dec 03 20:23:40 CST 2016

Total time taken to generate the page: 0.16633 seconds