12c到没有执行建立在哪里
[20181018]临时表lob段建立在哪里.txt
--//链接:http://www.itpub.net/thread-2105833-1-1.html的讨论.
--//才知道全局临时表中的大字段的索引,是建在SYSTEM表空间上的
--//自己测试看看,首先我什么觉得临时表不支持lob字段类型.
1.环境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
CREATE GLOBAL TEMPORARY TABLE T
( ID NUMBER,
IMAGE BLOB
)
ON COMMIT PRESERVE ROWS
LOB (IMAGE) STORE AS basicfile ( ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE ) ;
--//如果指定securefile报错,也就是临时表的lob仅仅支持basicfile.
--//看看如何定义:
SCOTT@test01p> @ ddl scott.t
C100
---------------------------------------------------
CREATE GLOBAL TEMPORARY TABLE "SCOTT"."T"
( "ID" NUMBER,
"IMAGE" BLOB
) ON COMMIT PRESERVE ROWS ;
--//看到的内容很少。也说明临时表支持的内容很少。
CREATE TABLE Tx
( ID NUMBER,
IMAGE BLOB
)
LOB (IMAGE) STORE AS securefile ( ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE ) ;
SCOTT@test01p> @ ddl scott.tx
C100
-----------------------------------------------------------
CREATE TABLE "SCOTT"."TX"
( "ID" NUMBER,
"IMAGE" BLOB
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
LOB ("IMAGE") STORE AS SECUREFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES ) ;
SCOTT@test01p> insert into t values (2,lpad('b',4000,'b'));
1 row created.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select table_name,index_name from user_indexes where tablespace_name='SYSTEM';
no rows selected
SCOTT@test01p> select table_name,index_name,tablespace_name from user_indexes where table_name='T';
TABLE_NAME INDEX_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
T SYS_IL0000022802C00002$$
--//12c临时表没有指明使用那个表空间在lob索引段使用那个表空间。
SCOTT@test01p> select table_name,column_name,segment_name,tablespace_name from USER_LOBS;
TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME
-------------------- -------------------- ------------------------------ --------------------
T IMAGE SYS_LOB0000022811C00002$$ TEMP
TX IMAGE SYS_LOB0000022814C00002$$ USERS