Ggtcf

why `nmap 192.168.1.97` returns less services than `nmap 127.0.0.1`?

How do you respond to a colleague from another team when they're wrongly expecting that you'll help them?

Flux received by a negative charge

Is Asuka Langley-Soryu disgusted by Shinji?

What linear sensor for a keyboard?

Longest common substring in linear time

getting the weights of intermediate layer in keras

Engineer refusing to file/disclose patents

Should I install hardwood flooring or cabinets first?

Question about alert, surprise, and crit failing

THT: What is a squared annular “ring”?

Is there a single word describing earning money through any means?

Has Darkwing Duck ever met Scrooge McDuck?

Journal losing indexing services

Proving a function is onto where f(x)=|x|.

Is there a conventional notation or name for the slip angle?

Python script not running correctly when launched with crontab

A social experiment. What is the worst that can happen?

Is it improper etiquette to ask your opponent what his/her rating is before the game?

Delete database accidentally by a bash, rescue please

Why does the Sun have different day lengths, but not the gas giants?

Is a file system driver implemented using a kernel module in Linux?

Are lightweight LN wallets vulnerable to transaction withholding?

Has any country ever had 2 former presidents in jail simultaneously?

How can I query only non-system databases in an Oracle connection?

Oracle SQL Query for listing all Schemas in a DBHow does database indexing work?How to select the nth row in a SQL database table?How do I find duplicate values in a table in Oracle?How can I prevent SQL injection in PHP?How to list the tables in a SQLite database file that was opened with ATTACH?How to return only the Date from a SQL Server DateTime datatypeCan I concatenate multiple MySQL rows into one field?How do I limit the number of rows returned by an Oracle query after ordering?Oracle: If Table ExistsWhat are the options for storing hierarchical data in a relational database?

2

1

I want to be able to display a list of all user databases in an Oracle connection, excluding the system databases.

There is a way to differentiate between user tables and system tables within a database. But I could not find any way how I can filter out all system databases.

Does anyone know how it can be achieved ?

sql database oracle system

share|improve this question

asked Mar 8 at 5:58

Mohammed RaqeebMohammed Raqeeb

45111

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  blog.dbi-services.com/oracle-system-schemas-vs-created-users
  
  – T.S.
  Mar 8 at 6:09
  

  
  
  
  

add a comment | 

2

1

I want to be able to display a list of all user databases in an Oracle connection, excluding the system databases.

There is a way to differentiate between user tables and system tables within a database. But I could not find any way how I can filter out all system databases.

Does anyone know how it can be achieved ?

sql database oracle system

share|improve this question

asked Mar 8 at 5:58

Mohammed RaqeebMohammed Raqeeb

45111

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  blog.dbi-services.com/oracle-system-schemas-vs-created-users
  
  – T.S.
  Mar 8 at 6:09
  

  
  
  
  

add a comment | 

I want to be able to display a list of all user databases in an Oracle connection, excluding the system databases.

There is a way to differentiate between user tables and system tables within a database. But I could not find any way how I can filter out all system databases.

Does anyone know how it can be achieved ?

sql database oracle system

share|improve this question

asked Mar 8 at 5:58

Mohammed RaqeebMohammed Raqeeb

45111

share|improve this question

asked Mar 8 at 5:58

Mohammed RaqeebMohammed Raqeeb

45111

share|improve this question

asked Mar 8 at 5:58

Mohammed RaqeebMohammed Raqeeb

45111

asked Mar 8 at 5:58

Mohammed RaqeebMohammed Raqeeb

45111

asked Mar 8 at 5:58

Mohammed RaqeebMohammed Raqeeb

45111

2 Answers
2

active

oldest

votes

2

When connecting to oracle you will normally connect to ONE database, and within that database you will have a number of schemas. These schemas would be seen as "databases" in some other rdbms systems. Each schema is owned by a DB-User that can be a system- or normal user. All tables/objects created in a schema is "owned" by the owner of the schema.

So a rough method to separate the system tables from user tables will be to consider tables in a schemas owned by a system user as system tables.

If in a database with version 12c and above you can do like this:

select t.table_name, t.owner, u.oracle_maintained
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

If version below 12c the oracle_maintained column does not exist so you need this work-around. The 35 should be set to the highest used_id for system users. System users are normally the lowest in the database.

select t.table_name, t.owner, case when u.user_id > 35 then 'N' else 'Y' end systemgenerated
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

Also: the 'and rownum < 100' should be replaced by your own filter on tables.

share|improve this answer

answered Mar 8 at 9:12

F.MadsenF.Madsen

44225

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thank you! My customer however is specifically interested in adding a filter on the databases / schemas rather than the tables. Is there a query (as a join of such tables) that I can run to list only the non-system databases/schemas ? thanks again.
  
  – Mohammed Raqeeb
  Mar 11 at 10:51
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Also as I understand, the Oracle doc says ORACLE_MAINTAINED column indicates whether the user/schema is System or non-system. If so, can we not filter by this column directly to check for non-system databases ? References: dba-oracle.com/t_oracle_maintained_dba_users.htm docs.oracle.com/database/121/REFRN/…
  
  – Mohammed Raqeeb
  Mar 11 at 11:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Then it is easy. Normally there will be one schema for one user. So just query "Select username, oracle_maintained form dba_user where <your filter>..."
  
  – F.Madsen
  Mar 11 at 16:33
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you. The precise query that I needed was slightly different however. I have quoted it below as an answer. Nevertheless, thanks for giving the initial direction.
  
  – Mohammed Raqeeb
  Mar 12 at 10:05
  

  
  
  
  

add a comment | 

0

I could come up with the exact answer to my question taking help from another post - Oracle SQL Query for listing all Schemas in a DB

Here is the query that I needed :

> SELECT * FROM dba_users u WHERE EXISTS (SELECT 1 FROM dba_objects o
> WHERE o.owner = u.username ) AND default_tablespace not in
> ('SYSTEM','SYSAUX') and ACCOUNT_STATUS = 'OPEN'

This will list all the Non-System users/schemas (aka databases) from your database. Make sure you have logged in as SYS user or you grant "GRANT SELECT ON dba_users TO the_non_sys_user"

share|improve this answer

edited Mar 12 at 11:04

answered Mar 12 at 10:00

Mohammed RaqeebMohammed Raqeeb

45111

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes that might work as long as you have already created at least one object for the non-system-user.
  
  – F.Madsen
  Mar 12 at 10:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, just what I needed :)
  
  – Mohammed Raqeeb
  Mar 12 at 11:04
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55057513%2fhow-can-i-query-only-non-system-databases-in-an-oracle-connection%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

When connecting to oracle you will normally connect to ONE database, and within that database you will have a number of schemas. These schemas would be seen as "databases" in some other rdbms systems. Each schema is owned by a DB-User that can be a system- or normal user. All tables/objects created in a schema is "owned" by the owner of the schema.

So a rough method to separate the system tables from user tables will be to consider tables in a schemas owned by a system user as system tables.

If in a database with version 12c and above you can do like this:

select t.table_name, t.owner, u.oracle_maintained
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

If version below 12c the oracle_maintained column does not exist so you need this work-around. The 35 should be set to the highest used_id for system users. System users are normally the lowest in the database.

select t.table_name, t.owner, case when u.user_id > 35 then 'N' else 'Y' end systemgenerated
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

Also: the 'and rownum < 100' should be replaced by your own filter on tables.

share|improve this answer

answered Mar 8 at 9:12

F.MadsenF.Madsen

44225

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thank you! My customer however is specifically interested in adding a filter on the databases / schemas rather than the tables. Is there a query (as a join of such tables) that I can run to list only the non-system databases/schemas ? thanks again.
  
  – Mohammed Raqeeb
  Mar 11 at 10:51
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Also as I understand, the Oracle doc says ORACLE_MAINTAINED column indicates whether the user/schema is System or non-system. If so, can we not filter by this column directly to check for non-system databases ? References: dba-oracle.com/t_oracle_maintained_dba_users.htm docs.oracle.com/database/121/REFRN/…
  
  – Mohammed Raqeeb
  Mar 11 at 11:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Then it is easy. Normally there will be one schema for one user. So just query "Select username, oracle_maintained form dba_user where <your filter>..."
  
  – F.Madsen
  Mar 11 at 16:33
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you. The precise query that I needed was slightly different however. I have quoted it below as an answer. Nevertheless, thanks for giving the initial direction.
  
  – Mohammed Raqeeb
  Mar 12 at 10:05
  

  
  
  
  

add a comment | 

2

When connecting to oracle you will normally connect to ONE database, and within that database you will have a number of schemas. These schemas would be seen as "databases" in some other rdbms systems. Each schema is owned by a DB-User that can be a system- or normal user. All tables/objects created in a schema is "owned" by the owner of the schema.

So a rough method to separate the system tables from user tables will be to consider tables in a schemas owned by a system user as system tables.

If in a database with version 12c and above you can do like this:

select t.table_name, t.owner, u.oracle_maintained
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

If version below 12c the oracle_maintained column does not exist so you need this work-around. The 35 should be set to the highest used_id for system users. System users are normally the lowest in the database.

select t.table_name, t.owner, case when u.user_id > 35 then 'N' else 'Y' end systemgenerated
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

Also: the 'and rownum < 100' should be replaced by your own filter on tables.

share|improve this answer

answered Mar 8 at 9:12

F.MadsenF.Madsen

44225

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thank you! My customer however is specifically interested in adding a filter on the databases / schemas rather than the tables. Is there a query (as a join of such tables) that I can run to list only the non-system databases/schemas ? thanks again.
  
  – Mohammed Raqeeb
  Mar 11 at 10:51
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Also as I understand, the Oracle doc says ORACLE_MAINTAINED column indicates whether the user/schema is System or non-system. If so, can we not filter by this column directly to check for non-system databases ? References: dba-oracle.com/t_oracle_maintained_dba_users.htm docs.oracle.com/database/121/REFRN/…
  
  – Mohammed Raqeeb
  Mar 11 at 11:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Then it is easy. Normally there will be one schema for one user. So just query "Select username, oracle_maintained form dba_user where <your filter>..."
  
  – F.Madsen
  Mar 11 at 16:33
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you. The precise query that I needed was slightly different however. I have quoted it below as an answer. Nevertheless, thanks for giving the initial direction.
  
  – Mohammed Raqeeb
  Mar 12 at 10:05
  

  
  
  
  

add a comment | 

When connecting to oracle you will normally connect to ONE database, and within that database you will have a number of schemas. These schemas would be seen as "databases" in some other rdbms systems. Each schema is owned by a DB-User that can be a system- or normal user. All tables/objects created in a schema is "owned" by the owner of the schema.

So a rough method to separate the system tables from user tables will be to consider tables in a schemas owned by a system user as system tables.

If in a database with version 12c and above you can do like this:

select t.table_name, t.owner, u.oracle_maintained
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

If version below 12c the oracle_maintained column does not exist so you need this work-around. The 35 should be set to the highest used_id for system users. System users are normally the lowest in the database.

select t.table_name, t.owner, case when u.user_id > 35 then 'N' else 'Y' end systemgenerated
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

Also: the 'and rownum < 100' should be replaced by your own filter on tables.

share|improve this answer

answered Mar 8 at 9:12

F.MadsenF.Madsen

44225

select t.table_name, t.owner, u.oracle_maintained
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

select t.table_name, t.owner, case when u.user_id > 35 then 'N' else 'Y' end systemgenerated
from dba_tables t, dba_users u 
where t.owner = u.username
 and rownum < 100;

share|improve this answer

answered Mar 8 at 9:12

F.MadsenF.Madsen

44225

answered Mar 8 at 9:12

F.MadsenF.Madsen

44225

answered Mar 8 at 9:12

F.MadsenF.Madsen

44225

0

I could come up with the exact answer to my question taking help from another post - Oracle SQL Query for listing all Schemas in a DB

Here is the query that I needed :

> SELECT * FROM dba_users u WHERE EXISTS (SELECT 1 FROM dba_objects o
> WHERE o.owner = u.username ) AND default_tablespace not in
> ('SYSTEM','SYSAUX') and ACCOUNT_STATUS = 'OPEN'

This will list all the Non-System users/schemas (aka databases) from your database. Make sure you have logged in as SYS user or you grant "GRANT SELECT ON dba_users TO the_non_sys_user"

share|improve this answer

edited Mar 12 at 11:04

answered Mar 12 at 10:00

Mohammed RaqeebMohammed Raqeeb

45111

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes that might work as long as you have already created at least one object for the non-system-user.
  
  – F.Madsen
  Mar 12 at 10:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, just what I needed :)
  
  – Mohammed Raqeeb
  Mar 12 at 11:04
  

  
  
  
  

add a comment | 

0

I could come up with the exact answer to my question taking help from another post - Oracle SQL Query for listing all Schemas in a DB

Here is the query that I needed :

> SELECT * FROM dba_users u WHERE EXISTS (SELECT 1 FROM dba_objects o
> WHERE o.owner = u.username ) AND default_tablespace not in
> ('SYSTEM','SYSAUX') and ACCOUNT_STATUS = 'OPEN'

This will list all the Non-System users/schemas (aka databases) from your database. Make sure you have logged in as SYS user or you grant "GRANT SELECT ON dba_users TO the_non_sys_user"

share|improve this answer

edited Mar 12 at 11:04

answered Mar 12 at 10:00

Mohammed RaqeebMohammed Raqeeb

45111

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes that might work as long as you have already created at least one object for the non-system-user.
  
  – F.Madsen
  Mar 12 at 10:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, just what I needed :)
  
  – Mohammed Raqeeb
  Mar 12 at 11:04
  

  
  
  
  

add a comment | 

I could come up with the exact answer to my question taking help from another post - Oracle SQL Query for listing all Schemas in a DB

Here is the query that I needed :

> SELECT * FROM dba_users u WHERE EXISTS (SELECT 1 FROM dba_objects o
> WHERE o.owner = u.username ) AND default_tablespace not in
> ('SYSTEM','SYSAUX') and ACCOUNT_STATUS = 'OPEN'

This will list all the Non-System users/schemas (aka databases) from your database. Make sure you have logged in as SYS user or you grant "GRANT SELECT ON dba_users TO the_non_sys_user"

share|improve this answer

edited Mar 12 at 11:04

answered Mar 12 at 10:00

Mohammed RaqeebMohammed Raqeeb

45111

> SELECT * FROM dba_users u WHERE EXISTS (SELECT 1 FROM dba_objects o
> WHERE o.owner = u.username ) AND default_tablespace not in
> ('SYSTEM','SYSAUX') and ACCOUNT_STATUS = 'OPEN'

share|improve this answer

edited Mar 12 at 11:04

answered Mar 12 at 10:00

Mohammed RaqeebMohammed Raqeeb

45111

answered Mar 12 at 10:00

Mohammed RaqeebMohammed Raqeeb

45111

answered Mar 12 at 10:00

Mohammed RaqeebMohammed Raqeeb

45111