莆田万科八期最新动态:Oracle学习笔记(9)管理数据表

管理数据表

定义存储数据的各种方法

描述Oracle数据类型
区分extended ROWID 和 restricted ROWID
描述行的结构
建立常规表和临时表
通过表来管理存储结构
重组、清空和删除一张表
删除一张表中的某个列

1、存储用户数据：常规表、分区表、索引表、Cluster群集表（共享数据）
sql>conn sys/admin1@fox as sysdba
sql>drop table test1;
sql>create table test1(id int)tablespace users;

2、Oracle支持的数据类型：用户定义的数据类型，系统集成的数据类型
系统集成的数据类型：Scalar标量(字符、数字、日期、时间date\timestamp、元RAW(N)、BLOB,CLOB/NCLOB,BFILE、长元LONG/LONG RAW，行号ROWID,UROWIDD) 、Collection集合（varray动态数组Record/table）、Relationship 参照（REF游标）

其中，char(n),nchar(n)/varchar2(n),nvarchar2(n) 其中nchar(n)和nvarchar2(n)用来支持unicode。char(n)最大2000字符，varchar2(n)最大4000字符
行ROWID格式：扩展行id格式、Restricted行id格式
存储表数据时，每一行肯定有标识，即行rowid。
ROWID length长度18，基本64位字符（10bytes,80bits）:OBJECT# 32bits,6 rfile# 10bits,3 block#

sql>create table test2 (lname varchar2(4001))tablespace users; //varchar2不支持4001.
sql>create table salary(id int,salary number(5,2))tablespace users; //创建表空间,5位数字2位小数
sql>drop table salary;
sql
sql>create table test2(itime date(9))tablespace users;
sql>drop table test2;

sql>desc dbms_rowid   //行id函数

    ROWID_BLOCK_NUMBER  块号
    ROWID_CREATE        创建行id
    ROWID_INFO          行id信息
    ROWID_OBJECT        行对象
    ROWID_RELATIVE_FNO     相对文件号
    ROWID_ROW_NUMBER    行数
    ROWID_TO_ABSOLUTE_FNO  绝对文件号
    ROWID_TO_EXTENDED   行扩展
    ROWID_TO_RESTRICTED    行id约束
    ROWID_TYPE              行id类型
    ROWID_VERIFY           行

sql> select rowid,dbms_rowid.rowid_to_restricted(rowid)from kong.authors;
sql> select rowid,dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid)

3、创建表：
CREATE TABLE hr.employees(employee_id NUMBER(6),first_name VARCHAR2(20),last_name VARCHAR2(25)
email VARCHAR2(25) phone_number VARCHAR2(20) hire_date DATE DEFAULT SYSDATE job_id VARCHAR2(10)
salary NUMBER(8,2) commission_pct NUMBER(2,2) manager_id NUMBER(6) department_id NUMBER(4));

sql>create table test2(id int, lname varchar2(20) not null,fname varchar2(20) constraint ck_1 check(fname lie 'k%'),empdate date default sysdate) tablespace users;

4、创建临时表：
CREATE GLOBAL TEMPORARY TABLE hr.employees_temp AS SELECT * FROM hr.employees;

表保留数据仅仅是为了事务或会话的持久性；DML语句在数据中无法识别；DMLs不生成redo logs；可以在临时表上创建索引,视图和触发器
create global temporary table xxx
on commit delete rows    //删除行（事务）
on commit preserve rows //保留行（会话）

sql>create global temporary table test2 on commit delete rows
    as select * from kong.authors;
sql>select * from test2;  //用临时表来保留数据

sql>delete test2 where au_id like '1%';  //删除临时表数据，删除1条
另一个窗口：
sql>conn sys/admin1@fox as sysdba
sql>delete test2 where au_id like '2%'; //删除临时表数据，删除0条

放在本地管理的表空间中来避免碎片重整

sql>create table test3(id int) tablespace users
    storage(initial 100k next 100k pctincrease 0 minextents 1 maxextents 10)  //避免使用pctincrease，建议使用标准的分区
    pctfree 10 pctused 40 /
 
5、改变存储参数：
ALTER TABLE hr.employees PCTFREE 30 PCTUSED 50 STORAGE(NEXT 500K MINEXTENTS 2 MAXEXTENTS 100);

sql>alter table test3 pctfree 20 pctused 50 /
 
6、手工分配分区：
ALTER TABLE hr.employees ALLOCATE EXTENT(SIZE 500K DATAFILE '/DISK3/DATA01.DBF'); // DATAFILE指定的空间一定属于这个表所在的表空间。
sql>alter table kong.authors allocate extent(1m datafile 'd:\oracle\oradata\system01.dbf');
allocate extent(size 1m datafile 'd:\oracle\oradata\fox\system01.dbf') //system01.dbf不是表空间USERS的成员。
sql>c /system01/users01/
sql>run   //手工强制分配分区给authors,ORACLE自动分配的分区不会受到影响（如分配的顺序和分区的大小）。

7、高水印HWM，无用空间释放
sql>alter table kong.authors
    deallocate unused;   //释放表kong.authors中没有使用的空间
sql>a keep 0  //alter table kong.authors deallocate unused keep 0
sql> c /0/2m/
   2*  deallocate unused keep 2m
sql>run  //段只包含14块在高水印之上的未使用空间

sql>c /2m/8k/
   2* deallocate unsed keep 8k  //释放没用过的空间只保留8k

8、Nonpartitioned Table Reorganization:
   ALTER TABLE hr.employees MOVE TABLESAPCE data1;  //对一个表空间的表物理移动到另一个表空间，同时可以重新组织它的分区，但索引、约束和触发器等都可以保留。如此实现负载的均衡。
sql>alter table kong.authors
    move tablespace index;
sql>create index testindex1 on kong.authors(au_lname) tablespace users;  //创建索引
sql>alter table kong.authors move tablespace users; //引移动表从一个表空间到另一个表空间
sql>select * from kong.authors where au_lanme like 'B%'; //索引SYS.TESTINDEX1处于不可用状态。
sql>alter index testindex1 rebuild; //移动以后，索引需要进行重建

9、清空表 Truncating a Table
清空表的同时，会释放它的空间，也把索引给清空掉了。
sql>truncate table kong.authors;

10.删除表 Dropping a Table
数据字典、索引信息、用户权限等都被删除，
DROP TABLE hr.department CASCADE CONSTRAINTS;  //有外键的也要将外键删除
sql>drop table kong.authors cascade constraint;

11、删除表中的一列(栏位)：
ALTER TABLE hr.employees DROP COLUMN comments CASCADE CONSTRAINTS CHECKPOINT 1000;
删除从每行每个在数据块中的自由空间中的列长度和数据。
如果表中已经有了数据，而且数据量很大，此时删除列中的数据需要很长的时间。变通方式：先将栏位设置为非可用。等栏位闲下来时再删除
设置column as unused
ALTER TABLE hr.employees SET UNUSED COLUMN comments CASCADE CONSTRAINTS;
Drop unsed 栏位：
ALTER TABLE hr.employees DROP UNUSED COLUMNS CHECKPOINT 1000;
继续drop栏位操作：
ALTER TABLE hr.employees DROP COLUMNS CONTINUE CHECKPOINT 1000;  //中间出错，系统中止删除时使用

sql>alter table kong.sales drop column qty;  //删除栏位
sql>alter table kong.sales set unused column  stor_id; //如果数据量大的表，使用变通方式：置无用的栏位。
sql>select * from kong.sales  //此时栏位stor_id已经看不到了，就像删除了一样。

sql>alter table kong.sales drop unused columns; //将无用的栏位从表中删除

12、获取表信息：
DBA_TABLES  数据表、 DBA_OBJECTS 数据对象

sql>select * from dba_tables where owner='KONG';
sql>SET WRAP OFF
sql>run
sql>select * from DBA_OBJECTS where owner='KONG'