Precise and clear to the point!
Jenny, January 14, 2003 - 12:30 pm UTC
Thank you Tom!
Drop RBS Tablespace
Nadeesh, December 04, 2004 - 6:14 am UTC
Tom,
On oracle 8.1.7 rbs is dictionary managed, but I want to use LMT . In link below you described that RBS is vital...........
And if this can be done can I do this while the database is open.
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:886028302502, <code>
RBS is a vital integral, needed and necessary component of the database. You cannot simply "slice it out". We need it. It is part of our "inner being".
December 04, 2004 - 11:02 am UTC
rbs can be lmt in 8i, just create it.
create a new tablespace.
create public online rollback segments in it.
alter the old ones "offline"
when they become "idle" (wait a day, to prevent 1555's) drop em.
dmt to lmt
Nadeesh Condon, December 10, 2004 - 1:14 am UTC
Thank Tom, it worked
I am quite new and started reading oracle 9i onwards. Because in our company we are using 8.1.7, I have already converted RBS dmt to lmt as said above. My only concern is to start using INDX and TEMP which are dictionary managed tablespaces to LMT.
I am browsing archives from last few days and trying to find something about this but didn't get anything. In sort I want to convert INDX and TEMP which are dictionary managed to LMT. Please brief like you did for rbs.
Regards,
December 10, 2004 - 1:22 am UTC
create a new temp tablespace.
alter people to have it.
drop old after a while (when it is done being used)
create a new tablespace
alter index Iname rebuild online tablespace new_one; (test this, do it when you are not so busy)
drop old tablespace
Dictionary managed to locally managed
Nadeesh Condon, December 14, 2004 - 4:50 am UTC
Tom,
While anserwing request you to consider that I started reading oracle 9i onwards and in our company oracle 8.1.6 is running (critical production server) with everything default.
I want to use lmt as default tablespace and default temporary tablespace for users.
[1] Users are using sytem tablespace as there default tablespace.
For this to work
[1.1] I have already created lmt tablespace (e.g lmt1) with uniform extent.
Question: What to do so that user "abcd" start using lmt1 as their default tablespace instead of system.
[2] Have already altered one of the user to start using lmt temporary tablespace (e.g lmtemp) but when I run v$sort_usage it shows :
SQL> select user, tablespace from v$sort_usage;
USER TABLESPACE
------------------------------ -------------------------------
SYSTEM LMTEMP
My query is that I am running this query as user "abcd" then why it is showing "SYSTEM" in the output.
Regards,
December 14, 2004 - 9:01 am UTC
1) alter user, see the alter user command documented in the sql reference.
2) what does select USER from dual return? :) think about that. then, use the username column in v$sort_usage.
dmt to lmt
Nadeesh, December 14, 2004 - 11:00 am UTC
[1] May be, I did not ask the question precisely, I know alter user will change default tablespace. My concern is to move the existing objects of the user which are in system tablespace to newly created lmt with their existing permissions, roles, prilvilegs, constraints and everything.
[2] When I connect abcd/abcd:- It shows user "abcd"
And running intensive query with order by in it shows the results in system user account for v$sort_usage as described earlier.
Regards,
December 14, 2004 - 11:08 am UTC
1) you asked a very very VERY precise question:
<quote>
Question: What to do so that user "abcd" start using lmt1 as their default
tablespace instead of system.
</quote>
That was definitely 100% precise -- and the answer to that precise question is precisely "alter user". (not that any of this has anything to do with "Drop RBS Tablespace" actually).
search this site for
moving tables
in order to see how to move tables from tablespace to tablespace.
2) did you read the entire answer? username is what you were looking for, think about what USER means to Oracle (it is a function, returns your name)
Thanks for quick response
Nadeesh, December 14, 2004 - 11:43 am UTC
December 15, 2004 - 12:33 pm UTC
you didn't mention the version, I naturally assumed "software written this century"
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ops$tkyte@ORA9IR2> desc v$sort_usage
Name Null? Type
---------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
USER VARCHAR2(30)
SESSION_ADDR RAW(4)
SESSION_NUM NUMBER
SQLADDR RAW(4)
.....
username is already in there in current and old software.
that is why the confusion.....