wrong tablespace used

From: <sinardyxing_at_bcsis.com>
Date: Fri, 09 Mar 2001 01:35:10 -0800
Message-ID: <F001.002C7F6F.20010309005045@fatcity.com>

Hi guys,

I have some understanding problem.

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). Received on Fri Mar 09 2001 - 03:35:10 CST