473,837 Members | 1,516 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

tablespace SYSTEM used by others user, not sys. Help!

hi,
i need advise and guidance from experts.
Today, I discovered about 1464 tables created in SYSTEM tablespace and
the owner is dev_user.

As, i read from ORACLE 101 DBA's it said :'System tablespace is a
tablespace user to stored objects belong to user SYS only. if, we do
not ensure this, the SYSTEM tablespace will :
• Tend to get very fragmented
• Have potentially severe contention against it and affect performance

we cannot alter SYSTEM tablespace offline, there is no way to rebuild
this tablespace without rebuilding the whole database and this can be
very time consuming.'

Then, wat should i do? please advise.
regards,
tracykim
Jul 19 '05 #1
7 12683
x
You should move all tables and rebuild all indexes to different tablespace.

something like:

alter table my_table move tablespace users;

alter index my_index rebuild tablespace users;

Use dba_tables and dba_indexes to find out which tables and indexes are in
system tablespace. You could use it like this:
select 'alter table ' || table_name || ' move tablespace users;'
from dba_tables
where tablespace_name ='SYSTEM' and owner not in('SYS','SYSTE M')

Output of this query is a script which you should run to move all the
tables. Do the same for indexes.
Jul 19 '05 #2
"x" <x@x.hr> wrote in message news:<c9******* ***@ls219.htnet .hr>...
You should move all tables and rebuild all indexes to different tablespace.

something like:

alter table my_table move tablespace users;

alter index my_index rebuild tablespace users;

Use dba_tables and dba_indexes to find out which tables and indexes are in
system tablespace. You could use it like this:
select 'alter table ' || table_name || ' move tablespace users;'
from dba_tables
where tablespace_name ='SYSTEM' and owner not in('SYS','SYSTE M')

Output of this query is a script which you should run to move all the
tables. Do the same for indexes.


I think the first step should be to alter the user dev_user to have a
default tablespace other than SYSTEM. If dev_user is not a DBA I
would then alter the user's quota/privilege depending on exactly how
this ID is to be used to prevent it from using SYSTEM. This will stop
any new non-SYS objects from being created in the SYSTEM tablespace.

Moving or dropping the objects is the next step, and depending on
object usage may take a while.

HTH -- Mark D Powell --
Jul 19 '05 #3
hi, guys,
tanz for the help. Just to make sure i run the rite script, here is
the sql, how i found out non-sys user created a tables in SYSTEM
tablespace. In fact, this dev_user have been assigened a default
tablespace which is dev_user Tablespace. I wonder, how can the table
created by them is in system schema? Pls let me know, if u have the
answer.

1 select owner, TABLESPACE_NAMe , table_name
2 from dba_tables
3 where TABLESPACE_NAMe ='SYSTEM'
4 AND OWNER='DEV_USER '

SQL> /
OWNER TABLESPACE_NAME
TABLE_NAME
------------------------------ ------------------------------
--------------------------
DEV_USER SYSTEM
CES_DETAIL
DEV_USER SYSTEM CES_MAIN
DEV_USER SYSTEM
CHARGES_LOOKUP
DEV_USER SYSTEM
CHECK_AGING
DEV_USER SYSTEM
CHECK_CREDITOR







Ma*********@eds .com (Mark D Powell) wrote in message news:<26******* *************** ****@posting.go ogle.com>...
"x" <x@x.hr> wrote in message news:<c9******* ***@ls219.htnet .hr>...
You should move all tables and rebuild all indexes to different tablespace.

something like:

alter table my_table move tablespace users;

alter index my_index rebuild tablespace users;

Use dba_tables and dba_indexes to find out which tables and indexes are in
system tablespace. You could use it like this:
select 'alter table ' || table_name || ' move tablespace users;'
from dba_tables
where tablespace_name ='SYSTEM' and owner not in('SYS','SYSTE M')

Output of this query is a script which you should run to move all the
tables. Do the same for indexes.


I think the first step should be to alter the user dev_user to have a
default tablespace other than SYSTEM. If dev_user is not a DBA I
would then alter the user's quota/privilege depending on exactly how
this ID is to be used to prevent it from using SYSTEM. This will stop
any new non-SYS objects from being created in the SYSTEM tablespace.

Moving or dropping the objects is the next step, and depending on
object usage may take a while.

HTH -- Mark D Powell --

Jul 19 '05 #4
x
I wonder, how can the table
created by them is in system schema? Pls let me know, if u have the
answer.


create table my_table (x varchar2(1)) tablespace system

Easy as that. If your dev_user has right priviledges he can create table
where ever he wants.

Jul 19 '05 #5
hi, x
dev_user only have priviledge to create table under his own tablespace
which is dev_user only. he only granted the priviledge 'create table'
not 'create any table'
"x" <x@x.hr> wrote in message news:<c9******* ***@ls219.htnet .hr>...
I wonder, how can the table
created by them is in system schema? Pls let me know, if u have the
answer.


create table my_table (x varchar2(1)) tablespace system

Easy as that. If your dev_user has right priviledges he can create table
where ever he wants.

Jul 19 '05 #6
tracy wrote:
hi, x
dev_user only have priviledge to create table under his own tablespace
which is dev_user only. he only granted the priviledge 'create table'
not 'create any table'
"x" <x@x.hr> wrote in message news:<c9******* ***@ls219.htnet .hr>...
I wonder, how can the table
created by them is in system schema? Pls let me know, if u have the
answer.


create table my_table (x varchar2(1)) tablespace system

Easy as that. If your dev_user has right priviledges he can create table
where ever he wants.


Unless granted the resource role, which offers quota unlimited on
any tablespace. Unless you specifically do
alter user quota 0 on system;
your dev_user can still create objects in the system tablespace
--

Regards,
Frank van Bortel

Jul 19 '05 #7
Frank van Bortel <fv********@net scape.net> wrote in message news:<c9******* ***@news1.tilbu 1.nb.home.nl>.. .
tracy wrote:
hi, x
dev_user only have priviledge to create table under his own tablespace
which is dev_user only. he only granted the priviledge 'create table'
not 'create any table'
"x" <x@x.hr> wrote in message news:<c9******* ***@ls219.htnet .hr>...
I wonder, how can the table
created by them is in system schema? Pls let me know, if u have the
answer.
create table my_table (x varchar2(1)) tablespace system

Easy as that. If your dev_user has right priviledges he can create table
where ever he wants.


Unless granted the resource role, which offers quota unlimited on
any tablespace. Unless you specifically do
alter user quota 0 on system;
your dev_user can still create objects in the system tablespace

Frank provided the information on how to lock the user out if it has
the resource role. Privileges should be aligned to what the ID has to
be able to do and no more.

Depending on the age of the objects it is also possible that they were
created before the default tablespace of the user was changed and were
never relocated.

Once you review and modify the privileges as necessary you still need
to clean up the system tablespace.

-- Mark D Powell --
Jul 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
6305
by: gecho | last post by:
New to oracle, but I'm coming from the sybase side. I took over one of our oracle production databases (1.5 Tb) while the regular dba is out. I got errors (unable to extend temp segment) for two user tablespaces which I took care of by adding a datafile to each. While reviewing the other tablespaces, I was surprised to see the system tablespace at 99%. I checked with app folks. No new objects were added accidentally to it. I panicked...
1
5023
by: xixi | last post by:
There is one thing I don't understand, I have default tablespace userspace1 is database managed type, with total freepages 506048. another one called tmpxx is DMS type too, with 525120 free pages. When I create a new table (did not specify on which tablespace), why the table not created on the userspace1 but instead on tmpxx? what is the rule to create a table on tablespace without specify in which tablespace if I have both tablespace...
2
554
by: Bing Wu | last post by:
I have a user defined tablespace: Tablespace ID = 4 Name = DATASPACE Type = Database managed space Contents = Any data State = 0x0000 Detailed explanation: Normal Total pages = 15360000
4
13053
by: Ulrich Sprick | last post by:
Hi all, (DB2 V7.1 for WinNT) I am looking for a way to determine the free space in my tablespace (containers), but I can't find out. The tablespace in question is a system managed tablespace in a raw partition. The Control Center always reports 100% usage (although I can insert data...). The "list tablespace containers for n show detail" command shows the number of usable pages, but they are almost equal to the total number of pages. ...
13
3347
by: Jean-Marc Blaise | last post by:
Hi there, I would like to know the meaning of direct_writes in a tablespace snapshot on a tablespace that is: * SMS (db2empfa ON) * Only 1 table is accessed in INSERT - Z locked (Not logged Initially mode) * No BLOB, long varchar ...in the table * No utility running
2
28614
by: Kums | last post by:
What is the maximum permissible size of a database? Is there any limitation. What is the maximum # of tablespace's allowed in a database? Thanks for your response.
4
4655
by: Joachim Klassen | last post by:
DB2 V8.2 FP10 on Windows I tested the following HADR scenario: - a new tablespace on a new filesytem is created on the primary System - the replay on standby fails because of lacking permissions - the tablespace is backed up on the primary system - tables are created in the new tablespace and data is inserted (and a couple of logs are archived) - Takeover is done by the standby
5
5280
by: denero_watz | last post by:
I got an error like this below. I already have a tablespace using 32K pagesize. The message below also says: "If such a table space already exists, eliminate one or more columns from the system temporary table. " But how do I do this?
7
189
by: tracy | last post by:
hi, i need advise and guidance from experts. Today, I discovered about 1464 tables created in SYSTEM tablespace and the owner is dev_user. As, i read from ORACLE 101 DBA's it said :'System tablespace is a tablespace user to stored objects belong to user SYS only. if, we do not ensure this, the SYSTEM tablespace will : • Tend to get very fragmented • Have potentially severe contention against it and affect performance
0
10881
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10575
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10275
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9406
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7004
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5850
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4475
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4043
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3126
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.