So where is the functionality of being able to take a datafile offline while
keeping others owned by the tablespace online? Users will still not be able
to access some data?
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:VRUwa.35092$1s1.509383_at_newsfeeds.bigpond.com...
>
> "Ryan" <rgaffuri_at_cox.net> wrote in message news:XqUwa.13356
>
> > How do you manage which objects go in which datafile? So if you take a
> > datafile offline how do you know which objects you took offline?
> >
>
> Usually, you wouldn't particularly know which objects were where (nor
> particularly care to know)... so that which things are going to suffer
> becomes a matter of pot-luck. (Incidentally, it's not the entire object
that
> gets taken offline, just the bit of the object that resides in the
affected
> data file).
>
> However, if you are about to take file 4 offline, for example, it's easy
> enough to do select * from dba_extents, which tells you which segments
have
> extents on the soon-to-be-doomed data file, and thus the ones to which
> access will be compromised.
>
> If you do this sort of thing frequently, then you might very well want to
> ensure you know which bits of objects are where, which you can do by
> manaually allocating your extents: alter table blah allocate extent
> (datafile '/bing/bong/thisone.dbf').
>
> As a bit of a (long) worked example:
>
> [oracle_at_HAYDN oracle]$ sqlplus /nolog
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Thu May 15 14:57:07 2003
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> SQL> connect / as sysdba
>
> SQL> create tablespace smallish
> 2 datafile '/u01/app/oracle/oradata/lnx9/small1.dbf' size 1064k
> 3 extent management local uniform size 1m;
> Tablespace created.
>
> SQL> create table blah
> 2 tablespace smallish
> 3 as select * from scott.emp;
>
> Table created.
>
> SQL> insert into blah select * from blah;
>
> 14 rows created.
>
> SQL> /
>
> 28 rows created.
>
> SQL> /
>
> [snip a few repeats!!]
>
> SQL> /
>
> 7168 rows created.
>
> SQL> /
>
> insert into blah select * from blah
> *
> ERROR at line 1:
> ORA-01653: unable to extend table SYS.BLAH by 256 in tablespace SMALLISH
>
> [so now we know the first datafile of this tablespace is completely filled
> by the initial extent of this segment]
>
> SQL> alter tablespace smallish
> 2 add datafile '/u01/app/oracle/oradata/lnx9/small2.dbf' size 1064k;
>
> Tablespace altered.
>
> SQL> select count(*) from blah;
>
> COUNT(*)
> ----------
> 14336
>
> SQL> insert into blah select * from blah;
>
> 14336 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select count(*) from blah;
>
> COUNT(*)
> ----------
> 28672
>
> [so now we know that the extra records must have been inserted into the
new
> extent created on the second datafile of this tablespace]
>
> SQL> select segment_name, tablespace_name, file_id from dba_extents
> 2 where segment_name='BLAH';
>
> SEGMENT_NAME TABLESPACE_NAME FILE_ID
> ---------------------------- ------------------------------ ----------
> BLAH SMALLISH 9
> BLAH SMALLISH 10
>
> SQL> alter database datafile 10 offline;
>
> Database altered.
>
> SQL> select count(*) from blah;
> select count(*) from blah
> *
> ERROR at line 1:
> ORA-00376: file 10 cannot be read at this time
> ORA-01110: data file 10: '/u01/app/oracle/oradata/lnx9/small2.dbf'
>
> [Naturally enough, because a select count(*) requires a full table scan,
> which we can't do because half the table is unavailable]
>
> SQL> select * from blah where rownum <10;
>
> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
> ---------- ---------- --------- ---------- --------- ---------- ----------
-
> ---------
> 7369 SMITH CLERK 7902 17-DEC-80 800 20
> 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
> 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
> 7566 JONES MANAGER 7839 02-APR-81 2975 20
> 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
> 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
> 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
> 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
> 7839 KING PRESIDENT 17-NOV-81 5000 10
> 9 rows selected.
>
> [But if all I want is the first 9 rows, then there's no trouble, because
> they are housed on file 9, and that's still very much online. Obviously
with
> better data, the select statement could have been far more sophisticated,
> and nevertheless still worked.]
>
> Regards
> HJR
>
>
Received on Thu May 15 2003 - 20:32:47 CDT
