Home » SQL & PL/SQL » SQL & PL/SQL » Query for user created databases in Oracle 9i and 10g
Query for user created databases in Oracle 9i and 10g [message #311360] Fri, 04 April 2008 04:45 Go to next message
shiva19apr
Messages: 6
Registered: April 2008
Junior Member
I want to get all the databases except system databases that oracle creates by defualt.

This I want to execute in a single select query wihtin a function by using any table/view.

I tried with all_users view but there is no way to check the databases whether it is system created one.

Is there any other table / view i can use it in a function?

Just want to exclude the system, sysaux, other system related users/tablespaces

Also tried using dba_users view, it cannot be used inside a function.

thanks for all your help.
Re: Query for user created databases in Oracle 9i and 10g [message #311367 is a reply to message #311360] Fri, 04 April 2008 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to get all the databases except system databases that oracle creates by defualt.

Do you mean schema?
It depends on your options.
Create a new database and get the list of the schemas, then you have the "default" ones.

Quote:
I tried with all_users view but there is no way to check the databases whether it is system created one.

No. There are only 2 mandatory schemas: SYS and SYSTEM.

Regards
Michel
Re: Query for user created databases in Oracle 9i and 10g [message #311369 is a reply to message #311367] Fri, 04 April 2008 05:00 Go to previous messageGo to next message
shiva19apr
Messages: 6
Registered: April 2008
Junior Member
No i mean Users
Re: Query for user created databases in Oracle 9i and 10g [message #311370 is a reply to message #311369] Fri, 04 April 2008 05:06 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think it is time for you read the oracle reference manual.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8003.htm#i2065278
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6014.htm#i2154127

Regards

Raj
Re: Query for user created databases in Oracle 9i and 10g [message #311373 is a reply to message #311370] Fri, 04 April 2008 05:11 Go to previous messageGo to next message
shiva19apr
Messages: 6
Registered: April 2008
Junior Member
Hi Raj / Michel,

Thanks for the replies. i just want to know what are all the users that oracle creates after installation?
Re: Query for user created databases in Oracle 9i and 10g [message #311374 is a reply to message #311360] Fri, 04 April 2008 05:12 Go to previous messageGo to next message
shiva19apr
Messages: 6
Registered: April 2008
Junior Member
I tried with all_users view but there is no way to check the tablespace type.. Sad
Re: Query for user created databases in Oracle 9i and 10g [message #311384 is a reply to message #311373] Fri, 04 April 2008 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i just want to know what are all the users that oracle creates after installation?

I already answered this but you don't want to read it.
Quote:
It depends on your options.
Create a new database and get the list of the schemas, then you have the "default" ones.

By the way, schema and user are the same thing in Oracle (most of the time). Read Database Concepts.

Regards
Michel

[Updated on: Fri, 04 April 2008 05:58]

Report message to a moderator

Re: Query for user created databases in Oracle 9i and 10g [message #311389 is a reply to message #311384] Fri, 04 April 2008 06:18 Go to previous messageGo to next message
shiva19apr
Messages: 6
Registered: April 2008
Junior Member
Hi Michel,

Thanks, sys and system are the users only after fresh installation?

i do see other users like operator, scott, tools, olapsys.

is there a way to exclude these users using a view?
Re: Query for user created databases in Oracle 9i and 10g [message #311394 is a reply to message #311389] Fri, 04 April 2008 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there a way to exclude these users using a view?

Create your own excluding the users you want to exclude.

As I said:
Quote:
There are only 2 mandatory schemas: SYS and SYSTEM.


Regards
Michel
Re: Query for user created databases in Oracle 9i and 10g [message #311398 is a reply to message #311389] Fri, 04 April 2008 06:46 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
shiva19apr
i do see other users like operator, scott, tools, olapsys.

(If I'm not wrong:)

Yes, you do, but that's because you probably let OUI (Oracle Universal Installer) do the whole job - install Oracle software and predefined Oracle database. It (OUI) created these users.

If you did that job yourself (i.e. created the database from scratch, by issuing CREATE DATABASE statement, you'd get only two users (which Michel mentioned): SYS and SYSTEM. There certainly wouldn't be user named 'scott' because - in order to have it - you'd have to create it manually (using the CREATE USER statement).
Previous Topic: Fetching currently logged in user name
Next Topic: Create a variable
Goto Forum:
  


Current Time: Sat Dec 03 11:55:29 CST 2016

Total time taken to generate the page: 0.10307 seconds