</p> 2 FROM dba_segments
7 C: v% h" e& r7 G( g7 u 3 WHERE tablespace_name IN (UPPER(’&&OLD_DATA_TBS’), UPPER(’&&OLD_INDX_TBS’));
5 q9 Y) w# F% m, E: s, I old 3: WHERE tablespace_name IN (UPPER(’&&OLD_DATA_TBS’), UPPER(’&&OLD_INDX_TBS’)); w$ S( G# z: E
new 3: WHERE tablespace_name IN (UPPER(’data_001’), UPPER(’indx_001’))
. ?5 l: L a# |. s: P# P; |. w no rows selected
- g3 {1 k, V: \& X; d6 e SQL> -- Check for users assigned to old tablespaces, O% G2 H/ l! A2 g9 N9 R; V, B
SQL> SELECT username, default_tablespace FROM dba_users
9 `- d! S: _& ?0 o T 2 WHERE default_tablespace IN (UPPER(’&&OLD_DATA_TBS’), UPPER(’&&OLD_INDX_TBS’));
C6 a! X, A* }8 H7 ?4 { old 2: WHERE default_tablespace IN (UPPER(’&&OLD_DATA_TBS’), UPPER(’&&OLD_INDX_TBS’)). y! z1 t: {8 }3 {( q: e
new 2: WHERE default_tablespace IN (UPPER(’data_001’), UPPER(’indx_001’))* q+ J( n& N( [: \
USERNAME DEFAULT_TABLESPACE
1 x8 K: u& l2 k1 j3 z9 k8 K ------------------------------ ------------------------------) u' {: ]) U; R. ?9 R$ G6 b
APP_001 DATA_001
: B. \/ {/ B1 | SQL> -- Assign new default tablespaces for users, as necessary
8 ?+ v8 X! H* E' u SQL> ALTER USER app_0013 m% g7 ~$ o. T4 s
2 DEFAULT TABLESPACE data_002;
, m* T* V' j6 [$ E! c User altered.
5 J) F* g+ L+ f! s7 N# V: O6 P1 j SQL> -- List the data file names of old tablespaces& z% s0 B! n8 }, ~3 k8 ^
SQL> COL tablespace_name FOR A15;% D1 f8 w, [, @
SQL> COL file_name FOR A70;+ N- G( j: _+ I0 k, h9 `: {2 w
SQL> SET LINES 100;& \# V+ C6 O0 T+ S/ J/ f1 Q
SQL> SELECT tablespace_name, file_name' s+ b! ?% J+ k+ m. M- c
2 FROM dba_data_files9 b% C4 i* _, `2 j# |! N
3 WHERE tablespace_name IN (UPPER(’&&OLD_DATA_TBS’), UPPER(’&&OLD_INDX_TBS’));
4 G! Q7 p1 O2 ~ old 3: WHERE tablespace_name IN (UPPER(’&&OLD_DATA_TBS’), UPPER(’&&OLD_INDX_TBS’))
& o" c" Y! q/ J p1 _( }4 a ] new 3: WHERE tablespace_name IN (UPPER(’data_001’), UPPER(’indx_001’))5 V4 U; i3 }; C0 f- U
TABLESPACE_NAME FILE_NAME& b; R: t- W/ B" d+ R5 Y
--------------- ----------------------------------------------------------------------6 q9 f( w: r' ], E, g
DATA_001 /data01/oracle/db001/datafile/o1_mf_data_001_4m081w7m_.dbf9 E" q) b% A1 G: c1 o( o
INDX_001 /data01/oracle/db001/datafile/o1_mf_indx_001_4m082l4q_.dbf
0 I1 h# \9 q9 k+ f* j# @ SQL> -- Drop old tablespaces, but keep data files in place
& M, g: @" O* k% I6 U* M( C4 [( f7 | SQL> DROP TABLESPACE data_001
! E7 z% y& h' U9 w% T3 z( Y1 _4 u* R 2 INCLUDING CONTENTS KEEP DATAFILES;' X8 {! Q3 H; W5 `
Tablespace dropped.
) z7 v0 p1 m# r' c$ ^+ \ SQL> DROP TABLESPACE indx_001 z/ V0 P+ M6 e: p" P/ K
/ X9 g6 j! A& x! f0 z 2 INCLUDING CONTENTS KEEP DATAFILES; |