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@]