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_data

logging  

datafile ‘D:\oracle\oradata\Oracle9i\user_data.dbf‘ 

size 50m  

autoextend on  

next 50m maxsize 20480m  

extent management local;  

 

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

create user username identified by password  

default tablespace user_data  

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中建表和指定表空间

 

--建一个表 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数据表的语法如下：

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

创建数据表students。

在该创建语句中，依次定义了student_id、student_name、student_age、status及version等列；tablespace users表示将表创建于表空间users中。

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

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

2.数据表的相关操作

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

删除数据表

利用drop table命令删除数据表

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

特殊的数据表dual

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

或者

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

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

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

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

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‘;  

Oracle创建表空间和表

标签：否则   设置   --   ber   synonym   填充   保留   sel   生成