I. Temporary tablestemporary tables are placed in a temporary tablespace and do not generate redo, only undo. You can create indexes, views, and triggers in a staging table, and you can export and import the definition of a temporary table by using the "Export and import (export and import)" or "Data Pump (Pump)". However, the data is not exported even if the rows option is used. There are transaction-based (default) and session-based two types,other sessions do not have access to the. in temporary tables, DML locks are never needed. 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 a second session-based staging table (on commit Preserve rows: data is persisted on commit, but data is emptied when exiting 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 a table to another table space 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 an index to another table spacesql> 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: Temp table, manual stripe, table/Index migration table space, delete table, external table
