| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Redo a Tablespace
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C04E3B.0C29FB5E
Content-Type: text/plain;
charset="iso-8859-1"
Thank you for responding to my question. Yes to #4 and #5. An example of this is creating a user REFERENCE to own all the reference (static) tables and have the tables located in one tablespace called REFERENCE_DATA (one for indexes as well). I read in a book awhile back about how recovery might be easier if a user is contained in one tablespace because there is not a export option for tablespace.
When I created the tablespaces that I have I didn't put a whole lot of thought into the size, extents, etc. It's been about a year now since I started this, and now I am finding articles which address issues on tuning and fragmentation. Needless to say I flunked about 7 of the 12 rules of SAFE. Now I want to correct earlier mistakes. It would probably be easier to just call the tablespace something else and cut out some of the steps I listed, but I like the naming convention I'm using and want to use the same names.
I really appreciate this list and the people like you who respond. Although sometimes we get a little 'testy', it is still a great window of the 'real' world that classes just never seem to get too.
Sorry for the book....Laura
P.S. Thanks for the 'offline' suggestion...that would be easier.
-----Original Message-----
From: Rachel Carmichael [mailto:carmichr_at_hotmail.com]
Sent: Monday, November 13, 2000 5:11 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Redo a Tablespace
Well, you are doing a bit more than I would ( I wouldn't do #1 or 2) but it's pretty good, with a few comments:
#4 - are you SURE that that user only has tables in that tablespace? #5 - are you SURE that no one else has tables (or indexes!) in that tablespace?
I would not drop the user, but if there are no other objects in that tablespace, I'd merely
alter tablespace <tablespace name> offline;
and then
drop tablespace <tablespace name> including contents;
you WILL have to delete the datafile, Oracle doesn't do that for you.
again, I wouldn't drop the user so I wouldn't need to create the user and I wouldn't take the database access away so I wouldn't need to let everyone back in.
If you are able to take the database away from people for the length of time
that it will take to do all that you want, go for it, it's safer to do the backup and restrict access. But you don't have to
Rachel
>From: "Burton, Laura L." <BurtonL_at_prismplus.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Redo a Tablespace
>Date: Mon, 13 Nov 2000 14:36:27 -0800
>
>I want to delete a tablespace that is already in use (contains tables) and
>rebuild it. Would someone please look at the steps I am going to take and
>offer advice if you see a problem?
>
> 1. Shutdown database and back it up (datafiles, redo, archive, etc)
> 2. Restart database ... maybe exclusive to keep others off
> 3. Export user (which only has tables on the tablespace I want to
>delete)
> 4. Drop user cascade
> 5. Drop tablespace
> 6. Delete datafile file from disk (if the drop tablespace takes care of
>this then I'll go to step 7)
> 7. Create tablespace with new info
> 8. Create user
> 9. Import user
>10. Alter database for everyone's use
>
>Thank you in advance.
>
>Laura
Share information about yourself, create your own public profile at http://profiles.msn.com.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: carmichr_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C04E3B.0C29FB5E Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12"> <TITLE>RE: Redo a Tablespace</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>Thank you for responding to my question. Yes to = #4 and #5. An example of this is creating a user REFERENCE to own = all the reference (static) tables and have the tables located in one = tablespace called REFERENCE_DATA (one for indexes as well). I = read in a book awhile back about how recovery might be easier if a user = is contained in one tablespace because there is not a export option for = tablespace. </FONT></P> <P><FONT SIZE=3D2>When I created the tablespaces that I have I didn't = put a whole lot of thought into the size, extents, etc. It's been = about a year now since I started this, and now I am finding articles = which address issues on tuning and fragmentation. Needless to say = I flunked about 7 of the 12 rules of SAFE. Now I want to correct = earlier mistakes. It would probably be easier to just call the = tablespace something else and cut out some of the steps I listed, but I = like the naming convention I'm using and want to use the same = names.</FONT></P> <P><FONT SIZE=3D2>I really appreciate this list and the people like you = who respond. Although sometimes we get a little 'testy', it is = still a great window of the 'real' world that classes just never seem = to get too.</FONT></P> <P><FONT SIZE=3D2>Sorry for the book....Laura = </FONT> </P> <P><FONT SIZE=3D2>P.S. Thanks for the 'offline' suggestion...that = would be easier.</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Rachel Carmichael [<A = HREF=3D"mailto:carmichr_at_hotmail.com">mailto:carmichr_at_hotmail.com</A>]</F= ONT> <BR><FONT SIZE=3D2>Sent: Monday, November 13, 2000 5:11 PM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: Re: Redo a Tablespace</FONT> </P> <BR> <P><FONT SIZE=3D2>Well, you are doing a bit more than I would ( I = wouldn't do #1 or 2) but </FONT> <BR><FONT SIZE=3D2>it's pretty good, with a few comments:</FONT> </P> <P><FONT SIZE=3D2>#4 - are you SURE that that user only has = tables in that tablespace?</FONT> <BR><FONT SIZE=3D2>#5 - are you SURE that no one else has tables = (or indexes!) in that </FONT> <BR><FONT SIZE=3D2>tablespace?</FONT> </P> <P><FONT SIZE=3D2>I would not drop the user, but if there are no other = objects in that </FONT> <BR><FONT SIZE=3D2>tablespace, I'd merely</FONT> </P> <P><FONT SIZE=3D2>alter tablespace <tablespace name> = offline;</FONT> </P> <P><FONT SIZE=3D2>and then</FONT> </P> <P><FONT SIZE=3D2>drop tablespace <tablespace name> including = contents;</FONT> </P> <P><FONT SIZE=3D2>you WILL have to delete the datafile, Oracle doesn't = do that for you.</FONT> </P> <BR> <P><FONT SIZE=3D2>again, I wouldn't drop the user so I wouldn't need to = create the user and I </FONT> <BR><FONT SIZE=3D2>wouldn't take the database access away so I wouldn't = need to let everyone </FONT> <BR><FONT SIZE=3D2>back in.</FONT> </P> <P><FONT SIZE=3D2>If you are able to take the database away from people = for the length of time </FONT> <BR><FONT SIZE=3D2>that it will take to do all that you want, go for = it, it's safer to do the </FONT> <BR><FONT SIZE=3D2>backup and restrict access. But you don't have = to</FONT> </P> <P><FONT SIZE=3D2>Rachel</FONT> <BR><FONT SIZE=3D2>>From: "Burton, Laura L." = <BurtonL_at_prismplus.com></FONT> <BR><FONT SIZE=3D2>>Reply-To: ORACLE-L_at_fatcity.com</FONT> <BR><FONT SIZE=3D2>>To: Multiple recipients of list ORACLE-L = <ORACLE-L_at_fatcity.com></FONT> <BR><FONT SIZE=3D2>>Subject: Redo a Tablespace</FONT> <BR><FONT SIZE=3D2>>Date: Mon, 13 Nov 2000 14:36:27 -0800</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>I want to delete a tablespace that is already in = use (contains tables) and</FONT> <BR><FONT SIZE=3D2>>rebuild it. Would someone please look at = the steps I am going to take and</FONT> <BR><FONT SIZE=3D2>>offer advice if you see a problem?</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>> 1. Shutdown database and back it up = (datafiles, redo, archive, etc)</FONT> <BR><FONT SIZE=3D2>> 2. Restart database ... maybe = exclusive to keep others off</FONT> <BR><FONT SIZE=3D2>> 3. Export user (which only has = tables on the tablespace I want to </FONT> <BR><FONT SIZE=3D2>>delete)</FONT> <BR><FONT SIZE=3D2>> 4. Drop user cascade</FONT> <BR><FONT SIZE=3D2>> 5. Drop tablespace</FONT> <BR><FONT SIZE=3D2>> 6. Delete datafile file from disk = (if the drop tablespace takes care of</FONT> <BR><FONT SIZE=3D2>>this then I'll go to step 7)</FONT> <BR><FONT SIZE=3D2>> 7. Create tablespace with new = info</FONT> <BR><FONT SIZE=3D2>> 8. Create user</FONT> <BR><FONT SIZE=3D2>> 9. Import user</FONT> <BR><FONT SIZE=3D2>>10. Alter database for everyone's = use</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>Thank you in advance.</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>Laura</FONT> </P> <P><FONT = SIZE=3D2>_______________________________________________________________= __________</FONT> <BR><FONT SIZE=3D2>Get Your Private, Free E-mail from MSN Hotmail at <A = HREF=3D"http://www.hotmail.com" = TARGET=3D"_blank">http://www.hotmail.com</A>.</FONT> </P> <P><FONT SIZE=3D2>Share information about yourself, create your own = public profile at </FONT> <BR><FONT SIZE=3D2><A HREF=3D"http://profiles.msn.com" = TARGET=3D"_blank">http://profiles.msn.com</A>.</FONT> </P> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Rachel Carmichael</FONT> <BR><FONT SIZE=3D2> INET: carmichr_at_hotmail.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =Received on Tue Nov 14 2000 - 07:01:47 CST
 |
 |