Re: Locally Managed Tablespaces ... again!!!

From: Marc Blum <marc_at_marcblum.de>
Date: Sun, 12 Jan 2003 21:56:31 +0100
Message-ID: <e2l32vsau6q3k64k919m23s1n2fh815vt7@4ax.com>

On Sat, 11 Jan 2003 09:06:37 +1100, "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote:

>If you want to perform maintenance on a data file, you offline the
>tablespace. If you offline a tablespace containing both indexes and tables,
>no-one can do any work at all. In separate tablespace, you could offline the
>index one, and people could still get at their data, albeit rather more
>slowly than they're used to.

sure? how about that one: the optimizer isn't aware of offlined tablespaces. Please enlighten me, how an application with GUI and bla should handle this one:

SQL> 
SQL> CLEAR scr
SQL> 
SQL> ALTER SESSION SET nls_language=american

  2 /

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> DROP TABLESPACE ts_data
  2 INCLUDING CONTENTS
  3 /

Tablespace dropped.

Elapsed: 00:00:02.44
SQL>
SQL> DROP TABLESPACE ts_idx
  2 INCLUDING CONTENTS
  3 /

Tablespace dropped.

Elapsed: 00:00:00.71
SQL>
SQL> CREATE TABLESPACE ts_data
  2 DATAFILE 'e:\oradata\o817\ts_data.ora'   3 SIZE 100M REUSE
  4 EXTENT MANAGEMENT LOCAL
  5 UNIFORM SIZE 128K
  6 /

Tablespace created.

Elapsed: 00:00:09.44
SQL>
SQL> CREATE TABLESPACE ts_idx
  2 DATAFILE 'e:\oradata\o817\ts_idx.ora'   3 SIZE 100M REUSE
  4 EXTENT MANAGEMENT LOCAL
  5 UNIFORM SIZE 128K
  6 /

Tablespace created.

Elapsed: 00:00:08.13
SQL>
SQL> CREATE TABLE t
  2 TABLESPACE ts_data
  3 NOLOGGING
  4 AS
  5 SELECT ROWNUM AS r,
  6 v.*
  7 FROM dba_source v
  8 /

Table created.

Elapsed: 00:00:14.01
SQL>
SQL> ALTER TABLE t
  2 ADD CONSTRAINT pk
  3 PRIMARY KEY (r)
  4 USING INDEX TABLESPACE ts_idx
  5 NOLOGGING
  6 COMPUTE STATISTICS
  7 /

Table altered.

Elapsed: 00:00:03.24
SQL>
SQL> ANALYZE TABLE t
  2 COMPUTE STATISTICS
  3 /

Table analyzed.

Elapsed: 00:00:11.26

SQL> 
SQL> SET TIMING ON
SQL> SELECT NAME

  2 FROM t
  3 WHERE r=1000
  4 /

NAME                                                                            
------------------------------                                                  
DBMS_AQIN                                                                       

Elapsed: 00:00:00.21
SQL>

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ATTENTION PLEASE

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SQL> ALTER TABLESPACE ts_idx
  2 OFFLINE
  3 /

Tablespace altered.

Elapsed: 00:00:00.70

SQL> SELECT NAME
  2 FROM t
  3 WHERE r=1000
  4 /
FROM t

     *
ERROR at line 2:
ORA-00376: file 4 cannot be read at this time ORA-01110: data file 4: 'E:\ORADATA\O817\TS_IDX.ORA'

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

POSSIBLE WORKAROUND ???

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Elapsed: 00:00:00.60
SQL>
SQL> SELECT NAME
  2 FROM t
  3 WHERE trunc(r)=1000
  4 /

NAME                                                                            
------------------------------                                                  
DBMS_AQIN                                                                       

Elapsed: 00:00:01.22
SQL>

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

THANK YOU VERY MUCH

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SQL> ALTER TABLESPACE ts_idx
  2 ONLINE
  3 /

Tablespace altered.

Elapsed: 00:00:00.31
SQL>
SQL> SELECT NAME
  2 FROM t
  3 WHERE r=1000
  4 /

NAME                                                                            
------------------------------                                                  
DBMS_AQIN                                                                       

Elapsed: 00:00:00.10
SQL>
SQL> SPOOL OFF Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sun Jan 12 2003 - 14:56:31 CST