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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.