I. Temporary tableThe temporary table is placed in the temporal table space without generating redo, only with undo. You can create indexes, views, and triggers in a temporary table, and you can also export and import the definition of a temporary table using either the Export and import (export and import) or data Pump (pump). But. Data is not exported even if the rows option is used. There are transaction-based (default) and session-based two types. Other sessions cannot be interviewed.. In the temporary table. DML locks are never required.
1. Create a default (transaction-based) temporary table: (On commit Delete rows: Empty data on commit)
Sql> Create global temporary table TEMP_EMP1 on commit delete rows as select * from Scott.emp where 1=2;
Table created.
sql> INSERT INTO TEMP_EMP1 select * from Scott.emp;
Rows created.
Sql> select * from TEMP_EMP1;
empno ename job mgr hiredate   ; SAL COMM DEPTNO----------------------------------------------- ------------------------------- 7369 SMITH clerk &N Bsp 7902 17-dec-80 800  20&NBSP ; 7499 ALLEN salesman 7698 20-feb-81 1600 300 30 7521 WARD salesman 7698 22-feb-81 1250 500 &N Bsp 30 7566 JONES manager 7839 02-apr-81 &NB Sp 2975   20 7654 MARTIN salesman &N Bsp 7698 28-sep-81 1250 1400 30&NBSP ; 7698 BLAKE manager 7839 01-may-81 2850 30 7782 CLARK &NBSP ; MANAGER 7839 09-jun-81 2450 &NB Sp 10 7788 SCOTT analyst 7566 19-apr- $ 20 7 839 KING President 17-nov-81 &NBSP ; &nbsP 10 7844 TURNER salesman &N Bsp 7698 08-sep-81 $ 0 30&N Bsp 7876 ADAMS clerk 7788 23-may-87 1100  20
EMPNO ename JOB MGR hiredate SAL COMM DEPTNO--------------------------------------- ---------------------------------------7900 JAMES clerk 7698 03-dec-81 950 7902 FORD ANALYST 7566 03-dec-81 7934 MILLER Clerk 7782 23-jan-82 1300 10
Rows selected. #提交SQL > Commit;
Commit complete.
Sql> select * from TEMP_EMP1;
No rows selected
2.create another session-based temporal table (On commit Preserve rows: data is persisted on commit. But the data will be emptied when you exit the session) Sql> Create global temporary table TEMP_EMP2 on commit preserve rows as select * from Scott.emp where 1=2;
Table created.
sql> INSERT INTO TEMP_EMP2 select * from Scott.emp;
Rows created.
Sql> sql> commit;
Commit complete.
Sql> select * from TEMP_EMP2;
empno ename job mgr hiredate   ; SAL COMM DEPTNO----------------------------------------------- ------------------------------- 7369 SMITH clerk &N Bsp 7902 17-dec-80 800  20&NBSP ; 7499 ALLEN salesman 7698 20-feb-81 1600 300 30 7521 WARD salesman 7698 22-feb-81 1250 500 &N Bsp 30 7566 JONES manager 7839 02-apr-81 &NB Sp 2975   20 7654 MARTIN salesman &N Bsp 7698 28-sep-81 1250 1400 30&NBSP ; 7698 BLAKE manager 7839 01-may-81 2850 30 7782 CLARK &NBSP ; MANAGER 7839 09-jun-81 2450 &NB Sp 10 7788 SCOTT analyst 7566 19-apr- $ 20 7 839 KING President 17-nov-81 &NBSP ; &nbsP 10 7844 TURNER salesman &N Bsp 7698 08-sep-81 $ 0 30&N Bsp 7876 ADAMS clerk 7788 23-may-87 1100  20
EMPNO ename JOB MGR hiredate SAL COMM DEPTNO--------------------------------------- ---------------------------------------7900 JAMES clerk 7698 03-dec-81 950 7902 FORD ANALYST 7566 03-dec-81 7934 MILLER Clerk 7782 23-jan-82 1300 10
Rows selected.
two. Manual strip:#分析表SQL > Analyze table scott.emp Compute statistics; Table analyzed. Sql> Select Table_name,tablespace_name,blocks,empty_blocks from dba_tables where owner= ' SCOTT ';
TABLE_NAME tablespace BLOCKS empty_blocks------------------------------------------DEPT usersemp USERS 5 3BONUS Userssalgrade USERS
Sql> col name for a50sql> select File#,name from V$datafile;
file# NAME------------------------------------------------------------1/u01/app/oracle/oradata/prod/disk3/syste M01.DBF 2/u01/app/oracle/oradata/prod/disk4/undotbs01.dbf 3/u01/app/oracle/oradata/prod/disk4/sysaux01.d BF 4/u01/app/oracle/oradata/prod/disk3/users01.dbf 5/U01/APP/ORACLE/ORADATA/PROD/DISK3/LXTBS01.DBF 6/u01/app/oracle/oradata/prod/disk4/lxtbs02.dbf
6 rows selected.
Sql> ALTER TABLE Scott.emp allocate extent (size 20m datafile '/u01/app/oracle/oradata/prod/disk3/system01.dbf '); ALTER TABLE Scott.emp allocate extent (size 20m datafile '/u01/app/oracle/oradata/prod/disk3/system01.dbf ') *error at Line 1:ora-03284:datafile/u01/app/oracle/oradata/prod/disk3/system01.dbf are not a member of Tablespaceusers
#分配数据块给EMP表SQL > ALTER TABLE Scott.emp allocate extent (size 20m datafile '/u01/app/oracle/oradata/prod/disk3/ Users01.dbf ');
Table altered.
Sql> Analyze table scott.emp estimate statistics;
Table analyzed.
Sql> Select Table_name,tablespace_name,blocks,empty_blocks from dba_tables where owner= ' SCOTT ';
TABLE_NAME tablespace BLOCKS empty_blocks------------------------------------------DEPT usersemp USERS5 2563BONUS Userssalgrade users# Retract empty blockSql>ALTER TABLE scott.emp deallocate unused;
Table altered.
Sql> Analyze table scott.emp estimate statistics;
Table analyzed.
Sql> Select Table_name,tablespace_name,blocks,empty_blocks from dba_tables where owner= ' SCOTT ';
TABLE_NAME tablespace BLOCKS empty_blocks------------------------------------------DEPT usersemp USERS 5 3BONUS Userssalgrade USERS
three. Migrating tables/Indexes to other tablespaces:
# migrating tables to other table spaces Sql> ALTER TABLE Scott.empMoveTablespace system;
Table altered.
Sql> Select Table_name,tablespace_name,blocks,empty_blocks from dba_tables where owner= ' SCOTT ';
TABLE_NAME tablespace BLOCKS empty_blocks------------------------------------------DEPT usersbonus USERS Salgrade usersemp SYSTEM 5 3#migrating indexes to other table spacessql> ALTER INDEX Scott.pk_empRebuildTablespace system;
Index altered.
Four. Delete Tables:sql> drop table Scott.dept;drop table scott.dept *error at line 1:ora-02449:unique/primary keys In table referenced by foreign keys
#SQL > DROP table scott.deptcascade constraints;
Table dropped.Five. External table:See: "DBA." 34 "External table.
Six. Query the view of the table:Sql> Select Object_id,object_name,object_type from dba_objects where owner= ' SCOTT ';
object_id Object_nam Object_typ------------------------------10241 EMP TABLE 10242 pk_emp INDEX 10243 BONUS table 10244 salgrade table
Sql> Col table_name for a10sql> Col tablespace_name for a10sql> Select Table_name,tablespace_name,blocks,empty_ Blocks from dba_tables where owner= ' SCOTT ';
TABLE_NAME tablespace BLOCKS empty_blocks------------------------------------------BONUS userssalgrade USERS EMP SYSTEM 5 3
Sql> Select segment_name,segment_type,tablespace_name,bytes/1024 k,extents,blocks,initial_extent/1024 init,next _extent/1024 next,pct_increase from dba_segments where owner= ' SCOTT ';
Segment_na segment_ty tablespace k extents BLOCKS &NBS P INIT NEXT pct_increase------------------------------------------------------------------ --------------------------EMP table system &N Bsp 1 8 64pk_emp &NB Sp INDEX system 1 & nbsp 8 64BONUS table users   ; 64 1  8 64SALGRADE TABLE users  64 &nbSp 1 8 64
"ORACLE11G,18" Storage structure: temporary table, manual stripe, table/Index migration table space, delete table, external table