oracle创建表和表空间深入学习

青旅半醒 2022-05-19 04:45 285阅读 0赞

# [Oracle创建表空间和表][Oracle] #

创建表空间和表
    ORACLE物理上是由磁盘上的以下几种文件:数据文件和控制文件和LOGFILE构成的
    oracle中的表就是一张存储数据的表。表空间是逻辑上的划分。方便管理的。
    数据表空间 (Tablespace)         
    存放数据总是需要空间， Oracle把一个数据库按功能划分若干空间来保存数据。当然数据存放在磁盘最终是以文件形式，所以一盘一个数据表空间包含一个以上的物理文件
    数据表         
    在仓库，我们可能有多间房子，每个房子又有多个货架，每架又有多层。 我们在数据库中存放数据，最终是数据表的单元来存储与管理的。
    数据文件         
    以上几个概念都是逻辑上的， 而数据文件则是物理上的。就是说，数据文件是真正“看得着的东西”，它在磁盘上以一个真实的文件体现

1、创建表空间：
    格式: create tablespace 表间名 datafile '数据文件名' size 表空间大小
                    create tablespace data_test datafile 'e:\oracle\oradata\test\data_1.dbf' size 2000M;
                    create tablespace idx_test datafile 'e:\oracle\oradata\test\idx_1.dbf' size 2000M;
                    (*数据文件名 包含全路径, 表空间大小 2000M 表是 2000兆) 
    2、建好tablespace, 就可以建用户了
              格式: create user 用户名 identified by 密码 default tablespace 表空间表;
                    create user study identified by study default tablespace data_test;
                    (*我们创建一个用户名为 study,密码为 study, 缺少表空间为 data_test -这是在第二步建好的.)
                    (*缺省表空间表示 用户study今后的数据如果没有专门指出，其数据就保存在 data_test中, 也就是保存在对应的物理文件 e:\oracle\oradata\test\data_1.dbf中)
    
    创建用户并指定表空间
    CREATE USER cici IDENTIFIED BY cici PROFILE DEFAULT DEFAULT TABLESPACE CICI ACCOUNT UNLOCK;
    create user jykl identified by jykl default tablespace jykl_data temporary tablespace jykl_temp;
    授权给新用户
    GRANT connect, resource TO cici;
    grant create session to cici;
    4. 授权给新用户
              grant connect,resource to study; 
              --表示把 connect,resource权限授予study用户
              grant dba to study;
              --表示把 dba权限授予给 study
    5. 创建数据表           
          在上面，我们已建好了用户 study 我们现在进入该用户           
          sqlplusw study/study@test   然后就可以在用户study中创建数据表了           
          格式: create table 数据表名 
    
    
    oracle命令建立主键外键
     
    1、创建一张学生表
    create table t_stu(  
      stuid      number(10)   primary key,  
      stuname    varchar2(20) not null,  
      stusex     varchar2(2)  default '男' check(stusex in('男','女'))
    );  
       
     2、创建一张课程表
    create table t_couse(  
      couseid     number(10)   primary key,  
      cousename   varchar2(20) not null,  
      cousetype   varchar2(4)
    );  
     
    3、创建一张学生课程成绩表（包括主外键）
    create table t_score(  
      scoreid    number(10) primary key,  
      stuid      number(10) references t_stu(stuid),  
      couseid    number(10),  
      constraint fk_couseid foreign key(couseid)  
      references t_couse(couseid)  
      on delete cascade
    ); 
    
    
    CREATE TABLE log(
    log_id int(10)   unsigned NOT NULL auto_increment,
    log_time         datetime NOT NULL,
    log_user         varchar(30) NOT NULL,
    log_title        varchar(30) default NULL,
    log_content      text default NULL,
    PRIMARY KEY(log_id));

**orale表管理：**** **

Oracle创建表同SQL Server一样，使用CREATE TABLE命令来完成。创建约束则使用如下命令：

**语法格式：alter table****命令**

**alter table 表名 add constraint 约束名 约束内容。**

不论创建表还是约束，与SQL Server基本相同，注：在Oracle中default是一个值，而SQL Server中default是一个约束，因此Oracle的default设置可以在建表的时候创建。

**案例1：**创建一个学生信息（INFOS）表和约束

**Oracle创建表和约束**

create table INFOS(

STUID            varchar2(7) not null,               --学号 学号=‘S’+班号+2位序号

STUNAME          varchar2(10) not null,              --姓名

GENDER           varchar2(2) not null,               --性别

AGE              number(2) not null,                 --年龄

SEAT             number(2) not null,                 --座号

ENROLLDATE       date,                               --入学时间

STUADDRESS       varchar2(50) default '地址不详',     --住址

CLASSNO          varchar2(4) not null,               --班号 班号=学期序号+班级序号

);

/  ①

**alter table** INFOS **add constraint** pk\_INFOS primary key(STUID)  ②

alter table INFOS add constraint ck\_INFOS\_gender check(GENDER = '男' or GENDER = '女')  ③

alter table INFOS add constraint ck\_INFOS\_SEAT check(SEAT >=0 and SEAT <=50)  ④

alter table INFOS add constraint ck\_INFOS\_AGE check(AGE >=0 and AGE<=100)  ⑤

alter table INFOS add constraint ck\_INFOS\_CLASSNO check((CLASSNO >='1001' and CLASSNO<='1999') or

(CLASSNO >='2001' and CLASSNO<='2999'))  ⑥

alter table INFOS add constraint un\_STUNAME unique(STUNAME)  ⑦

**代码解析：**

①  在Oracle代码中，“/”执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过语句，由于每条语句没有用分号结尾，只是保存在缓冲区，因此每条语句后面都有单独一行“/”。

②  创建一个主键约束。

③  与 ④ ⑤ ⑥ ⑦一起创建各种check约束。其中⑦是唯一约束，表示该列值是唯一的，列中的值不能重复。

Oracle中创建外键约束与SQL Server相同。比如：现有成绩表定义如下：

**案例2：**创建一个成绩表（SCORES）表和约束

**Oracle创建表和约束**

create table scores(

ID                 number,                   --ID  ①

TERM               varchar2(2),              --学期 S1或S2

STUID              varchar2(7) not null,     --学号

EXAMNO             varchar2(7) not null,     --考号 E+班号+序号

WRITTENSCORE       number(4,1) not null,     --笔试成绩

LABSCORE           number(4,1) not null,     --机试成绩

);

ALTER TABLE SCORES ADD CONSTRAINT CK\_SCORES\_TERM CHECK(TERM = 'S1' OR TERM ='S2')

ALTER TABLE SCORES ADD CONSTRAINT FK\_SCORES\_INFOS\_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID)  ②

**代码解析：**

①  SQL Server中可以使用identify创建自动增长列，但是Oracle中的自动增长需要借助序列（Sequence）完成，在后面章节中讲解。

②  Oracle中的外键约束定义。

注意：表名，字段名要大写！

# #

## oracle建表、建主键、外键基本语法 ##

**-创建表格语法:**

create table 表名(

字段名1  字段类型(长度)   是否为空,

字段名2  字段类型           是否为空 );

**-增加主键 **

**alter table** 表名 **add constraint **主键名 **primary key** (字段名1);

**-增加外键: **

**alter table **表名 **add constraint **外键名 **foreign key **(字段名1)** references **关联表 (字段名2);

**在建立表格时就指定主键和外键**

create table T\_STU(

STU\_ID                char(5) not null,

STU\_NAME              VARCHAR2(8) not null,

constraint PK\_T\_STU primary key (STU\_ID));

**主键和外键一起建立：**

create table T\_SCORE(

EXAM\_SCORE             number(5,2),

EXAM\_DATE              date,

AUTOID                 number(10) not null,

STU\_ID                 char(5),

SUB\_ID                 char(3),

constraint PK\_T\_SCORE primary key (AUTOID),

constraint FK\_T\_SCORE\_REFE foreign key (STU\_ID) references T\_STU (STU\_ID));

**orale数据类型：**

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> 类型 </td> 
 <td style="vertical-align:top;"> 含义 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> CHAR(length) </td> 
 <td style="vertical-align:top;"> 存储固定长度的字符串。参数length指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> VARCHAR2(length) </td> 
 <td style="vertical-align:top;"> 存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> NUMBER(p，s) </td> 
 <td style="vertical-align:top;"> 既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点，p默认是38为），s是指小数位数。可存负数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> DATE </td> 
 <td style="vertical-align:top;"> 存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> TIMESTAMP </td> 
 <td style="vertical-align:top;"> 不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> CLOB </td> 
 <td style="vertical-align:top;"> 存储大的文本，比如存储非结构化的XML文档 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> BLOB </td> 
 <td style="vertical-align:top;"> 存储二进制对象，如图形、视频、声音等。 </td> 
 </tr> 
 </tbody> 
</table>

2. 创建表时给字段加默认值 和约束条件

创建表时可以给字段加上默认值 例如 : 日期字段 DEFAULT SYSDATE 这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间

例如：IS\_SEND    NUMBER(1) default 1        --是否已发

创建表时可以给字段加上约束条件 例如: 非空 NOT NULL ，不允许重复 UNIQUE ，关键字 PRIMARY KEY ，按条件检查 CHECK (条件)， 外键 REFERENCES 表名(字段名)

3. 创建表的例子

create table DEPT(  
  DNAME      varchar2(14),                    
  LOC           varchar2(6),     
  EPTNO       number(2)   constraint PK\_DEPT  primary  KEY,                        
);

create table region(  
  ID                    number(2) not null primary KEY,                    
  postcode           number(6) default '0' not null,     
  areaname          varchar2(30) default '' not null,                        
);

4. 创建表时的命名规则和注意事项

1)表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,\_,$,\#

2)大小写不区分

3)不用SQL里的保留字, 一定要用时可用双引号把字符串括起来

4)用和实体或属性相关的英文符号长度有一定的限制

5）约束名的命名规则和语法 约束名的命名规则约束名如果在建表的时候没有指明，系统命名规则是SYS\_Cn(n是数字) 约束名字符串的命名规则同于表和字段名的命名规则

6）使用约束时的注意事项 约束里不能用系统函数,如SYSDATE和别的表的字段比较 可以用本表内字段的比较

注意事项:

1)建表时可以用中文的字段名, 但最好还是用英文的字段名

2)创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面

3)建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引

4)一个表的最多字段个数也是有限制的，254个.

想在事务处理后, 做约束的

检查 SQL> alter session set constraints deferred.

7. 由实体关系图到创建表的例子 s\_dept 前提条件:已有region表且含唯一关键字的字段id SQL> CREATE TABLE s\_dept (id NUMBER(7) CONSTRAINT s\_dept\_id\_pk PRIMARY KEY, name VARCHAR2(25) CONSTRAINT s\_dept\_name\_nn NOT NULL, region\_id NUMBER(7) CONSTRAINT s\_dept\_region\_id\_fk REFERENCES region (id), CONSTRAINT s\_dept\_name\_region\_id\_uk UNIQUE(name, region\_id));

8. 较复杂的创建表例子 SQL> CREATE TABLE s\_emp (id NUMBER(7) CONSTRAINT s\_emp\_id\_pk PRIMARY KEY, last\_name VARCHAR2(25) CONSTRAINT s\_emp\_last\_name\_nn NOT NULL, first\_name VARCHAR2(25), userid VARCHAR2(8) CONSTRAINT s\_emp\_userid\_nn NOT NULL CONSTRAINT s\_emp\_userid\_uk UNIQUE, start\_date DATE DEFAULT SYSDATE, comments VARCHAR2(25), manager\_id NUMBER(7), title VARCHAR2(25), dept\_id NUMBER(7) CONSTRAINT s\_emp\_dept\_id\_fk REFERENCES s\_dept(id), salary NUMBER(11,2), commission\_pct NUMBER(4,2) CONSTRAINT s\_emp\_commission\_pct\_ck CHECK (commission\_pct IN(10,12.5,15,17.5,20)));

8. 通过子查询建表 通过子查询建表的例子 SQL>CREATE TABLE emp\_41 AS SELECT id, last\_name, userid, start\_date FROM s\_emp WHERE dept\_id = 41;

SQL> CREATE TABLE A as select \* from B where 1=2; 只要表的结构.

10. 用子查询建表的注意事项 1)可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。 2)用子查询方式建立的表，只有非空NOT NULL的约束条件能继承过来, 其它的约束条件和默认值都没有继承过来. 3)根据需要，可以用alter table add constraint ……再建立其它的约束条件，如primary key等.

11. Foreign Key的可选参数ON DELETE CASCADE 在创建Foreign Key时可以加可选参数: ON DELETE CASCADE它的含义是如果删除外键主表里的内容，子表里相关的内容将一起被删除. 如果没有ON DELETE CASCADE参数，子表里有内容，父表里的主关键字记录不能被删除掉.

12. 如果[数据库][Link 1]表里有不满足的记录存在，建立约束条件将不会成功.

13. 给表创建和删除同义词的例子 SQL> CREATE SYNONYM d\_sum 2 FOR dept\_sum\_vu;

SQL> CREATE PUBLIC SYNONYM s\_dept 2 FOR alice.s\_dept;

SQL> DROP SYNONYM s\_dept;

# [ORACLE之新建][ORACLE]表 #

# 创建一个名为INSURES的表 #

create table INSURES

(

INSURE\_NO                CHAR(18) not null,          --医保号

GETSURE\_UNIT\_NO          CHAR(9) not null,           --经办机构号

INSURE\_NAME              VARCHAR2(10) not null,      --姓名

INSURE\_SEX               CHAR(1) not null,           --性别

ID\_CARD\_NO               CHAR(18) not null,          --身份证号

);

创建/修改主键，唯一性约束和外键   这里INSURE\_NO, GETSURE\_UNIT\_NO唯一性约束

alter table INSURES

add constraint UNQ\_INSURES unique (INSURE\_NO, GETSURE\_UNIT\_NO)

alter table TWN\_SEED

add constraint UNQ\_INSURES primary key (。。。。)

创建索引

create index IDX\_INSURES on INSURES (GETSURE\_UNIT\_NO, SONSURE\_UNIT\_NO, UNIT\_NO, FAMILY\_NO, HOSPS\_NO)

创建一个PK的时候，是自动创建一个与之对应的唯一索引的。  如果不特别指定，那么这个索引的表空间和表格的空间是一样的，但是我们不建议放在一起。

create table testone(

name varchar2(10 char))

TABLESPACE1;

ALTER TABLE TESTONE ADD CONSTRAINT  PK\_TESTONE1 PRIMARY KEY(NAME) USING INDEX TABLESPACE TABLESPACE2;

作为一种好习惯，不要把索引和表格的数据存在在同一个表空间中

# [Oracle创建表语法 - create][Oracle_ - create] #

\--创建Oracle表（使用create关键字）

\-- \*\*\*\*\*\*

（1）创建新表 use 数据库（在那个数据库中建表） create table 表名

( 字段名1（列名） 数据类型 列的特征,

字段名2（列名） 数据类型 列的特征(NOT NULL),

...... )

（2）创建带有主键约束的表语法 create table 表名 (

字段名1（列名） 数据类型 列的特征,

字段名2 数据类型 列的特征(NOT NULL),

...... primary key(主键列字段))

（3）利用现有的表创建表 -- 注意：仅复制Oracle数据表结构：采用的是子查询方式 create table 新表 as select \* from 旧的表 where 1=2  
（4）利用现有的表的结构创建新表 -- 注意：仅复制Oracle数据表结构：采用的是子查询方式 create table 新表 select 字段1,字段2... from 旧的表 where 条件（旧的表字段满足的条件）  
（5）利用现有的表的结构创建新表 -- 注意：复制Oracle数据表数据 create table 新表 as select \* from 旧的表 where 1=1  
（6）利用现有的表的结构创建新表  -- 注意：复制Oracle数据表数据 create table 新表 as select 字段1,字段2... from 旧的表 where 条件（旧的表字段满足的条件）（7）将查询结果插入另一张表 insert into 另一张表     select \* from 要查询的表 where 条件（要查询的表的列符合什么条件）

[oracle create table(转)][oracle create table]

*  //建测试表  
 *  create table dept(  
 *         deptno number(3) primary key,  
 *         dname varchar2(10),  
 *         loc varchar2(13)   
 *         );  
 *  create table employee\_info(  
 *         empno number(3),  
 *         deptno number(3),  
 *         ename varchar2(10),  
 *         sex char(1),  
 *         phone number(11),  
 *         address varchar2(50),  
 *         introduce varchar2(100)  
 *         );  
 *  \--   
 *  **重命名**  
 *  **重命名表**:rename dept to dt;  
 *               rename dt to dept; 
 *   
 *  重命名列:alter table dept rename column loc to location;  
 *               alter table dept rename column location to loc;  
 *  添加约束  
 *  1. primary key  
 *        alter table employee\_info add constraint pk\_emp\_info primary key(empno);
 *    
 *  2. foreign key  
 *        alter table employee\_info add constraint fk\_emp\_info foreign key(deptno)  
 *        references dept(deptno);  
 *   
 *  3. check  
 *        alter table employee\_info add constraint ck\_emp\_info check  
 *        (sex in ('F','M'));  
 *   
 *  4. not null  
 *        alter table employee\_info modify phone constraint not\_null\_emp\_info not null;  
 *   
 *  5. unique  
 *        alter table employee\_info add constraint uq\_emp\_info unique(phone);  
 *   
 *  6. default  
 *        alter table employee\_info modify sex char(2) default 'M';  
 *   
 *  添加列  
 *     alter table employee\_info add id varchar2(18);  
 *     alter table employee\_info add hiredate date default sysdate not null; 
 *   
 *  删除列  
 *     alter table employee\_info drop column introduce;  
 *   
 *  修改列  
 *  1.修改列的长度  
 *        alter table dept modify loc varchar2(50);
 *    
 *  2. 修改列的精度  
 *        alter table employee\_info modify empno number(2);
 *    
 *  3. 修改列的数据类型  
 *        alter table employee\_info modify sex char(2); 
 *   
 *  4. 修改默认值  
 *        alter table employee\_info modify hiredate default sysdate+1; 
 *   
 *  5.禁用约束  
 *    alter table employee\_info disable constraint uq\_emp\_info;
 *    
 *  6.启用约束  
 *    alter table employee\_info enable constraint uq\_emp\_info;  
 *   
 *  7.延迟约束  
 *    alter table employee\_info drop constraint fk\_emp\_info;  
 *    alter table employee\_info add constraint fk\_emp\_info foreign key(deptno)  
 *          references dept(deptno)  
 *    deferrable initially deferred;  
 *   
 *  8.向表中添加注释  
 *    comment on table employee\_info is 'information of employees';
 *    
 *  9.向列添加注释  
 *    comment on column employee\_info.ename is 'the name of employees';  
 *    comment on column dept.dname is 'the name of department';  
 *   
 *  10.清除表中所有数据  
 *    truncate table employee\_info; (DELETE FROM table\_name或DELETE \* FROM table\_name) 
 *   
 *  11.删除表  
 *    drop table employee\_info;  
 *  \--   
 *  //下面来看看刚刚才我们对表dept和表employee\_info所做的更改  
 *  //user\_constraints视图里面包含了刚刚才我们创建的所有约束，以及其他信息，  
 *  //你可以用desc user\_constraints命令查看其详细说明  
 *  select constraint\_name,constraint\_type,status,deferrable,deferred  
 *  from user\_constraints  
 *  where table\_name='EMPLOYEE\_INFO';  
 *  \--   
 *  CONSTRAINT\_NAME                CONSTRAINT\_TYPE STATUS   DEFERRABLE     DEFERRED  
 *  \------------------------------ --------------- -------- -------------- ---------   
 *  PK\_EMP\_INFO                    P               ENABLED  NOT DEFERRABLE IMMEDIATE  
 *  FK\_EMP\_INFO                    R               ENABLED  DEFERRABLE     DEFERRED  
 *  NOT\_NULL\_EMP\_INFO              C               ENABLED  NOT DEFERRABLE IMMEDIATE  
 *  SYS\_C005373                    C               ENABLED  NOT DEFERRABLE IMMEDIATE  
 *  UQ\_EMP\_INFO                    U               ENABLED  NOT DEFERRABLE IMMEDIATE  
 *  CK\_EMP\_INFO                    C               ENABLED  NOT DEFERRABLE IMMEDIATE  
 *  //我们可以通过user\_cons\_columns视图查看有关列的约束信息；  
 *  select owner,constraint\_name,table\_name,column\_name  
 *  from user\_cons\_columns  
 *  where table\_name='EMPLOYEE\_INFO';  
 *  \--   
 *  OWNER                          CONSTRAINT\_NAME                TABLE\_NAME                     COLUMN\_NAME  
 *  \------------------------------ ------------------------------ ------------------------------ ---------------   
 *  YEEXUN                         PK\_EMP\_INFO                    EMPLOYEE\_INFO                  EMPNO  
 *  YEEXUN                         CK\_EMP\_INFO                    EMPLOYEE\_INFO                  SEX  
 *  YEEXUN                         NOT\_NULL\_EMP\_INFO              EMPLOYEE\_INFO                  PHONE  
 *  YEEXUN                         SYS\_C005373                    EMPLOYEE\_INFO                  HIREDATE  
 *  YEEXUN                         UQ\_EMP\_INFO                    EMPLOYEE\_INFO                  PHONE  
 *  YEEXUN                         FK\_EMP\_INFO                    EMPLOYEE\_INFO                  DEPTNO  
 *  //我们将user\_constraints视图与user\_cons\_columns视图连接起来  
 *  //查看约束都指向哪些列  
 *  column column\_name format a15;  
 *  select ucc.column\_name,ucc.constraint\_name,uc.constraint\_type,uc.status  
 *  from user\_constraints uc,user\_cons\_columns ucc  
 *  where uc.table\_name=ucc.table\_name and  
 *        uc.constraint\_name=ucc.constraint\_name and  
 *        ucc.table\_name='EMPLOYEE\_INFO';  
 *  \--   
 *  COLUMN\_NAME     CONSTRAINT\_NAME                CONSTRAINT\_TYPE STATUS  
 *  \--------------- ------------------------------ --------------- --------   
 *  EMPNO           PK\_EMP\_INFO                    P               ENABLED  
 *  DEPTNO          FK\_EMP\_INFO                    R               ENABLED  
 *  PHONE           NOT\_NULL\_EMP\_INFO              C               ENABLED  
 *  HIREDATE        SYS\_C005373                    C               ENABLED  
 *  PHONE           UQ\_EMP\_INFO                    U               ENABLED  
 *  SEX             CK\_EMP\_INFO                    C               ENABLED  
 *  \--   
 *  //这里有个constraint\_type，他具体指下面几种类型:  
 *  //C:check,not null  
 *  //P:primary key  
 *  //R:foreign key  
 *  //U:unique  
 *  //V:check option  
 *  //O:read only  
 *  \--   
 *  //我们可以通过user\_tab\_comments视图获得对表的注释  
 *  select \* from user\_tab\_comments  
 *  where table\_name='EMPLOYEE\_INFO';  
 *  TABLE\_NAME                     TABLE\_TYPE  COMMENTS  
 *  \------------------------------ ----------- --------------------------   
 *  EMPLOYEE\_INFO                  TABLE       information of employees  
 *  \--   
 *  //我们还可以通过user\_col\_comments视图获得对表列的注释：  
 *  select \* from  user\_col\_comments  
 *  where table\_name='EMPLOYEE\_INFO';  
 *  \--   
 *  TABLE\_NAME                     COLUMN\_NAME                    COMMENTS  
 *  \------------------------------ ------------------------------ ---------------------------   
 *  EMPLOYEE\_INFO                  EMPNO                            
 *  EMPLOYEE\_INFO                  DEPTNO                           
 *  EMPLOYEE\_INFO                  ENAME                          the name of employees  
 *  EMPLOYEE\_INFO                  SEX                              
 *  EMPLOYEE\_INFO                  PHONE                            
 *  EMPLOYEE\_INFO                  ADDRESS                          
 *  EMPLOYEE\_INFO                  ID                               
 *  EMPLOYEE\_INFO                  HIREDATE   
 *  \--   
 *  select \* from user\_col\_comments  
 *  where table\_name='EMPLOYEE\_INFO' and  
 *        comments is not null;  
 *  \--   
 *  TABLE\_NAME                     COLUMN\_NAME                    COMMENTS  
 *  \------------------------------ ------------------------------ ------------------------   
 *  EMPLOYEE\_INFO                  ENAME                          the name of employees  
 *  \--   
 *  //最后我们来查看一下修改后的表:  
 *  desc employee\_info;  
 *  Name     Type         Nullable Default   Comments                
 *  \-------- ------------ -------- --------- ---------------------    
 *  EMPNO    NUMBER(2)                                               
 *  DEPTNO   NUMBER(3)    Y                                          
 *  ENAME    VARCHAR2(10) Y                  the name of employees   
 *  SEX      CHAR(2)      Y        'M'                               
 *  PHONE    NUMBER(11)                                              
 *  ADDRESS  VARCHAR2(50) Y                                          
 *  ID       VARCHAR2(18) Y                                          
 *  HIREDATE DATE                  sysdate+1  
 *  \--   
 *  desc dept;  
 *  Name   Type         Nullable Default Comments                 
 *  \------ ------------ -------- ------- ----------------------    
 *  DEPTNO NUMBER(3)                                              
 *  DNAME  VARCHAR2(10) Y                the name of department   
 *  LOC    VARCHAR2(50) Y

create table test_user (               no          number(5)      not null , --pk               username    varchar2(30)    not null , --用户名               passpord    varchar2(30)    not null , --密码               constraint pk_connectdb primary key(no)           )storage (initial 10k next 10k pctincrease 0);

\*下面讲解上面命令的各方面的含义           create table test\_user --创建数据表               no              number(5)            not null ,        --pk               (列名或字段名) 数据类型(数据长度) 该数据列不能为空 ,是列之间的分隔符 --后的内容是注释                   constraint pk\_connectdb primary key(no)           (约束)    约束名      (主键)    (列名)    意思就是 在数据表 test\_user中所有行数据 no的值不能相同(这就是主键的含义)

storage (initial 10k next 10k pctincrease 0); 这个说起来比较复杂, 反正如果某个数据表要存放大量数据，就把initial和next后的值设置大一点, 否则设置小一点.

既然上面在创建数据表中没有特别指定 表空间，当然该表就存放在study缺省表空间data\_test了.

create tablespace data\_phonepos datefile 'd:\\install\\OracleXpdb\\datafilephonepos.dbf' size 8000M; create user phonepos identified by phonepos default tablespace data\_phonepos;

grant connect, resource to phonepos; grant dba to phonepos;

权限的查询

5.1 查询某个用户授予其他用户在当前用户模式下的对象权限    select \* from user\_tab\_privs\_made --假如当前用户为WENZI，那么查询结果就是由WENZI授权，在WENZI模式下的权限记录

5.2 查询某个用户授予其他用户在该用户模式对象及其他模式对象上的对象权限    select \* from all\_tab\_privs\_made -- 假如当前登录用户为WENZI，那么查询结果就是所有由WENZI授予的权限的记录

修改用户

ALTER USER avyrros IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data\_ts TEMPORARY TABLESPACE temp\_ts QUOTA 100M ON data\_ts QUOTA 0 ON test\_ts PROFILE clerk;

删除用户 DROP USER username \[CASCADE\] --CASECADE 选项会删除该用户模式下的所有对象，建议在删除前，先确认是否有其他的依赖关系存在。

查询属于用户的对象 select owner,object\_name,object\_type,status from dba\_objects where owner='WENZI'

5.3 查询为某个用户授予的，在其他模式对象上的权限          select \* from user\_tab\_privs\_recd --假如当前登录用户为WENZI，那么查询结果就是WENZI在其他模式对象上的权限

5.4 查询为某个用户授予的，在该用户模式对象与其他模式对象上的权限    select \* from all\_tab\_privs\_recd --假如当前用户为wenzi，则查询结果为wenzi在整个数据库中拥有权限的对象

角色管理

创建口令文件 orapwd file='..........\\pwd\{SID\}.ora' password='\*\*\*(sys的密码)' tntries=10(口令文件最大的用户数量)

要使某个用户可以使用口令文件，必须为其授予SYSDBA权限，系统会自动将其加入到口令文件中。 grant sysdba to wenzi 当收回SYSDBA权限时，系统将对应的用户从口令文件中删除。 revoke sysdba from wenzi

查看口令文件管理的用户 select \* from v$pwfile\_users

创建步骤： 
    SYS用户在CMD下以DBA身份登陆：
    [user@root ~]$ sqlplus /nolog

SQL> conn sys/h1w2D3B4 as sysdba

SQL> startup

\[user@root ~\]$ lsnrctl start

**// 分为四步**

**//第1步：创建临时表空间  **

**create temporary tablespace user\_temp  **

**tempfile 'D:\\oracle\\oradata\\Oracle9i\\user\_temp.dbf'**

**size 50m  **

**autoextend on  **

**next 50m maxsize 20480m  **

**extent management local;  **

** ****//第2步：创建数据表空间  **

**create tablespace user\_da****ta**

**logging  **

**datafile 'D:\\oracle\\oradata\\Oracle9i\\user\_da****ta.dbf' **

**size 50m  **

**autoextend on  **

**next 50m maxsize 20480m  **

**extent management local;  **

** **

**//第3步：创建用户并指定表空间  **

**create user username identified by password  **

**default tablespace user\_da****ta  **

**temporary tablespace user\_temp;  **

** **

**//第4步：给用户授予权限  **

** ****grant connect,resource to username;  **

\---------------------------------------------------------------------------------

//以后以该用户登录，创建的任何数据库对象都属于user\_temp 和user\_data表空间，

这就不用在每创建一个对象给其指定表空间了

撤权：

revoke   权限...   from  用户名;

删除用户命令

drop user user\_name cascade;

建立表空间

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM SIZE 128k; \#指定区尺寸为128k,如不指定，区尺寸默认为64k

删除表空间

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

一、建立表空间

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM SIZE 128k; \#指定区尺寸为128k,如不指定，区尺寸默认为64k

二、建立UNDO表空间

CREATE UNDO TABLESPACE UNDOTBS02

DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M

\#注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间:

ALTER SYSTEM SET undo\_tablespace=UNDOTBS02;

三、建立临时表空间

CREATE TEMPORARY TABLESPACE temp\_data

TEMPFILE '/oracle/oradata/db/TEMP\_DATA.dbf' SIZE 50M

四、改变表空间状态

1.使表空间脱机

ALTER TABLESPACE game OFFLINE;

如果是意外删除了数据文件，则必须带有RECOVER选项

ALTER TABLESPACE game OFFLINE FOR RECOVER;

2.使表空间联机

ALTER TABLESPACE game ONLINE;

3.使数据文件脱机

ALTER DATABASE DATAFILE 3 OFFLINE;

4.使数据文件联机

ALTER DATABASE DATAFILE 3 ONLINE;

5.使表空间只读

ALTER TABLESPACE game READ ONLY;

6.使表空间可读写

ALTER TABLESPACE game READ WRITE;

五、删除表空间

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

六、扩展表空间

首先查看表空间的名字和所属文件

select tablespace\_name, file\_id, file\_name,

round(bytes/(1024\*1024),0) total\_space

from dba\_data\_files

order by tablespace\_name;

1.增加数据文件

ALTER TABLESPACE game

ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

2.手动增加数据文件尺寸

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'

RESIZE 4000M;

3.设定数据文件自动扩展

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf

AUTOEXTEND ON NEXT 100M

MAXSIZE 10000M;

设定后查看表空间信息

SELECT A.TABLESPACE\_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES\*100)/A.BYTES "% USED",(C.BYTES\*100)/A.BYTES "% FREE"

FROM SYS.SM$TS\_AVAIL A,SYS.SM$TS\_USED B,SYS.SM$TS\_FREE C

WHERE A.TABLESPACE\_NAME=B.TABLESPACE\_NAME AND A.TABLESPACE\_NAME=C.TABLESPACE

创建数据表：

[Oracle中建表和指定表空间][Oracle 1]

\--建一个表 create table HH2(

tid number primary key ,--主键设定

tname varchar2(20)

);

\--删除表 drop table HH;

\--表空间（相当于一个数据库）(DBA权限) create tablespace test datafile 'D:test.dbf' size 10M autoextend on next 10M maxsize 100M

\--指定表在那个表空间里面(默认在USERS表空间里) create table HH(tid number primary key) tablespace test; select \* from tabs;

\--删除 表空间 drop tablespace test including contents and datafiles --连带物理文件和表空间中的数据也一起删除

\--建表建约束 create table student1(

sid number primary key,

sname varchar2(20) not null,

sage number,

ssex char(2),

saddress varchar2(100),

cid number references tclass(cid)--建立外键关系 );

create table tclass (

cid number primary key,

cname varchar2(20) );

\--唯一unique 检查 check 默认值 modify 添加外键关系 添加列

alter table student1 add constraint UQ\_student1\_sname unique(sname);

alter table student1 add constraint CK\_student1\_agae check(sage between 19 and 70);

alter table student1 modify ssex default '男';

alter table student1 add constraint FK\_student1\_cid foreign key(cid) references tclass(cid);

alter table student1 add dt date;

\--删除约束 alter table student1 drop constraint  UQ\_student1\_sname ;

1.创建oracle数据表

创建oracle数据表的语法如下：

![SouthEast][]

create table命令用于创建一个oracle数据表；括号内列出了数据表应当包含的列及列的数据类型；tablespace则指定该表的表空间。

创建数据表students。

![SouthEast 1][]

在该创建语句中，依次定义了student\_id、student\_name、student\_age、status及version等列；tablespace users表示将表创建于表空间users中。

通过视图user\_tables可以获得当前用户所拥有的表信息，利用如下SQL语句可以查看表student的表空间信息。

![SouthEast 2][]

在oracle的SQL命令行下，可以利用describe命令来查看已有数据表的表结构，如下所示。

![SouthEast 3][]

2.数据表的相关操作

数据表创建之后，由于某些原因，例如，设计时的考虑不足，往往需要对其进行结构上的调整。常见的调整包括，增加新列、修改已有列、删除、重命名已有列。另外，还可以转移数据表的表空间。

修改数据表结构应当使用alter table命令。例如，在表student中，增加新列class\_id(班级ID)的SQL语句如下图所示。

![SouthEast 4][]

alter table student 用于修改表student的结构；add用于增加列，注意此处没有collumn关键字；小括号内是列及列的数据类型；用户可以一次性为表增加多个列，各列之间使用逗号进行分隔。

在修改成功之后，表student的结构如下所示。

![SouthEast 5][]

同样，可以利用alter命令修改和删除已有列。

利用alter命令将class\_id的数据类型修改为varchar2(20)。

![SouthEast 6][]

modify(class\_id varchar2(20))用于修改表student中的已有列class\_id，实际相当于重新定义。该列新的数据类型为varchar2(20)。此时表student的结构如下：

![SouthEast 7][]

通过modify选项可以将列的类型重新定义，而通过drop选项则删除已有列，如下所示。

![SouthEast 8][]

drop column class\_id用于删除已有列class\_id；需要注意的是，此处必须添加column选项，才能表示删除的目标是一个列。此时，表student的结构已经修改如下：

![SouthEast 9][]

对于数据表的列，除了增、删、改操作之外，还可以进行重命名操作。重命名一个列，应该使用rename选项。例如，为了与其他数据表进行统一，需要将表student的列student\_id重命名为id，则可以利用如下SQL语句。

![SouthEast 10][]

rename column student\_id to id用于将列student\_id重命名为id。此时，表student的结构如下所示。

![SouthEast 11][]

对于调整数据表结构来说，要特别注意严谨性。列的数据类型的修改，有可能会影响应用程序对数据库进行存取；而列的删除和重命名更需要检查应用程序是否会出现关联性错误。

如果数据表创建时，选择了错误的表空间，那么可以利用alter table命令，结合move tablespace选项转移表空间，如下图所示。

将表student转移至表空间users中。

![SouthEast 12][]

alter table student用于修改表student的属性；move tablespace users用于将表student从当前表空间转移至表空间users。可以通过查询视图user\_tables获得表student转移之后的表空间信息，如下所示。

![SouthEast 13][]

分析查询结果可知，利用move tablespace选项已经成功实现了表空间的转移。

删除数据表

利用drop table命令删除数据表

![SouthEast 14][]

有时，由于某些约束的存在，例如，当前表的主键被其他表用作外键，会导致无法成功删除。利用cascade constraints选项可以将约束同时删除，从而保证drop table命令一定能够成功执行。

![SouthEast 15][]

特殊的数据表dual

dual表实际属于系统用户sys，具有了数据库基本权限的用户，均可查询该表的内容，如下所示：

![SouthEast 16][]

或者

![SouthEast 17][]

分析查询结果可知，dual表仅含有一行一列。该表并非为了存储数据而创建的，其存在的意义在于提供强制的数据源。

在oracle中，所有查询语句必须满足select column\_name from table\_name的格式。但是，在某些场景下，数据源table\_name并不明确。例如，函数sysdate()用于返回当前日期，那么在SQL命令行下调用该函数时，很难有明确的数据源，此时即可使用dual表。

利用dual表提供数据源，以获得当前日期。

![SouthEast 18][]

同样，对于各种数学运算，同样可以利用dual表作为数据源，来打印和查看输出结果。

![SouthEast 19][]

dual表提供了一行一列的数据格式，从而使各种表达式、函数运算在以其为数据源时，能够输出单行单列的形式。

由于dual表的所有者为系统用户sys，因此，只有用户利用该身份登录数据库才可以修改该表。但是，修改该表的内容或者结构都应该被禁止。

# [oracle create tablespace][] #

1.  create tablespace ZGECM   
2.   logging datafile 'G:\\oracle\\product\\10.2.0\\ZGECM.DBF'  
3.   size 500M autoextend on  
4.   next 10M maxsize 500M extent management local  
5.    
6.    
7.  create user sms2 identified by sms2 default tablespace ECM2   
8.  temporary tablespace temp profile default;  
9.    
10. \--6 授权给新建用户  
11. grant connect,resource to sms2;  
12. grant dba to sms2;  
13.   
14.   
15. exp xuner\_ecm/xuner\_ecm@192.168.1.139/SMS FILE=E:/xunerecm0910\_server.DMP OWNeR=xuner\_ecm  
16.   
17. imp scjt/scjt@SMS fromuser=ecm touser=sms2 file='G:\\sms.dmp'   
18.   
19.   
20. imp SMS/SMS@LOCAL\_SMS fromuser=SMS touser=SMS file='G:\\sms.dmp'    
21.   
22. select userenv('language') from dual;     //查询oracle服务器端的字符  
23.   
24. select nls\_charset\_name(to\_number('0354','0354'))  
25.  from dual;  
26.   
27.   
28.   
29. CREATE DATABASE LINK sms CONNECT TO sms IDENTIFIED BY sms  
30. USING 'SMS';  
31.   
32.   
33.   
34. insert into XSJBXXB select \* from sms.xsjbxxb@zhang2  
35.   
36. insert into XSJBXXB select \* from sms.xsjbxxb@to\_test  
37.   
38. \--创建dblink  
39. 　create database link to\_test  
40. 　　connect to sms identified by sms  
41. 　　using 'ZHANG';  
42.   
43. '(DESCRIPTION =(  
44. ADDRESS\_LIST =(  
45. ADDRESS =(  
46. PROTOCOL = TCP)(  
47. HOST = 192.168.0.100)(PORT = 1521)))  
48. (CONNECT\_DATA =(SERVICE\_NAME = AAA)))'  
49.   
50. \--查询约束  
51. select owner,constraint\_name,table\_name from user\_constraints   
52. where constraint\_name='SYS\_C00119759'  
53.   
54.   
55.   
56. \-----------------------------------------------------  
57.   
58.   
59. /\*分为四步 \*/  
60. /\*第1步：创建临时表空间  \*/  
61. create temporary tablespace user\_temp    
62. tempfile 'D:\\oracle\\oradata\\Oracle9i\\user\_temp.dbf'   
63. size 50m    
64. autoextend on    
65. next 50m maxsize 20480m    
66. extent management local;    
67.    
68. /\*第2步：创建数据表空间  \*/  
69. create tablespace user\_data    
70. logging    
71. datafile 'D:\\oracle\\oradata\\Oracle9i\\user\_data.dbf'   
72. size 50m    
73. autoextend on    
74. next 50m maxsize 20480m    
75. extent management local;    
76.    
77. /\*第3步：创建用户并指定表空间  \*/  
78. create user username identified by password    
79. default tablespace user\_data    
80. temporary tablespace user\_temp;    
81.    
82. /\*第4步：给用户授予权限  \*/  
83. grant connect,resource,dba to username;  
84.   
85.   
86. \--Error dropping MEM\_GENINF:  
87. \--ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源   
88. \--Record is locked by another user  
89.   
90. \--1.查看锁  
91. select t2.username,t2.sid,t2.serial\#,t2.logon\_time   
92. from v$locked\_object t1,v$session t2 where t1.session\_id=t2.sid ;   
93.   
94. \--2、Kill   
95. alter system kill session 'sid,serial\#';   
96. alter system kill session '151,14678';

### 更多精彩技术分享请浏览本人博客：[https://blog.csdn.net/wohiusdashi][https_blog.csdn.net_wohiusdashi] ###

[Oracle]: https://www.cnblogs.com/qmfsun/p/3817344.html
[Link 1]: http://www.51xit.com/www/c32_1.html
[ORACLE]: http://www.cnblogs.com/ballet1029/articles/1357442.html
[Oracle_ - create]: http://www.cnblogs.com/haibin168/archive/2011/02/20/1959168.html
[oracle create table]: http://www.cnblogs.com/fengqingtao/p/3706207.html
[Oracle 1]: http://www.cnblogs.com/laotan/p/3663053.html
[SouthEast]: /images/20220519/637bb1ee39d946558d7cd2751379dc4b.png
[SouthEast 1]: /images/20220519/65fa68e3da5e41dd9caf2e9926f72a4b.png
[SouthEast 2]: /images/20220519/66074f7269774973a35e0fbbb69090ec.png
[SouthEast 3]: /images/20220519/55a9ca4736b34cee9d1f96f5f663db2e.png
[SouthEast 4]: /images/20220519/1efd48ab6064475d8ce39b0a886610e6.png
[SouthEast 5]: /images/20220519/5cdaf25bf4424443868845db44ba8f36.png
[SouthEast 6]: /images/20220519/43a7aed1fe4b43ae8a21593a15571f83.png
[SouthEast 7]: /images/20220519/81e47667446e4551aa44a4437da12b87.png
[SouthEast 8]: /images/20220519/7956d65cf5c043bdb57829cd785328e5.png
[SouthEast 9]: /images/20220519/e6feef4faeea4cc194b6652c5d16de62.png
[SouthEast 10]: /images/20220519/726b5d6e094a4d33b286868c3943737e.png
[SouthEast 11]: /images/20220519/43b3a2153e2f4a649171b2818b3cbe26.png
[SouthEast 12]: /images/20220519/14d3b73b296c47ee894e4905f7530963.png
[SouthEast 13]: /images/20220519/0d9226af8e4c46358c8aecd32ac7134f.png
[SouthEast 14]: /images/20220519/a8f1d00d9027423d85aeb2d7b08cceab.png
[SouthEast 15]: /images/20220519/fef369c988c64092985fbba6c8de547c.png
[SouthEast 16]: /images/20220519/4cd0b0fd63cf422eaee4a08144007d9a.png
[SouthEast 17]: /images/20220519/65bfb06223374859964ceae2ae9a8ff4.png
[SouthEast 18]: /images/20220519/a360aa6fc0764f20b15ae270024d8458.png
[SouthEast 19]: /images/20220519/8345787ecc1a47a3abc4625947e803ce.png
[oracle create tablespace]: http://blog.csdn.net/zhx624/article/details/17753013
[https_blog.csdn.net_wohiusdashi]: https://blog.csdn.net/wohiusdashi