The following is a just one of my weird 
thinking patterns I want to write down here.
Therefore this is one of my first “scratchpad” exercises. maybe more will follow.
I hope it will help someone out there who is trying the same stuff…
Timing is done to see what the performance is; Tablespace storage details where checked by me to see, check overall results / demonstrate behavior.
SQL*Plus: Release 10.2.0.2.0 - Production ON Thu Nov 2 15:22:56 2006 Copyright (c) 1982, 2005, Oracle. ALL Rights Reserved. Connected TO: Oracle DATABASE 10g Enterprise Edition Release 10.2.0.2.0 - Production WITH the Partitioning, OLAP AND DATA Mining options 16:42:31 SQL> conn system/oracle Connected. 16:42:58 SQL> SET long 100000000 16:43:02 SQL> SET pages 5000 16:43:05 SQL> SELECT dbms_metadata.get_ddl('TABLESPACE','DAT_TS01') FROM dual; DBMS_METADATA.GET_DDL('TABLESPACE','DAT_TS01') ---------------------------------------------------- CREATE TABLESPACE "DAT_TS01" DATAFILE '/oracle/oradata/UWVTST/DAT_TS01.ora' SIZE 52428800 AUTOEXTEND ON NEXT 10485760 MAXSIZE 2048M, '/oracle/oradata/UWVTST/DAT_TS01_2.ora' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 2048M, '/oracle/oradata/UWVTST/DAT_TS01_3.ora' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 2048M, '/oracle/oradata/UWVTST/DAT_TS01_4.ora' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 2048M, '/oracle/oradata/UWVTST/DAT_TS01_5.ora' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 2048M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2097152 SEGMENT SPACE MANAGEMENT AUTO ALTER DATABASE DATAFILE '/oracle/oradata/UWVTST/DAT_TS01.ora' RESIZE 587202560 ALTER DATABASE DATAFILE '/oracle/oradata/UWVTST/DAT_TS01_2.ora' RESIZE 629145600 ALTER DATABASE DATAFILE '/oracle/oradata/UWVTST/DAT_TS01_3.ora' RESIZE 576716800 ALTER DATABASE DATAFILE '/oracle/oradata/UWVTST/DAT_TS01_4.ora' RESIZE 576716800 ALTER DATABASE DATAFILE '/oracle/oradata/UWVTST/DAT_TS01_5.ora' RESIZE 2044723200 Elapsed: 00:00:01.85 16:43:37 SQL> SELECT dbms_metadata.get_ddl('TABLESPACE','IDX_TS01') FROM dual; DBMS_METADATA.GET_DDL('TABLESPACE','IDX_TS01') ---------------------------------------------------- CREATE TABLESPACE "IDX_TS01" DATAFILE '/oracle/oradata/UWVTST/IDX_TS01.ora' SIZE 52428800 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M, '/oracle/oradata/UWVTST/IDX_TS01_1.ora' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 2048M, '/oracle/oradata/UWVTST/IDX_TS01_2.ora' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 2048M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 SEGMENT SPACE MANAGEMENT AUTO ALTER DATABASE DATAFILE '/oracle/oradata/UWVTST/IDX_TS01.ora' RESIZE 188743680 ALTER DATABASE DATAFILE '/oracle/oradata/UWVTST/IDX_TS01_1.ora' RESIZE 157286400 ALTER DATABASE DATAFILE '/oracle/oradata/UWVTST/IDX_TS01_2.ora' RESIZE 146800640 Elapsed: 00:00:00.15 16:43:46 SQL> SELECT dbms_metadata.get_ddl('TABLESPACE','TEMP') FROM dual; DBMS_METADATA.GET_DDL('TABLESPACE','TEMP') ----------------------------------------------- CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/oracle/oradata/UWVTST/temp01.dbf' SIZE 49283072 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 Elapsed: 00:00:00.16 16:44:02 SQL> SHOW sga Total System Global Area 524288000 bytes Fixed SIZE 1261788 bytes Variable SIZE 423628580 bytes DATABASE Buffers 96468992 bytes Redo Buffers 2928640 bytes SQL> conn HGO/HGO Connected. SQL> SHOW USER USER IS "HGO" SET long 100000 SET pages 5000 SQL> DESC HGO010_DETAM Name NULL? TYPE ----------------------------------------- -------- --------- HGO_ID NOT NULL NUMBER(12) GEGEVENS NOT NULL SYS.XMLTYPE SQL> SELECT dbms_metadata.get_ddl('TABLE','HGO010_DETAM') 2 FROM dual; DBMS_METADATA.GET_DDL('TABLE','HGO010_DETAM') --------------------------------------------------- CREATE TABLE "HGO"."HGO010_DETAM" ( "HGO_ID" NUMBER(12,0) NOT NULL ENABLE, "GEGEVENS" "SYS"."XMLTYPE" NOT NULL ENABLE, CONSTRAINT "FK_HGO010_HGO000" FOREIGN KEY ("HGO_ID") REFERENCES "HGO"."HGO000_SOFI" ("HGO_ID") ON DELETE CASCADE ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DAT_TS01" XMLTYPE COLUMN "GEGEVENS" STORE AS CLOB ( TABLESPACE "DAT_TS01" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) -- XML Data is roundabout 650 Mb storage on disk. SQL> SELECT COUNT(*) FROM "HGO"."HGO010_DETAM"; COUNT(*) ---------- 7500 SQL> conn sys/oracle AS sysdba Connected. SQL> purge dba_recyclebin 2 ; DBA Recyclebin purged. SQL> @tablespace Tablespace Name Specific Frag. Free SPACE(%) Free SPACE(Mb) Used SPACE(Mb) MAX.SPACE(Mb) -------------------- -------- ------ ------------- -------------- -------------- ------------- DAT_TS01 DATAFILE 34 89 3,738 472 4,210 EXAMPLE DATAFILE 2 32 32 68 100 IDX_TS01 DATAFILE 3 91 64 6 70 SYSAUX DATAFILE 209 14 55 335 390 SYSTEM DATAFILE 2 1 6 504 510 TEMP TEMPFILE 2 13 6 41 47 UNDOTBS1 DATAFILE 3 96 851 39 890 USERS DATAFILE 1 2 0 7 7 -------------- -------------- ------------- SUM 4,753 1,471 6,224 8 ROWS selected. SQL> conn hgo Connected. SQL> col OBJECT_NAME FOR a30 SQL> col object_type FOR a20 SQL> SELECT object_name, object_type FROM user_objects 2 ; OBJECT_NAME OBJECT_TYPE ------------------------------ -------------------- HGO000_SOFI TABLE HGO010_DETAM TABLE SYS_LOB0000054274C00003$$ LOB HGO000_PK INDEX HGO000_U_IND01 INDEX HGO010_U_IND01 INDEX 6 ROWS selected. SQL> col SEGMENT_NAME FOR a30 SQL> SELECT segment_name, segment_type, tablespace_name, bytes FROM user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES ------------------------------ ------------------ ------------------------------ ---------- SYS_IL0000054274C00003$$ LOBINDEX DAT_TS01 2097152 SYS_LOB0000054274C00003$$ LOBSEGMENT DAT_TS01 478150656 HGO010_DETAM TABLE DAT_TS01 2097152 HGO000_SOFI TABLE DAT_TS01 2097152 HGO010_U_IND01 INDEX IDX_TS01 1048576 HGO000_U_IND01 INDEX IDX_TS01 1048576 HGO000_PK INDEX IDX_TS01 1048576 7 ROWS selected. SQL> conn sys AS sysdba Connected. SQL> @tablespace Tablespace Name Specific Frag. Free SPACE(%) Free SPACE(Mb) Used SPACE(Mb) MAX.SPACE(Mb) -------------------- -------- ------ ------------- -------------- -------------- ------------- DAT_TS01 DATAFILE 34 89 3,738 472 4,210 EXAMPLE DATAFILE 2 32 32 68 100 IDX_TS01 DATAFILE 3 91 64 6 70 SYSAUX DATAFILE 209 14 55 335 390 SYSTEM DATAFILE 2 1 6 504 510 TEMP TEMPFILE 2 13 6 41 47 UNDOTBS1 DATAFILE 3 96 851 39 890 USERS DATAFILE 1 2 0 7 7 -------------- -------------- ------------- SUM 4,753 1,471 6,224 8 ROWS selected. SQL> SELECT * FROM v$sort_usage ; no ROWS selected SQL> conn hgo Connected. SQL> SELECT * FROM session_roles; ROLE ------------------------------ CONNECT RESOURCE SQL> SELECT * FROM session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE VIEW CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE 11 ROWS selected. SQL> SELECT * FROM user_tables; TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- DEGREE INSTANCES CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE GLO USE DURATION SKIP_COR MON CLUSTER_OWNER DEPENDEN COMPRESS DRO ---------- ---------- ----- -------- ----------- --------- --- ------------ - - --- ------- -------- --- --- --------------- -------- --- ------------------------------ -------- -------- --- HGO010_DETAM DAT_TS01 VALID 10 1 255 2097152 2097152 1 2147483645 0 YES N 7500 122 0 0 0 160 0 0 1 1 N ENABLED 7500 09-JUN-06 NO N N NO DEFAULT DISABLED YES NO DISABLED YES DISABLED DISABLED NO HGO000_SOFI DAT_TS01 VALID 10 1 255 2097152 2097152 1 2147483645 0 YES N 16200 122 0 0 0 26 0 0 1 1 N ENABLED 16200 09-JUN-06 NO N N NO DEFAULT DISABLED YES NO DISABLED YES DISABLED DISABLED NO SQL> SET TIME ON timing ON 15:54:01 SQL> ALTER TABLE HGO.HGO010_DETAM move 15:54:07 2 tablespace DAT_TS01; TABLE altered. Elapsed: 00:00:01.14 SQL> ALTER TABLE hgo.hgo010_detam enable ROW movement; TABLE altered. SQL> ALTER TABLE hgo.hgo010_detam shrink SPACE; TABLE altered. SQL> ALTER TABLE hgo.hgo010_detam deallocate unused; TABLE altered. SQL> ALTER TABLE HGO.HGO010_DETAM move 2 tablespace IDX_TS01; TABLE altered. SQL> SELECT segment_name, segment_type, tablespace_name, bytes FROM user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES ------------------------------ ------------------ ------------------------------ ---------- SYS_IL0000054274C00003$$ LOBINDEX DAT_TS01 2097152 SYS_LOB0000054274C00003$$ LOBSEGMENT DAT_TS01 478150656 HGO000_SOFI TABLE DAT_TS01 2097152 HGO010_DETAM TABLE IDX_TS01 2097152 HGO010_U_IND01 INDEX IDX_TS01 1048576 HGO000_U_IND01 INDEX IDX_TS01 1048576 HGO000_PK INDEX IDX_TS01 1048576 7 ROWS selected. |
http://forums.oracle.com/forums/thread.jspa?threadID=397537
alter table
/* ---------------------------------------------------------------------- select 'alter table '||table_name||' move tablespace &YOUR_TS'||chr(10) ||'LOB ('||column_name||') store as '||segment_name||chr(10) ||'(tablespace &YOUR_TS);' from user_lobs ---------------------------------------------------------------------- */ SQL> ALTER tablespace DAT_TS01 READ WRITE; Tablespace altered. SQL> ALTER tablespace IDX_TS01 READ WRITE; Tablespace altered. SQL> SELECT 'alter table '||TABLE_NAME||' move tablespace &YOUR_TS'||chr(10) 2 ||'LOB ('||column_name||') store as '||segment_name||chr(10) 3 ||'(tablespace &YOUR_TS);' "DDL Statement" 4* FROM user_lobs; DDL Statement ----------------------------------------------------------- ALTER TABLE HGO010_DETAM move tablespace IDX_TS01 LOB (SYS_NC00003$) store AS SYS_LOB0000054274C00003$$ (tablespace IDX_TS01); /* Statement took about 4 minits - Timed via OS clock */ SQL> SELECT segment_name, segment_type, tablespace_name, bytes FROM user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES ------------------------------ ------------------ ------------------------------ ---------- HGO000_SOFI TABLE DAT_TS01 2097152 HGO010_DETAM TABLE IDX_TS01 2097152 SYS_IL0000054274C00003$$ LOBINDEX IDX_TS01 1048576 SYS_LOB0000054274C00003$$ LOBSEGMENT IDX_TS01 479199232 HGO010_U_IND01 INDEX IDX_TS01 1048576 HGO000_U_IND01 INDEX IDX_TS01 1048576 HGO000_PK INDEX IDX_TS01 1048576 7 ROWS selected. SET timing ON TIME ON ALTER TABLE HGO010_DETAM move tablespace DAT_TS01 LOB (SYS_NC00003$) store AS SYS_LOB0000054274C00003$$ (tablespace DAT_TS01); SQL> conn system Connected. 16:23:45 SQL> @tablespace Tablespace Name Specific Frag. Free SPACE(%) Free SPACE(Mb) Used SPACE(Mb) MAX.SPACE(Mb) -------------------- -------- ------ ------------- -------------- -------------- ------------- DAT_TS01 DATAFILE 5 89 3,738 472 4,210 EXAMPLE DATAFILE 2 32 32 68 100 IDX_TS01 DATAFILE 3 99 464 6 470 SYSAUX DATAFILE 209 14 55 335 390 SYSTEM DATAFILE 2 1 6 504 510 TEMP TEMPFILE 2 13 6 41 47 UNDOTBS1 DATAFILE 3 95 849 41 890 USERS DATAFILE 1 2 0 7 7 -------------- -------------- ------------- SUM 5,151 1,473 6,624 8 ROWS selected. Elapsed: 00:00:32.40 16:24:19 SQL> conn hgo Connected. Elapsed: 00:00:00.32 16:25:10 SQL> col segment_name FOR a30 16:25:17 SQL> SELECT segment_name, segment_type, tablespace_name, bytes FROM user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES ------------------------------ ------------------ ------------------------------ ---------- HGO010_DETAM TABLE DAT_TS01 2097152 SYS_LOB0000054274C00003$$ LOBSEGMENT DAT_TS01 478150656 SYS_IL0000054274C00003$$ LOBINDEX DAT_TS01 2097152 HGO000_SOFI TABLE DAT_TS01 2097152 HGO010_U_IND01 INDEX IDX_TS01 1048576 HGO000_U_IND01 INDEX IDX_TS01 1048576 HGO000_PK INDEX IDX_TS01 1048576 7 ROWS selected. Elapsed: 00:00:00.05 SQL> SET timing ON TIME ON 16:16:26 SQL> ALTER TABLE HGO010_DETAM move tablespace DAT_TS01 16:16:26 2 LOB (SYS_NC00003$) store AS SYS_LOB0000054274C00003$$ 16:16:26 3 (tablespace DAT_TS01); TABLE altered. Elapsed: 00:03:57.52 /* During this action run top in a second session: --> Nor %CPU, nor %MEM used more then 10% of total */ 16:25:20 SQL> ALTER TABLE HGO010_DETAM move tablespace IDX_TS01 16:26:28 2 LOB (SYS_NC00003$) store AS SYS_LOB0000054274C00003$$ 16:26:28 3 (tablespace IDX_TS01); TABLE altered. 16:31:54 SQL> SELECT segment_name, segment_type, tablespace_name, bytes FROM user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES ------------------------------ ------------------ ------------------------------ ---------- HGO000_SOFI TABLE DAT_TS01 2097152 HGO010_DETAM TABLE IDX_TS01 2097152 SYS_IL0000054274C00003$$ LOBINDEX IDX_TS01 1048576 SYS_LOB0000054274C00003$$ LOBSEGMENT IDX_TS01 479199232 HGO010_U_IND01 INDEX IDX_TS01 1048576 HGO000_U_IND01 INDEX IDX_TS01 1048576 HGO000_PK INDEX IDX_TS01 1048576 7 ROWS selected. Elapsed: 00:00:00.07 Elapsed: 00:03:38.50 16:32:27 SQL> conn system/oracle Connected. 16:32:42 SQL> @tablespace Tablespace Name Specific Frag. Free SPACE(%) Free SPACE(Mb) Used SPACE(Mb) MAX.SPACE(Mb) -------------------- -------- ------ ------------- -------------- -------------- ------------- DAT_TS01 DATAFILE 5 100 4,198 12 4,210 EXAMPLE DATAFILE 2 32 32 68 100 IDX_TS01 DATAFILE 3 1 4 466 470 SYSAUX DATAFILE 209 14 55 335 390 SYSTEM DATAFILE 2 1 6 504 510 TEMP TEMPFILE 2 13 6 41 47 UNDOTBS1 DATAFILE 3 95 846 44 890 USERS DATAFILE 1 2 0 7 7 -------------- -------------- ------------- SUM 5,148 1,476 6,624 8 ROWS selected. Elapsed: 00:00:34.47 16:33:18 SQL> ALTER tablespace DAT_TS01 READ ONLY; Tablespace altered. 16:37:13 SQL> ALTER tablespace IDX_TS01 READ ONLY; Tablespace altered. 16:39:35 SQL> SELECT STATUS, TABLE_NAME FROM user_tables; STATUS TABLE_NAME -------- ------------------------------ VALID HGO000_SOFI VALID HGO010_DETAM Elapsed: 00:00:00.56 16:39:44 SQL> SELECT STATUS, index_name FROM user_indexes; STATUS INDEX_NAME -------- ------------------------------ VALID SYS_IL0000054274C00003$$ UNUSABLE HGO010_U_IND01 VALID HGO000_PK VALID HGO000_U_IND01 Elapsed: 00:00:01.06 16:40:30 SQL> SELECT dbms_metadata.get_ddl('INDEX','HGO010_U_IND01') FROM dual; DBMS_METADATA.GET_DDL('INDEX','HGO010_U_IND01') -------------------------------------------------------------------------------- CREATE UNIQUE INDEX "HGO"."HGO010_U_IND01" ON "HGO"."HGO010_DETAM" ("HGO_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "IDX_TS01" ALTER INDEX "HGO"."HGO010_U_IND01" UNUSABLE 16:40:44 SQL> ALTER INDEX "HGO"."HGO010_U_IND01" rebuild online; INDEX altered. Elapsed: 00:00:02.58 16:41:27 SQL> SELECT STATUS, index_name FROM user_indexes; STATUS INDEX_NAME -------- --------------------------- VALID SYS_IL0000054274C00003$$ VALID HGO010_U_IND01 VALID HGO000_PK VALID HGO000_U_IND01 Elapsed: 00:00:00.15 16:42:25 SQL> SELECT * FROM user_lobs; TABLE_NAME ------------------------------ COLUMN_NAME ------------------------------------------------------------------------------------------------------------------------------------ SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK PCTVERSION RETENTION ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- FREEPOOLS CACHE LOGGING IN_ FORMAT PAR ---------- ---------- ------- --- --------------- --- HGO010_DETAM SYS_NC00003$ SYS_LOB0000054274C00003$$ IDX_TS01 SYS_IL0000054274C00003$$ 8192 900 NO YES YES ENDIAN NEUTRAL NO Elapsed: 00:00:00.49 |
1 comment
Marco Gralike
15 March, 2009 at 16:52 (UTC 1) Link to this comment
Some extra info can be found here:
http://surachartopun.com/2009/03/move-lobsegment-to-different-tablespace.html