oracle管理之 table(server.102 b14231)

b14231/15 manage table
Table Compression 用於只讀表提高io效能，減少cache
NOLOGGING 用於create table as select /ctas
Parallelizing 用於ctas 多處理器的情形
--臨時表，對所有session可見，不過資料只對session可見
CREATE GLOBAL TEMPORARY TABLE transaction-specific (the default) or session-specific,

DELETE ROWS transaction-specific
PRESERVE ROWS session-specific
--事務級的臨時表
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;--transaction-specific
--臨時表的內容，是由INSERT觸發的，並分配segment如果沒有insert，臨時表是沒有資料的;臨時表上可以建立index;沒有session邦定臨時表時才可以drop臨時表
--Parallelizing
ALTER SESSION FORCE PARALLEL DDL
A PARALLEL clause is included in the CREATE TABLE...AS SELECT statement
--例如
CREATE TABLE hr.admin_emp_dept
PARALLEL COMPRESS
AS SELECT * FROM hr.employees
WHERE department_id = 10;

--大容量操作，記錄錯誤記錄，避免大容量回滾誤時誤事
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('AAATTT', 'ERR_AAATTT');
INSERT INTO AAATTT(ID,NAME)
VALUES( '222222','22')
LOG ERRORS INTO ERR_AAATTT ('daily_load') REJECT LIMIT 25;

select t.*, t.rowid from err_aaattt t;

create table AAATTT
(
ID CHAR(5) not null,
NAME CHAR(10),
NOTE CHAR(30),
XINGMING VARCHAR2(10),
XINGM VARCHAR2(10),
NUM NUMBER(5,3),
NUM2 NUMBER(4),
NUM3 NUMBER(*,3),
DAT DATE,
TIMLZ TIMESTAMP(6) WITH LOCAL TIME ZONE,
TIMZ TIMESTAMP(6) WITH TIME ZONE,
CONSTRAIT PK_AAATTT PRIMARY KEY (ID)
)
--對應的error table為 err_aaattt
create table ERR_AAATTT
(
ORA_ERR_NUMBER$ NUMBER,
ORA_ERR_MESG$ VARCHAR2(2000),
ORA_ERR_ROWID$ UROWID(4000),
ORA_ERR_OPTYP$ VARCHAR2(2),
ORA_ERR_TAG$ VARCHAR2(2000),
ID VARCHAR2(4000),
NAME VARCHAR2(4000),
NOTE VARCHAR2(4000),
XINGMING VARCHAR2(4000),
XINGM VARCHAR2(4000),
NUM VARCHAR2(4000),
NUM2 VARCHAR2(4000),
NUM3 VARCHAR2(4000),
DAT VARCHAR2(4000),
TIMLZ VARCHAR2(4000),
TIMZ VARCHAR2(4000)
)
--Inserting Data Into Tables Using Direct-Path INSERT 是在high-water mark之上insert data，而不是使用hwm下面的free space

ALTER TABLE hr.admin_emp MOVE
STORAGE ( INITIAL 20K
NEXT 40K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 0 );
ALTER TABLE hr.admin_emp DROP (bonus, commission);
ALTER TABLE hr.admin_emp RENAME COLUMN comm TO commission;
ALTER TABLE hr.admin_emp ADD (bonus NUMBER (7,2));
ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);
ALTER TABLE...DROP UNUSED COLUMNS
--表線上重新定義；額外需要待重新定義的表的空間大小
DBMS_REDEFINITION by primary key or by rowid(iot除外)
CAN_REDEF_TABLE
alter session force parallel dml parallel degree-of-parallelism;
alter session force parallel query parallel degree-of-parallelism;
START_REDEF_TABLE
ABORT_REDEF_TABLE
COPY_TABLE_DEPENDENTS
FINISH_REDEF_TABLE
ALTER TABLE table_name SET UNUSED (M_ROW$$);
DBA_REDEFINITION_ERRORS
DBA_REDEFINITION_OBJECTS
REGISTER_DEPENDENT_OBJECT
UNREGISTER_DEPENDENT_OBJECT
SYNC_INTERIM_TABLE
ABORT_REDEF_TABLE

--flash back drop table
SELECT * FROM RECYCLEBIN;
BIN$unique_id$version
ALTER SESSION SET recyclebin = ON;
ALTER SESSION SET recyclebin = OFF;

USER_RECYCLEBIN
DBA_RECYCLEBIN
PURGE TABLE BIN$jsleilx392mk2=293$0;
PURGE TABLESPACE example;
PURGE TABLESPACE example USER oe;
PURGE RECYCLEBIN;
FLASHBACK TABLE int_admin_emp TO BEFORE DROP RENAME TO int2_admin_emp;
--iot索引表：可以分為index 和overflow兩部分儲存
Index-Organized Tables
ALTER TABLE admin_iot5 MOVE NOCOMPRESS;
ALTER TABLE admin_docindex MOVE ONLINE;
ALTER TABLE admin_docindex MOVE TABLESPACE admin_tbs2 OVERFLOW TABLESPACE admin_tbs3;
Secondary Indexes on Index-Organized Tables --They store logical rowids instead of physical rowids.--The logical rowid also includes a physical guess
ALTER INDEX ... UPDATE BLOCK REFERENCES
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('HR','COUNTRIES');

--table 資料字典
DBA_TABLES
ALL_TABLES
USER_TABLES
DBA_TAB_COLUMNS
ALL_TAB_COLUMNS
USER_TAB_COLUMNS
DBA_ALL_TABLES
ALL_ALL_TABLES
USER_ALL_TABLES
DBA_TAB_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
DBA_COL_COMMENTS
ALL_COL_COMMENTS
USER_COL_COMMENTS
DBA_EXTERNAL_TABLES
ALL_EXTERNAL_TABLES
USER_EXTERNAL_TABLES
DBA_EXTERNAL_LOCATIONS
ALL_EXTERNAL_LOCATIONS
USER_EXTERNAL_LOCATIONS
DBA_TAB_HISTOGRAMS
ALL_TAB_HISTOGRAMS
USER_TAB_HISTOGRAMS
DBA_TAB_STATISTICS
ALL_TAB_STATISTICS
USER_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
ALL_TAB_COL_STATISTICS
USER_TAB_COL_STATISTICS
DBA_TAB_MODIFICATIONS
ALL_TAB_MODIFICATIONS
USER_TAB_MODIFICATIONS
DBA_ENCRYPTED_COLUMNS
USER_ENCRYPTED_COLUMNS
ALL_ENCRYPTED_COLUMNS
DBA_UNUSED_COL_TABS
ALL_UNUSED_COL_TABS
USER_UNUSED_COL_TABS
DBA_PARTIAL_DROP_TABS
ALL_PARTIAL_DROP_TABS
USER_PARTIAL_DROP_TABS

[@more@]