RE: wrong tablespace used

From: <sinardyxing_at_bcsis.com>
Date: Sun, 11 Mar 2001 17:17:06 -0800
Message-ID: <F001.002C92D3.20010311170018@fatcity.com>

Hi Gunawan,

unfortunetly I do shutdown normal, and when I try to

SVRMGRL> startup mount (startup only it give me error because can not found ts1.dbf)

ALTER DATABASE CREATE DATAFILE
        '/appl/OraHome/oradata/MYDB/ts1.dbf'    AS

        '/appl/OraHome/oradata/MYDB/ts1.dbf';

It gave me

Statement processed

and when I do (

RECOVER DATAFILE '/appl/OraHome/oradata/MYDB/ts1.dbf'; ORA-00279: change 199725 generated at 03/01/2001 15:24:10 needed for thread 1
ORA-00289: suggestion : /appl/OraHome/dbs/arch1_7472.dbf ORA-00280: change 199725 for thread 1 is in sequence #7472 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

• I just press enter for the RET
• It gave me

ORA-00308: cannot open archived log '/appl/OraHome/dbs/arch1_7472.dbf' ORA-27037: unale to obtain file status
SVR4 Error: 2: No such file or directory Additional informatoin: 3

Thank you for your help

-----Original Message-----
Sent: Friday, 9 March 2001 10:12 PM
To: sinardyxing_at_bcsis.com; oracle-l_at_fatcity.com; 'lzDBA'

First of all, to recover the ts1 tablespace, do something like:   ALTER DATABASE CREATE DATAFILE '/appl/OraHome/oradata/MYDB/ts1.dbf'

      AS '/appl/OraHome/oradata/MYDB/ts1.dbf';

  RECOVER DATAFILE '/appl/OraHome/oradata/MYDB/ts1.dbf';

Now, on the other question, are you sure there's not another table called EMP
owned by another USER like SCOTT? Query dba_segments view:   SELECT owner, segment_name, tablespace_name FROM dba_segments   WHERE segment_name = 'EMP';

My $0.02.

HTP.
Gunawan Yuwono
Oracle DBA
Kansas City, USA

• sinardyxing_at_bcsis.com wrote:
  > 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
  >
  >
  > --------
  > Think you know someone who can answer the above question? Forward it to
  them!
  > to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com
  > to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com
  > Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
  >
  

  ---

Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/

---

Think you know someone who can answer the above question? Forward it to them!
to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl

-- 
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 Sun Mar 11 2001 - 19:17:06 CST