【oracle11g,18】存储结构:临时表,手工条带化,表/索引迁移表空间,删除表,外部表

一. 临时表
临时表放在临时表空间,不生成redo,只有undo。
在临时表中可以创建索引、视图及触发器,还可以使用“Export and Import(导出和导入)”或“Data Pump(数据泵)”导出和导入临时表的定义。但是,即使使用了ROWS 选项,也不会导出数据。
有基于事务(默认)和基于session两种,其他的会话不能访问到
在临时表中,DML锁永远不需要。
1.创建默认的(基于事务的)临时表:(on commit delete rows:提交时清空数据)

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;

14 rows created.

SQL> select * from temp_emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      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                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.
#提交
SQL> commit;

Commit complete.

SQL> select * from temp_emp1;

no rows selected

2.创建第二种基于session的临时表(on commit preserve rows:提交时保留数据,但在退出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;

14 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL>  select * from temp_emp2;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      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                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

 

二.手工条带化:
#分析表
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       USERS
EMP        USERS               5            3
BONUS      USERS
SALGRADE   USERS

SQL> col name for a50
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
         2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf
         3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
         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 is not a member of tablespace
USERS

#分配数据块给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       USERS
EMP        USERS               5         2563
BONUS      USERS
SALGRADE   USERS
#收回空块
SQL> 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       USERS
EMP        USERS               5            3
BONUS      USERS
SALGRADE   USERS


三.把表/索引迁移到其他表空间:

#把表迁移到其他表空间
SQL> alter table scott.emp move tablespace 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       USERS
BONUS      USERS
SALGRADE   USERS
EMP        SYSTEM              5            3
#把索引迁移到其他表空间
SQL> alter index scott.pk_emp rebuild tablespace system;

Index altered.


四.删除表:
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.dept cascade constraints;

Table dropped.
五.外部表:
详见: 【dba,34】外部表。

六.查询表的视图:
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 a10
SQL> col tablespace_name for a10
SQL> select table_name,tablespace_name,blocks,empty_blocks from dba_tables where owner=‘SCOTT‘;

TABLE_NAME TABLESPACE     BLOCKS EMPTY_BLOCKS
---------- ---------- ---------- ------------
BONUS      USERS
SALGRADE   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       INIT       NEXT PCT_INCREASE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
EMP        TABLE      SYSTEM             64          1          8         64
PK_EMP     INDEX      SYSTEM             64          1          8         64
BONUS      TABLE      USERS              64          1          8         64
SALGRADE   TABLE      USERS              64          1          8         64





郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。