oracle11g如何查找表,oracle11g的表

最新推荐文章于 2022-07-18 18:13:09 发布

赵龙峰

最新推荐文章于 2022-07-18 18:13:09 发布

阅读量379

点赞数

文章标签： oracle11g如何查找表

create cluster scott.cluster1(code_key number); create table scott.student (sno1 number, sname varchar2(10)) cluster scott.cluster1(sno1); create table scott.address (sno2 number, zz varchar2(10)) cluster scott.cluster1(sno2); create index

create cluster scott.cluster1(code_key number);

create table scott.student (sno1 number, sname varchar2(10)) cluster scott.cluster1(sno1);

create table scott.address (sno2 number, zz varchar2(10)) cluster scott.cluster1(sno2);

create index index1 on cluster scott.cluster1; --为簇创建索引

//1.首先创建簇表

SQL> create cluster cluster1(share_col number(10));

Cluster created

SQL> create table tab1(sno number(10),sname varchar2(20)) cluster cluster1(sno);

Table created

SQL> create table tab2(sno number(10),saddr varchar2(20)) cluster cluster1(sno);

Table created

SQL> create index cluster_index on cluster cluster1;

Index created

SQL> select uc.CLUSTER_NAME,uc.TABLESPACE_NAME,uc.CLUSTER_TYPE from user_clusters uc where uc.CLUSTER_NAME='CLUSTER1';

CLUSTER_NAME TABLESPACE_NAME CLUSTER_TYPE

------------------------------ ------------------------------ ------------

CLUSTER1 USERS INDEX

SQL> select ut.TABLE_NAME,ut.TABLESPACE_NAME,ut.CLUSTER_NAME from user_tables ut where ut.TABLE_NAME='TEST1';

TABLE_NAME TABLESPACE_NAME CLUSTER_NAME

------------------------------ ------------------------------ ------------------------------

SQL> select ut.TABLE_NAME,ut.TABLESPACE_NAME,ut.CLUSTER_NAME from user_tables ut where ut.CLUSTER_NAME='CLUSTER1';

TABLE_NAME TABLESPACE_NAME CLUSTER_NAME

------------------------------ ------------------------------ ------------------------------

TAB2 USERS CLUSTER1

TAB1 USERS CLUSTER1

SQL>

先删除表，再删除簇表

四、临时表：

存放临时数据，可以使用临时表；临时表被每个session

单独使用，即：不同session看到的临时表中的数据可能不一

样。

如果在退出session时删除临时表中的数据，可以使用on

commit preserve rows；如果在用户commit或rollback时删

除临时表中的数据，可以使用on commit delete rows；

从v$sort_usage中查看正在使用临时表空间的session信

息和SQL语句的ID号，从v$sort_segment中查看临时表空间中

的段的使用情况。

临时表在临时表空间中保存。

create global temporary table temp_tab1() on commmit preserve rows/delete rows;

五、分区表：

test.jsp?url=http%3A%2F%2Fblog.csdn.net%2Fu011218159%2Farticle%2Fdetails%2F43924305%2Ffile%3A%2FC%3A%2FUsers%2F%25E6%25B0%25B8%25E6%2596%2587%2FAppData%2FLocal%2FTemp%2Fenhtmlclip%2FEN03cea621.png&refer=http%3A%2F%2Fblog.csdn.net%2Fu011218159%2Farticle%2Fdetails%2F43924305

q允许用户将一个表分成多个分区

q用户可以执行查询，只访问表中的特定分区

q将不同的分区存储在不同的磁盘，提高访问性能和安全性

q可以独立地备份和恢复每个分区

分区方式有一下几种：

范围分区：以表中的一个列或一组列的值的范围分区

SQL> create table test_partition(id number(20),account number(20))

2 partition by range(id)(

3 partition part1 values less than(1000),

4 partition part2 values less than(2000),

5 partition part3 values less than(3000));

Table created

SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION';

TABLE_NAME PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

TEST_PARTITION PART1 1000//<1000

TEST_PARTITION PART2 2000>=1000 和<2000

TEST_PARTITION PART3 3000

SQL> insert into test_partition part1 values(1,100);

1 row inserted

SQL> insert into test_partition values(1000,200);

1 row inserted

SQL> insert into test_partition part1 values(2000,300);//标注为会被忽略

1 row inserted

SQL> insert into test_partition values(5000,400);

insert into test_partition values(5000,400)

ORA-14400: 插入的分区关键字未映射到任何分区

SQL> select * from test_partition partition(part1);

ID ACCOUNT

--------------------- ---------------------

1 100

SQL>

SQL> select * from test_partition partition(part2);

ID ACCOUNT

--------------------- ---------------------

1000 200

SQL> alter table test_partition add partition part4 values less than(maxvalue);//无上限

Table altered

SQL> insert into test_partition values(6000,600);

1 row inserted

SQL> select * from test_partition partition(part4);

ID ACCOUNT

--------------------- ---------------------

6000 600

SQL>

2.散列分区

允许用户对不具有逻辑范围的数据进行分区

通过在分区键上执行HASH函数决定存储的分区

将数据平均地分布到不同的分区

SQL> create table test_partition_hash(id number(20),name varchar(20))

2 partition by hash(id)(//散列分区是通过hash算法得到分区来进行的

3 partition part1,partition part2,partition part3);

Table created

SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION_HASH';

TABLE_NAME PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

TEST_PARTITION_HASH PART1 //所以不存在high_value

TEST_PARTITION_HASH PART2

TEST_PARTITION_HASH PART3

SQL> insert into test_partition_hash values(1,'张三');

1 row inserted

SQL> insert into test_partition_hash values(2,'李四');

1 row inserted

SQL> insert into test_partition_hash values(3,'王五');

1 row inserted

SQL>

SQL> select * from test_partition_hash partition(part1);

ID NAME

--------------------- --------------------

SQL> select * from test_partition_hash partition(part2);

ID NAME

--------------------- --------------------

1 张三

3 王五

SQL> select * from test_partition_hash partition(part3);

ID NAME

--------------------- --------------------

2 李四

SQL>

3. 列表分区

允许用户将不相关的数据组织在一起

注意：列表分区是针对于可以列举的类型进行分区的

SQL> create table test_partition_list(id number(20),name varchar2(20),address varchar2(20))

2 partition by list(address)(

3 partition 上北 values('九江'),

4 partition 下南 values('赣州','鹰潭'),

5 partition 左西 values('抚州','新余'),

6 partition 右东 values('景德镇'));

Table created

SQL> insert into test_partition_list values(1,'吴xx','九江');

1 row inserted

SQL> insert into test_partition_list values(2,'陈xx','赣州');

1 row inserted

SQL> insert into test_partition_list values(3,'邹xx','抚州');

1 row inserted

SQL> insert into test_partition_list values(4,'刘xx','景德镇');

1 row inserted

SQL> select * from test_partition_list partition(上北);

ID NAME ADDRESS

--------------------- -------------------- --------------------

1 吴xx 九江

SQL> select * from test_partition_list partition(下南);

ID NAME ADDRESS

--------------------- -------------------- --------------------

2 陈xx 赣州

SQL>

4. 复合分区

范围分区与散列分区或列表分区的组合//只有这两种组合而且顺序不能颠倒

SQL> create table test_partition_compass(

2 id number(20),name varchar2(20))

3 partition by range(id)//主分区

4 subpartition by hash(name)//子分区

5 subpartitions 4(//每个主分区包括4个子分区

6 partition part1 values less than(100),//第一个分区

7 partition part2 values less than(200),

8 partition part3 values less than(maxvalue));

Table created

通过EM查看表的分区信息如下：

A browser with Javascript enabled is required for this page to operate properly.

Partitions

test.jsp?url=http%3A%2F%2Fblog.csdn.net%2Fu011218159%2Farticle%2Fdetails%2F43924305%2Ffile%3A%2FC%3A%2FUsers%2F%25E6%25B0%25B8%25E6%2596%2587%2FAppData%2FLocal%2FTemp%2Fenhtmlclip%2FImage.gif&refer=http%3A%2F%2Fblog.csdn.net%2Fu011218159%2Farticle%2Fdetails%2F43924305

Partitioning Description

Partitioning Method

Range-Hash

Partitioning Columns

Number of Partitions

Subpartitioning Columns

NAME

Number of Subpartitions

Partition Definitions

test.jsp?url=http%3A%2F%2Fblog.csdn.net%2Fu011218159%2Farticle%2Fdetails%2F43924305%2Ffile%3A%2FC%3A%2FUsers%2F%25E6%25B0%25B8%25E6%2596%2587%2FAppData%2FLocal%2FTemp%2Fenhtmlclip%2FImage%281%29.gif&refer=http%3A%2F%2Fblog.csdn.net%2Fu011218159%2Farticle%2Fdetails%2F43924305

1-3 of 3

test.jsp?url=http%3A%2F%2Fblog.csdn.net%2Fu011218159%2Farticle%2Fdetails%2F43924305%2Ffile%3A%2FC%3A%2FUsers%2F%25E6%25B0%25B8%25E6%2596%2587%2FAppData%2FLocal%2FTemp%2Fenhtmlclip%2FImage%282%29.gif&refer=http%3A%2F%2Fblog.csdn.net%2Fu011218159%2Farticle%2Fdetails%2F43924305

Partition Name

High Value - ID (NUMBER)

Subpartition Default Tablespace

Subpartitions

PART1

100

USERS

PART2

200

USERS

PART3

MAXVALUE

USERS

Subpartition Definitions

1-12 of 12

Partition Name

Subpartition Name

Tablespace

PART1

SYS_SUBP21

USERS

SYS_SUBP22

USERS

SYS_SUBP23

USERS

SYS_SUBP24

USERS

PART2

SYS_SUBP25

USERS

SYS_SUBP26

USERS

.......

........

5.11g新增的表分区的类型 –引用分区

引用分区：基于由外键引用的父表的分区的方

法，它依赖已有的父表子表的关系，子表通过外键

关联到父表，进而继承了父表的分区方式而不需自

己创建，子表还继承了父表的维护操作。

1，主表是范围分区，子表是引用分区

2，主表是列表分区，子表是引用分区

3，主表是散列分区，子表是引用分区

//创建范围分区

SQL>

SQL> create table test_partition_student(

2 id number(10),name varchar2(20),grade varchar2(20),constraints pk_student primary key(id))

3 partition by range(id) (

4 partition part1 values less than(100),

5 partition part2 values less than(200),

6 partition part3 values less than(maxvalue));

Table created

SQL> create table test_partition_score(

2 id number(10) primary key ,sid number(10),goal number(10),constraints fk_student_score foreign key(sid)

3 references test_partition_student(id))

4 partition by reference(fk_student_score);

create table test_partition_score(

id number(10) primary key ,sid number(10),goal number(10),constraints fk_student_score foreign key(sid)

references test_partition_student(id))

partition by reference(fk_student_score)

ORA-14652: 不支持引用分区外键,关联的外键必须是非空

SQL>

SQL> create table test_partition_score(

2 id number(10) primary key ,sid number(10) not null,goal number(10),constraints fk_student_score foreign key(sid)

3 references test_partition_student(id))

4 partition by reference(fk_student_score);

Table created

SQL>

SQL> select upt.table_name,upt.partition_name,upt.high_value from user_tab_partitions upt where upt.table_name in(upper('test_partition_score'),upper('test_partition_student'));

TABLE_NAME PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

TEST_PARTITION_STUDENT PART1 100

TEST_PARTITION_STUDENT PART2 200

TEST_PARTITION_STUDENT PART3 MAXVALUE

TEST_PARTITION_SCORE PART1

TEST_PARTITION_SCORE PART2

TEST_PARTITION_SCORE PART3

6 rows selected

分区名称相同

SQL> insert into TEST_PARTITION_STUDENT values(1,'张三','二年级');

1 row inserted

SQL> insert into TEST_PARTITION_STUDENT values(111,'李四','三年级');

1 row inserted

SQL> insert into TEST_PARTITION_SCORE values(1,1,100);

1 row inserted

SQL> insert into TEST_PARTITION_SCORE values(2,111,99);

1 row inserted

SQL> select * from TEST_PARTITION_STUDENT partition(part1);

ID NAME GRADE

----------- -------------------- --------------------

1 张三二年级

SQL> select * from TEST_PARTITION_SCORE partition(part1);

ID SID GOAL

----------- ----------- -----------

1 1 100

SQL>

6.11g新增的表分区的类型 –间隔分区

间隔分区：可以完全自动地根据间隔阈值创建范

围分区，它是范围分区的扩展。

在数据仓库中有广泛的应用。

SQL> select * from user_part_tables;//存放的是分区表的情况

SQL> select * from user_tab_partitions;/存放的是表分区的情况

SQL> create table test_partition_interval(

2 id number(10),name varchar2(20),num number(20),_date date)

3 partition by range(_date)

4 interval(NUMTOYMINTERVAL(1,'MONTH'))(

5 partition part1 values less than(to_date(20140101,'yyyymmdd')));

create table test_partition_interval(

id number(10),name varchar2(20),num number(20),_date date)

partition by range(_date)

interval(NUMTOYMINTERVAL(1,'MONTH'))(

partition part1 values less than(to_date(20140101,'yyyymmdd')))

ORA-00911: 无效字符//不能使用_开头的属性名称

SQL>

SQL> create table test_partition_interval(

2 id number(10),name varchar2(20),num number(20),s_date date)

3 partition by range(s_date)

4 interval(NUMTOYMINTERVAL(1,'MONTH'))(//按照一个月来间隔增长的

5 partition part1 values less than(to_date(20140101,'yyyymmdd')));//初始的月份

Table created

SQL> select sysdate from dual;

SYSDATE

-----------

13-1月-15 1:

SQL> INSERT INTO test_partition_interval VALUES(1,'上衣',20,'01-1月-2014');

1 row inserted

SQL>

SQL> INSERT INTO test_partition_interval VALUES(1,'上衣',20,'01-2月-2014');

1 row inserted

SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION_INTERVAL';

TABLE_NAME PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

TEST_PARTITION_INTERVAL PART1 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TEST_PARTITION_INTERVAL SYS_P41 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TEST_PARTITION_INTERVAL SYS_P42 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> INSERT INTO test_partition_interval VALUES(1,'上衣',20,'01-10月-2014');

1 row inserted

SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION_INTERVAL';

TABLE_NAME PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

TEST_PARTITION_INTERVAL PART1 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TEST_PARTITION_INTERVAL SYS_P41 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TEST_PARTITION_INTERVAL SYS_P42 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TEST_PARTITION_INTERVAL SYS_P43 TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL>

7. 11g新增的表分区的类型--基于虚拟列的分区

基于虚拟列的分区：把分区建立在某个虚拟列

上，即建立在函数或表达式的计算结果上，来完成

某种任务。

SQL>

SQL> create table test_partition_virtual(

2 id number(10),name varchar2(20),num number(20),price number(8,2),total_price as price*num virtual)

3 partition by range(total_price)(

4 partition part1 values less than(1000),

5 partition part2 values less than(2000),

6 partition part3 values less than(maxvalue));

ORA-02000: 缺失 ( 关键字

SQL>

SQL> create table test_partition_virtual(

2 id number(10),name varchar2(20),num number(20),price number(8,2),total_price as (price*num) virtual)

3 partition by range(total_price)(

4 partition part1 values less than(1000),

5 partition part2 values less than(2000),

6 partition part3 values less than(maxvalue));

Table created

SQL> insert into test_partition_virtual(id,name,num,price) values(1,'上衣',10,100);

1 row inserted

SQL> insert into test_partition_virtual(id,name,num,price) values(1,'上衣',20,100);

1 row inserted

SQL> select * from test_partition_virtual partition(part1);

ID NAME NUM PRICE TOTAL_PRICE

----------- -------------------- --------------------- ---------- -----------

SQL> select * from test_partition_virtual partition(part2);

ID NAME NUM PRICE TOTAL_PRICE

----------- -------------------- --------------------- ---------- -----------

1 上衣 10 100.00 1000

SQL>

8. 11g新增的表分区的类型—系统分区

系统分区：不指定分区列，由ORACLE来完成分

区的控制和管理，它没有了范围分区或列表分区的

界限。

分区维护操作

q分区维护操作修改已分区表的分区。

q分区维护的类型：

q计划事件－定期删除最旧的分区

q非计划事件－解决应用程序或系统问题

q分区维护操作有：

q添加分区

q删除分区

q截断分区

q合并分区

q拆分分区

SQL> alter table test_partition add partition values less than(6000);

alter table test_partition add partition values less than(6000)

ORA-14074: 分区界限必须调整为高于最后一个分区界限

分区因为是添加在最后以后分区上的

//删除分区

SQL> alter table test_partition drop partition part4;

Table altered

//增加分区

SQL> alter table test_partition add partition part4 values less than(7000);

Table altered

//拆分分区

SQL> alter table test_partition merge partitions part1,part2 into partition part2;

Table altered

//合并分区

SQL> alter table test_partition split partition part2 at(1000) into (partition part1 ,partition part2);

Table altered

//截断分区

SQL> alter table test_partition truncate partition part3;

Table truncated

SQL>

本文原创发布php中文网，转载请注明出处，感谢您的尊重！

赵龙峰

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle11g如何查找表,oracle11g的表

create cluster scott.cluster1(code_key number); create table scott.student (sno1 number, sname varchar2(10)) cluster scott.cluster1(sno1); create table scott.address (sno2 number, zz varchar2(10)) clu...
复制链接

扫一扫