OraFAQ Forum: Server Administration » User defined tables in System tablespaces

Home » RDBMS Server » Server Administration » User defined tables in System tablespaces (oracle 11g)

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

User defined tables in System tablespaces [message #636727]

Thu, 30 April 2015 19:49

krish96
Messages: 74
Registered: October 2012

Member

Hello Experts,

I am trying to find in 11g database there are some user defined tables in system tablespaces, please help me how to find these objects with the SQL query.. Need to recreate these tables into a new tablespaces..

Thanks in advance..

Report message to a moderator

Re: User defined tables in System tablespaces [message #636728 is a reply to message #636727]

Thu, 30 April 2015 19:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

 
 1  select owner, count(*) from dba_extents where tablespace_name = 'SYSTEM'
  2* AND OWNER NOT IN ('SYS','SYSTEM') GROUP BY OWNER ORDER BY 1
SQL> /

OWNER                            COUNT(*)
------------------------------ ----------
OUTLN                                   9

[Updated on: Thu, 30 April 2015 19:53]

Report message to a moderator

Re: User defined tables in System tablespaces [message #636729 is a reply to message #636728]

Thu, 30 April 2015 21:02

krish96
Messages: 74
Registered: October 2012

Member

Thanks for your quick response, the output gives same count from the query for me also..

Is there chance to get the output with list of objects and owner of those objects..

Report message to a moderator

Re: User defined tables in System tablespaces [message #636730 is a reply to message #636729]

Thu, 30 April 2015 21:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there chance to get the output with list of objects and owner of those objects..

yes, modify the SQL to display what you desire

Report message to a moderator

Re: User defined tables in System tablespaces [message #636749 is a reply to message #636728]

Fri, 01 May 2015 07:30

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

BlackSwan -

That's a good start, but what about "user" objects that were "accidentally" created in the SYSTEM schema?
I'm a afraid that is a scenario that occurs far too often

Report message to a moderator

Re: User defined tables in System tablespaces [message #636752 is a reply to message #636749]

Fri, 01 May 2015 09:49

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

Senior Member
Account Moderator

Without speaking about SYS schema as we often see here.

Report message to a moderator

Re: User defined tables in System tablespaces [message #636754 is a reply to message #636749]

Fri, 01 May 2015 10:24

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

A basic start could be:

SELECT object_name, object_type
FROM dba_objects o, v$database d
WHERE owner='SYSTEM' and trunc(o.created) > trunc(d.created)

Report message to a moderator

Re: User defined tables in System tablespaces [message #636856 is a reply to message #636754]

Mon, 04 May 2015 07:39

krish96
Messages: 74
Registered: October 2012

Member

thank you gazzag... i got the same out put from that query

SQL> select owner, count(*) from dba_extents where tablespace_name = 'SYSTEM' AND OWNER NOT IN ('SYS','SYSTEM') GROUP BY OWNER ORDER BY 1

OWNER COUNT(*)
------------------------------ ----------
OUTLN 9

now i am trying to find out what are these objects that belong to OUTLN, and OUTLN is another default schema and it could be not userdefined objects right..?

thank you in advance..

Report message to a moderator

Re: User defined tables in System tablespaces [message #636858 is a reply to message #636856]

Mon, 04 May 2015 07:42

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

Senior Member
Account Moderator

OUTLN is a predefined schema like SYSTEM.
These objects are OL$% tables and indexes.

Report message to a moderator

Re: User defined tables in System tablespaces [message #636860 is a reply to message #636727]

Mon, 04 May 2015 07:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

krish96 wrote on Thu, 30 April 2015 17:49

Hello Experts,

I am trying to find in 11g database there are some user defined tables in system tablespaces, please help me how to find these objects with the SQL query.. Need to recreate these tables into a new tablespaces..

Thanks in advance..

"problem" only exists between your ears.

post SQL & results that show I am wrong

Report message to a moderator

Re: User defined tables in System tablespaces [message #636897 is a reply to message #636860]

Tue, 05 May 2015 04:16

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

BlackSwan wrote on Mon, 04 May 2015 13:46

krish96 wrote on Thu, 30 April 2015 17:49
Hello Experts,

I am trying to find in 11g database there are some user defined tables in system tablespaces, please help me how to find these objects with the SQL query.. Need to recreate these tables into a new tablespaces..

Thanks in advance..

"problem" only exists between your ears.

post SQL & results that show I am wrong

Oracle docs specifically advise against this.

http://docs.oracle.com/cd/E11882_01/network.112/e36292/users.htm#DBSEG10200

Quote:

he default setting for the default tablespaces of all users is the SYSTEM tablespace. If a user does not create objects, and has no privileges to do so, then this default setting is fine. However, if a user is likely to create any type of object, then you should specifically assign the user a default tablespace, such as the USERS tablespace. Using a tablespace other than SYSTEM reduces contention between data dictionary objects and user objects for the same data files. In general, do not store user data in the SYSTEM tablespace.

You can use the CREATE TABLESPACE SQL statement to create a permanent default tablespace other than SYSTEM at the time of database creation, to be used as the database default for permanent objects. By separating the user data from the system data, you reduce the likelihood of problems with the SYSTEM tablespace, which can in some circumstances cause the entire database to become nonfunctional.

Sure, it is a "guideline", but why would one roll the dice needlessly?

Report message to a moderator

Re: User defined tables in System tablespaces [message #636899 is a reply to message #636897]

Tue, 05 May 2015 04:33

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Quote:

The default setting for the default tablespaces of all users is the SYSTEM tablespace.

That's hardly helpful is it, Oracle? Smile

Report message to a moderator

Previous Topic:	ORA-16032
Next Topic:	Database upgradation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 01 21:09:23 CST 2025