Home » RDBMS Server » Server Administration » User defined tables in System tablespaces (oracle 11g)
User defined tables in System tablespaces [message #636727] Thu, 30 April 2015 19:49 Go to next message
krish96
Messages: 70
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..
Re: User defined tables in System tablespaces [message #636728 is a reply to message #636727] Thu, 30 April 2015 19:53 Go to previous messageGo to next message
BlackSwan
Messages: 25796
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 Go to previous messageGo to next message
krish96
Messages: 70
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..

Re: User defined tables in System tablespaces [message #636730 is a reply to message #636729] Thu, 30 April 2015 21:05 Go to previous messageGo to next message
BlackSwan
Messages: 25796
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
Re: User defined tables in System tablespaces [message #636749 is a reply to message #636728] Fri, 01 May 2015 07:30 Go to previous messageGo to next message
EdStevens
Messages: 888
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
Re: User defined tables in System tablespaces [message #636752 is a reply to message #636749] Fri, 01 May 2015 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Without speaking about SYS schema as we often see here.

Re: User defined tables in System tablespaces [message #636754 is a reply to message #636749] Fri, 01 May 2015 10:24 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, 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)
Re: User defined tables in System tablespaces [message #636856 is a reply to message #636754] Mon, 04 May 2015 07:39 Go to previous messageGo to next message
krish96
Messages: 70
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..

Re: User defined tables in System tablespaces [message #636858 is a reply to message #636856] Mon, 04 May 2015 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: User defined tables in System tablespaces [message #636860 is a reply to message #636727] Mon, 04 May 2015 07:46 Go to previous messageGo to next message
BlackSwan
Messages: 25796
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
Re: User defined tables in System tablespaces [message #636897 is a reply to message #636860] Tue, 05 May 2015 04:16 Go to previous messageGo to next message
Roachcoach
Messages: 1508
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?
Re: User defined tables in System tablespaces [message #636899 is a reply to message #636897] Tue, 05 May 2015 04:33 Go to previous message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, 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
Previous Topic: ORA-16032
Next Topic: Database upgradation
Goto Forum:
  


Current Time: Fri Jan 19 18:40:48 CST 2018

Total time taken to generate the page: 0.08727 seconds