#眉標=Oracle、Object、ROWID #副標=管理Oracle資料庫必要的功力(6) #大標=運用物件讓資料庫管理增加彈性(上) #作者=文/圖 張天慧 =====<反灰>============= CREATE TABLE [schema.] table (column datatype [ , column datatype ] ...) [TABLESPACE tablespace ] [ PCTFREE integer ] [ PCTUSED integer ] [ INITRANS integer ] [ MAXTRANS integer ] [ STORAGE storage-clause ] [LOGGING | NOLOGGING] =================== =====<反灰>============= DEFAULT STORAGE ( INITIAL integer[K|M] NEXT integer[K|M] MINEXTENTS integer MAXEXTENTS integer PCTINCREASE integer BUFFER POOL[DEFAULT | KEEP| RECYCLE] ================== =====<反灰>============= CREATE TABLE TEST.TEST_TABLE (AA VARCHAR2(10), BB VARCHAR2(10) NOT NULL, CC CHAR2(10)) TABLESPACE TEST_TABLESPACE PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 2 BUFFER_POOL KEEP) ; ================== =====<反灰>============= CREATE TABLE CHAINED_ROWS ( owner_name varchar2(30), table_name varchar2(30), cluster_name varchar2(30), partition_name varchar2(30), subpartition_name varchar2(30), head_rowid rowid, analyze_timestamp date); ================== =====<反灰>============= SELECT B.owner_name AS "OWNER", A.table_name AS "TABLE NAME", B.row_count AS "ROW COUNT", A.num_rows AS "TOTAL ROWS" FROM ALL_tables A, (SELECT B.owner_name, B.table_name, COUNT(B.head_rowid) ROW_COUNT FROM chained_rows B GROUP BY B.owner_name, B.table_name) B WHERE A.table_name=B.table_name; ================== =====<反灰>============= DECLARE v_owner VARCHAR2(30) := 'USER_NAME' ; v_segment_name VARCHAR2(30) := 'TEST_TABLE'; v_segment_type VARCHAR2(30) := 'TABLE'; v_total_blocks NUMBER; v_total_bytes NUMBER; v_unused_blocks NUMBER; v_unused_bytes NUMBER; v_last_used_extent_file_id NUMBER; v_last_used_extent_block_id NUMBER; v_last_used_block NUMBER; BEGIN dbms_space.unused_space(v_owner, v_segment_name, v_segment_type, v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block); dbms_output.put_line(INITCAP(v_segment_type)||' :'||v_owner||'.'||v_segment_name); dbms_output.put_line('Total Blocks :'||TO_CHAR(v_total_blocks)); dbms_output.put_line('Blocks above HWM :'||TO_CHAR(v_unused_blocks)); END; / ================== =====<反灰>============= ALTER TABLE TEST.TEST_TABLE PCTFREE 40 PCTUSED 20 STORAGE (NEXT 1M MINEXTENTS 3 MAXEXTENTS 200); ================== =====<反灰>============= ALTER TABLE TEST.TEST_TABLE ALLOCATE EXTENT (SZIE 500K DATAFILE ‘/u01/oracle/data/test/users_data_01.dbf’); ================== =====<反灰>============= CREATE TABLE TEST.TEST_TABLE02 ( COLUMN01 VARCHAR2(4),COLUMN02 VARCHAR2(4), COLUMN03 VARCHAR2(4),COLUMN04 VARCHAR2(4) NOT NULL, COLUMN05 VARCHAR2(4) DEFAULT 1,COLUMN06 VARCHAR2(4), CONSTRAINT PK_TABLE02 PRIMARY KEY(COLUMN01), CONSTRAINT UNIQ_TABLE02 UNIQUE(COLUMN02), CONSTRAINT FK_TABLE02 FOREIGN KEY(COLUMN03) REFERENCES TEST.TEST_TABLE(COLUMN4), CONSTRAINT CHK_TABLE02 CHECK(COLUMN06 IN('W','X','Y','Z'))); ======================= =====<反灰>============= ALTER TABLE TEST.TEST_TABLE02 DISABLE PRIMARY KEY; ALTER TABLE TEST.TEST_TABLE02 DISABLE UNIQUE(COLUMN02); ======================= =====<反灰>============= ALTER TABLE TEST.TEST_TABLE02 ENABLE PRIMARY KEY; ALTER TABLE TEST.TEST_TABLE02 ENABLE UNIQUE(COLUMN02); =======================