oracle 9段分别取数,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'