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

时间：2022-03-14 00:02

一. 临时表

临时表放在临时表空间，不生成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