|
T/CONSTRAINT
-- CONNECT SYS
ALTER TABLE "SCOTT"."CESHI" ADD CONSTRAINT "PK_ID" PRIMARY KEY ("EMPNO")
USING INDEX "SCOTT"."PK_ID" ENABLE;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2(60);
I_O VARCHAR2(60);
NV VARCHAR2(1);
c DBMS_METADATA.T_VAR_COLL;
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:
2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
i_n := 'PK_ID';
i_o := 'SCOTT';
EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,14,1,14,1,1,1,0,14,NV,NV,TO_DATE('2018-11-29 15:17:22',df),NV;
DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
DECLARE I_N VARCHAR2(60);
I_O VARCHAR2(60);
NV VARCHAR2(1);
c DBMS_METADATA.T_VAR_COLL;
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:
2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
i_n := 'ID2';
i_o := 'SCOTT';
EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,14,1,3,1,1,1,0,14,NV,NV,TO_DATE('2018-11-29 15:18:08',df),NV;
DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
---查询表的ddl语法
select dbms_metadata.get_ddl('TABLE','CESHI','SCOTT') ddl_text from dual
DDL_TEXT
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."CESHI"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_ID" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
--表不存在storage属性后
insert 数据进行测试
SQL> insert into scott.ceshi select * from scott.emp;
--查询表及索引的extent分配，发现表其实并没有extent storage属性，是怎么分配的呢？
SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS,EXTENTS,INITIAL_EXTENT/1024 INITIAL_EXTENT_K,NEXT_EXTENT/1024 NEXT_EXTENT_K from
user_segments where segment_name in(select object_name from user_objects where created>sysdate-1);
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS INITIAL_EXTENT_K NEXT_EXTENT_K
--------------- ------------------ ---------- --------- |