OraFAQ Forum: SQL & PL/SQL » Query for user created databases in Oracle 9i and 10g

Home » SQL & PL/SQL » SQL & PL/SQL » Query for user created databases in Oracle 9i and 10g

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query for user created databases in Oracle 9i and 10g [message #311360]

Fri, 04 April 2008 04:45

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.

Report message to a moderator

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

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

shiva19apr
Messages: 6
Registered: April 2008

Junior Member

No i mean Users

Report message to a moderator

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

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

Report message to a moderator

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

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?

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

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?

Report message to a moderator

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

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Littlefoot
Messages: 21825
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).

Report message to a moderator

Previous Topic:	Fetching currently logged in user name
Next Topic:	Create a variable

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 18 22:03:46 CDT 2025