| 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: wrong tablespace used
who were you logged in as when you created the table?
you will need to remove that tablespace and then re-add it (this was detailed in a note earlier this week) to get that datafile back.
export data in the tablespace
alter tablespace offline
drop tablespace including contents
recreate the tablespace (with the datafile you deleted)
import the data
>From: sinardyxing_at_bcsis.com
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: wrong tablespace used
>Date: Fri, 09 Mar 2001 00:50:45 -0800
>
>Hi guys,
>
>I have some understanding problem.
>
>1. I create a new tablespace
>
> create tablespace ts1
> datafile '/appl/OraHome/oradata/MYDB/ts1.dbf'
> size 15M
> autoextend on
> default storage
>
> initial 128K
> next 128K
> minextents 1
> maxextents 8192
> pctincrease 0
> );
>
>2. I create a new user
> CREATE USER teddy
> IDENTIFIED BY bear
> DEFAULT TABLESPACE ts1
> TEMPORARY TABLESPACE temp1 (created too)
> QUOTA UNLIMITED ON ts1
> ;
>
>3. teddy create a table:
> CREATE TABLE emp (
> empno NUMBER(3) CONSTRAINT emp_pk PRIMARY KEY,
> fname VARCHAR2(20) CONSTRAINT emp_fname_nn NOT NULL,
> lname VARCHAR2(20) CONSTRAINT emp_lname_nn NOT NULL,
> bdate DATE CONSTRAINT emp_dbate_nn NOT NULL,
> CONSTRAINT check_empno CHECK (empno BETWEEN 0 AND 999)
> );
>
>why this table is not store inside ts1 ? as teddy was created with the
>default tablespace = ts1
>do I have to specify tablespace name in the create table emp script ?
>this table is store inside SYSTEM tablespace
>
>3. Now I made a stupid move "rm .../MYDB/ts1.dbf
> my solaris 2.7 login profile don't have the trash can (undelete) so I
>can
>not undelete
> I try to recreate my tbs1 by using above script (step 1) but oracle
>said
>tbs1 is allready exists.
>
>what should I do now. can I recover my tbs1.dbf and
>move teddy object into tablespace tbs1 from system tablespace ?
>
>
>
>Thank You guys,....
>
>
>sinardy
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author:
> INET: sinardyxing_at_bcsis.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).
-- 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).Received on Fri Mar 09 2001 - 05:34:15 CST
 |
 |