"ORACLE11G,18" Storage structure: temporary table, manual stripe, table/Index migration table space, delete table, external table

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 &nbsp ;       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 &nbsp                  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 &nbsp ;       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 &nbsp                  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 &nbsp ;            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