Oracle之创建表与修改表详解
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#
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
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。