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
Some extra info can be found here:
http://surachartopun.com/2009/03/move-lobsegment-to-different-tablespace.html