Oracle 之创建表与修改表详解

来源：互联网发布：腾讯代理吃鸡会优化吗编辑：程序博客网时间：2024/06/01 17:30

Oracle之创建表与修改表详解

Oracle中表有、标准表、索引表、簇表、分区表。现在主要介绍标准表的创建和修改内容。其他表的创建以及使用会放到其他章节中。

第一部分创建表：

一）创建表

Create table [schema.]table_name (

Column_name date_type [default expression]constraint;

Eg:

create table scott.mybook(

bookid number(10) not null quiue,

bookname varchar2(40),

author varchar2(40),

press varchar2(40),

bookprice number(4,2),

presstime date,

constraint book1_pk primary key(bookid)

)

二）查看表：

通过describe查看表的概述信息，如果查看表中每个列的信息可以通过查看user_tab_columns这个视图进行检索相关内容。

Eg：

SQL> describe user_tab_columns;

Name Type Nullable Default Comments

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

TABLE_NAME VARCHAR2(30) Table, view or cluster name

COLUMN_NAME VARCHAR2(30) Column name

DATA_TYPE VARCHAR2(106)Y Datatype of thecolumn

DATA_TYPE_MOD VARCHAR2(3) Y Datatype modifier of thecolumn

DATA_TYPE_OWNER VARCHAR2(30) Y Owner of the datatype of thecolumn

DATA_LENGTH NUMBER Length of the column inbytes

DATA_PRECISION NUMBER Y Length: decimal digits (NUMBER)or binary digits (FLOAT)

DATA_SCALE NUMBER Y Digits to right of decimal point in anumber

NULLABLE VARCHAR2(1) Y Does column allow NULLvalues?

COLUMN_ID NUMBER Y Sequence number of the columnas created

DEFAULT_LENGTH NUMBER Y Length of default value for thecolumn

DATA_DEFAULT LONG Y Default value for thecolumn

NUM_DISTINCT NUMBER Y The number of distinct valuesin the column

LOW_VALUE RAW(32) Y The low value in thecolumn

HIGH_VALUE RAW(32) Y The high value in thecolumn

DENSITY NUMBER Y The density of the column

NUM_NULLS NUMBER Y The number of nulls in thecolumn

NUM_BUCKETS NUMBER Y The number of buckets inhistogram for the column

LAST_ANALYZED DATE Y The date of the most recenttime this column was analyzed

SAMPLE_SIZE NUMBER Y The sample size used inanalyzing this column

CHARACTER_SET_NAME VARCHAR2(44) Y Character set name

CHAR_COL_DECL_LENGTH NUMBER Y Declarationlength of character type column

GLOBAL_STATS VARCHAR2(3) Y Are the statistics calculatedwithout merging underlying partitions?

USER_STATS VARCHAR2(3) Y Were the statistics entereddirectly by the user?

AVG_COL_LEN NUMBER Y The average length of thecolumn in bytes

CHAR_LENGTH NUMBER Y The maximum length of thecolumn in characters

CHAR_USED VARCHAR2(1) Y C is maximum length given incharacters, B if in bytes

V80_FMT_IMAGE VARCHAR2(3) Y Is column data in 8.0 imageformat?

DATA_UPGRADED VARCHAR2(3) Y Has column data been upgradedto the latest type version format?

HISTOGRAM VARCHAR2(15) Y

SQL>

三）创建表指定表空间；

Eg：

create table books_01(

2 b_id number not null,

3 b_name varchar2(40) not null,

4 constraint unique_key_02 unique(b_id)

5 ) tablespace users,temporary temp;

) tablespace users；

四）创建表指定存储参数

Storage (Initial [nk,nm] next [nk,nm]minextents n)

Initial:表示创建表的时候分配第一个盘区的大小，next表示如需要新盘区，哪分配下一个盘区的大小，minextents表示至少有几个盘区。

注意：next和minextents参数在表空间是本地管理方式的时候，参数不起作用。

如果在创建表空间的时候使用手段管理方式manual并且制定了uniform size那么storage参数不起作用了，因为这个每个盘区的大小都是统一的。

Eg：

SCOTT@orcl#r

1 create table books_01(

2 b_id number not null,

3 b_name varchar2(40) not null,

4 constraint unique_key_02unique(b_id)

5*) storage (initial 20K)

表已创建。

SCOTT@orcl#R

1*select INITIAL_EXTENT,table_name from user_tables where table_name='BOOKS_01'

INITIAL_EXTENT TABLE_NAME

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

24576 BOOKS_01

SCOTT@orcl#

Notes：可以看到我虽然指定了盘区为20K，但是真正是24k，这是因为我db_block_size为8k，所以无论你指定多少都必须是8的整数倍。

SQL> SHOW PARAMETER BLOCK_SIZE

NAME TYPE VALUE

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

db_block_size integer 8192

SQL>

五）设置数据块管理参数：

数据块结构：

数据块头部

表目录

行目录

空闲空间

存储数据空间

数据块头部：包含本数据的一些信息，如scn信息，事务访问信息等等

表目录：包含该数据块所属表的相关信息

自由空间：表示该数据块可以的空闲空间

存储数据空间：表示该数据块已经使用的空间大小：

在设置表的数据块相关属性的时候需要注意四个参数：pctfree,pctused,initrans,maxtrans.

PCT_FREE NUMBER Y Minimum percentage of freespace in a block

PCT_USED NUMBER Y Minimum percentage of usedspace in a block

注意：当段的管理方式为：segment space manager manual手动管理的时候，那么可以设置pctfree和pctused来管理数据块的空间使用比例。

Pctfree:该参数设置数据块可以保持的空闲空间比率。如设置为20，那么当该数据块的空闲空间达到20%的时候，就不允许进行insert操作了。在进行update操作，并会产生数据条目的时候。可以增大该参数的设置，但是过大的设置pctfree，就不能够存储很大的数据了。推荐设置pctfree为20.pctused为40

Pctused：用于设置数据块可以使用的临界点.如，当该数据块中的数据使用设置为pctused 30，那么当该数据块使用低于30%的时候，该数据块还可以被使用，当达到30%的时候，本数据块就不能被使用了。当尽心delete和insert数据比较多的时候，可以考虑将该数据块的pctused设置为稍微大点。但是pctfree+prcused<=100.推荐设置为pctfree为5，pctused为60.

Eg：

SCOTT@orcl#r

1 create table books_01(

2 b_id number not null,

3 b_name varchar2(40) not null,

4 constraint unique_key_02 unique(b_id)

5* ) pctfree 5pctused 60

表已创建。

Initrans和maxtrans

Initrans和maxtrans参数用于设置指定并发访问数据块的事务数量，initrans用于设置数据块头部可以存放的数据数量；maxtrans参数用于设置并发访问数据块所允许的事务总数。

Initrans和maxtrans对数据块的管理如下：

在表创建的时候，oracle为为每个数据块的头部分配空间，该空间大小可以存储initrans参数指定数量的事务记录。

当一个事务访问数据块的时候，该事务将会在数据块的头部保存一个标记，表示该事务正在使用这个数据块，当该事务结束的时候，对应的标记将被删除。

当数据块的头部空间已经存储了initrans参数指定的数量的事务后，如果还有其他的事务访问该数据块，那么oracle将会在该数据块的空闲空间中为事务分配空间。

Eg：

SCOTT@orcl#r

1 create table books_01(

2 b_id number not null,

3 b_name varchar2(40) not null,

4 constraint unique_key_02 unique(b_id)

5* ) pctfree20 pctused 40 initrans 5 maxtrans 200

表已创建。

SCOTT@orcl#

SCOTT@orcl#selecttable_name,pct_free,pct_used,ini_trans,max_trans from user_tables wheretable_name=

2 'BOOKS_01';

TABLE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS

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

BOOKS_01 20 5 255

SCOTT@orcl#

六)使对表的操作不记录到redo可以再创建表的时候使用nologging，

Eg：

SCOTT@orcl#r

1 create table books_01(

2 b_id number not null,

3 b_name varchar2(40) not null,

4 constraint unique_key_02 unique(b_id)

5* ) nologging

表已创建。

SCOTT@orcl#

七）指定cache

当sql操作数据的时候，如select查看数据文件中的数据，那么先从缓存中找相应内容，如果没有找到，那么会把数据文件中的信息存入缓冲中。当进行全表扫描的时候，那么oracle会根据lru算法进行缓存内容的变更信息，这个时候如果不希望我一些内容从缓存中丢掉，那么可以使用cache，这个时候在利用lru算法对缓存块进行换入、换出调度时，不会将将属于这个表的数据块换出缓存，以提高对表的查询速度。

Eg：

SCOTT@orcl#r

1 create table books_01(

2 b_id number not null,

3 b_name varchar2(40) not null,

4 constraint unique_key_02unique(b_id)

5*) cache

表已创建。

SCOTT@orcl#

第二部分修改表：

增加或删除列，

更新列

对表进行重命令

修改表的存储参数

将表移动到另一个表空间

删除表；

这部分内容，我直接进行试验操作：

Eg：

SCOTT@orcl#

SCOTT@orcl#DESC mybook5;

名称是否为空?类型

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

BOOKID NOT NULL NUMBER(10)

BOOKNAME VARCHAR2(40)

AUTHOR VARCHAR2(40)

BOOKPRICE NUMBER(4,2)

PRESSTIME DATE

SCOTT@orcl#alter table mybook5 add book_newvarchar2(20) not null;

表已更改。

SCOTT@orcl#desc mybook5;

名称是否为空?类型

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

BOOKID NOT NULL NUMBER(10)

BOOKNAME VARCHAR2(40)

AUTHOR VARCHAR2(40)

BOOKPRICE NUMBER(4,2)

PRESSTIME DATE

BOOK_NEW NOT NULL VARCHAR2(20)

SCOTT@orcl#alter table mybook5 drop column book_new;

表已更改。

SCOTT@orcl#desc mybook5;

名称是否为空?类型

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

BOOKID NOT NULL NUMBER(10)

BOOKNAME VARCHAR2(40)

AUTHOR VARCHAR2(40)

BOOKPRICE NUMBER(4,2)

PRESSTIME DATE

SCOTT@orcl#alter table mybook5 drop (bookname,author);

表已更改。

SCOTT@orcl#desc mybook5;

名称是否为空?类型

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

BOOKID NOT NULL NUMBER(10)

BOOKPRICE NUMBER(4,2)

PRESSTIME DATE

SCOTT@orcl#alter table mybook5 set unused (bookprice);

表已更改。

SCOTT@orcl#desc mybook5;

名称是否为空?类型

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

BOOKID NOTNULL NUMBER(10)

PRESSTIME DATE

SCOTT@orcl#select * from user_unused_col_tabs;

TABLE_NAME COUNT

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

MYBOOK4 1

MYBOOK5 1

SCOTT@orcl#alter table mybook5 drop unused column;

表已更改。

SCOTT@orcl#l

1* alter tablemybook5 drop unused column

SCOTT@orcl#select * from user_unused_col_tabs;

TABLE_NAME COUNT

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

MYBOOK4 1

SCOTT@orcl#desc mybook4;

名称是否为空?类型

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

BOOKID NOT NULL NUMBER(10)

PRESS VARCHAR2(40)

PRESSTIME NOT NULL DATE

BOOKNAME NOT NULL VARCHAR2(40)

AUTHOR VARCHAR2(20)

SCOTT@orcl#alter table mybook4 rename column press topre;

表已更改。

SCOTT@orcl#alter table mybook4 modify pre not null;

表已更改。

SCOTT@orcl#alter table mybook4 modify prenumber(10,2);

表已更改。

SCOTT@orcl#alter table mybook4 modify pre number(4,1);

表已更改。

SCOTT@orcl#alter table mybook4 modify pre default 0.0;

表已更改。

SCOTT@orcl#alter table mybook4 rename to book4;

表已更改。

SCOTT@orcl#select table_name,tablespace_name fromuser_tables;

TABLE_NAME TABLESPACE_NAME

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

DEPT USERS

EMP USERS

BONUS USERS

SALGRADE USERS

NEWEMP USERS

QUEST_SOO_EVENT_CATEGORIES USERS

QUEST_SOO_BUFFER_BUSY USERS

QUEST_SOO_PLAN_TABLE USERS

QUEST_SOO_LOCK_TREE USERS

QUEST_SOO_VERSION USERS

QUEST_SOO_SB_EVENT USERS

TABLE_NAME TABLESPACE_NAME

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

QUEST_SOO_SB_BUFFER_BUSY USERS

QUEST_SOO_SB_IO_STAT USERS

QUEST_SOO_PARSE_TIME_TRACK USERS

QUEST_SOO_SCHEMA_VERSIONS USERS

QUEST_SOO_AT_APPNAME USERS

QUEST_SOO_AT_SQL_EXEC_ERROR USERS

QUEST_SOO_AT_PARSE_WAITS USERS

QUEST_SOO_AT_EXECUTION_PLAN USERS

QUEST_SOO_AT_PARSE_CURSOR USERS

QUEST_SOO_AT_PARSE_ERROR USERS

QUEST_SOO_AT_SESSION_ID USERS

TABLE_NAME TABLESPACE_NAME

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

QUEST_SOO_AT_SQL_BINDS USERS

QUEST_SOO_AT_SQL_EXECUTIONS USERS

QUEST_SOO_AT_SQL_FETCH USERS

QUEST_SOO_AT_SQL_STATEMENT USERS

QUEST_SOO_AT_SQL_STMT_PIECES USERS

QUEST_SOO_AT_SQL_WAITS USERS

QUEST_SOO_AT_TRACE_FILE USERS

QUEST_SOO_AT_WAIT_NAMES USERS

QUEST_SOO_AT_OPERATIONS USERS

MYBOOK USERS

BOOK4 USERS

TABLE_NAME TABLESPACE_NAME

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

MYBOOK5 USERS

MYBOOK5_1 USERS

EMP_TEST USERS

BOOKS_01 USERS

已选择37行。

SCOTT@orcl#alter table book4 move tablespace system;

表已更改。

SCOTT@orcl#select table_name,tablespace_name fromuser_tables where table_name='BOOK4';

TABLE_NAME TABLESPACE_NAME

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

BOOK4 SYSTEM

SCOTT@orcl#alter table book4 move tablespace users;

表已更改。

SCOTT@orcl#alter table book4 pctfree 5 pctused 60;

表已更改。

SCOTT@orcl#drop table book4 cascade constraints;

表已删除。

SCOTT@orcl#show recycle

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

BOOK4 BIN$2ZVssaoayV/gQKjABIkd1g==$0 TABLE 2013-04-05:13:00:49

BOOKS_01 BIN$2ZVssaoTyV/gQKjABIkd1g==$0 TABLE 2013-04-05:12:44:32

BOOKS_01 BIN$2ZVssaoOyV/gQKjABIkd1g==$0 TABLE 2013-04-05:12:34:47

BOOKS_01 BIN$2ZVssaoJyV/gQKjABIkd1g==$0 TABLE 2013-04-05:12:21:41

Oracle 之 创建表与修改表详解

Oracle 之创建表与修改表详解