數(shù)據(jù)表空間 (Tablespace)         
存放數(shù)據(jù)總是需要空間， Oracle把一個數(shù)據(jù)庫按功能劃分若干空間來保存數(shù)據(jù)。當然數(shù)據(jù)存放在磁盤最終是以文件形式，所以一盤一個數(shù)據(jù)表空間包含一個以上的物理文件
數(shù)據(jù)表         
在倉庫，我們可能有多間房子，每個房子又有多個貨架，每架又有多層。 我們在數(shù)據(jù)庫中存放數(shù)據(jù)，最終是數(shù)據(jù)表的單元來存儲與管理的。
數(shù)據(jù)文件         
以上幾個概念都是邏輯上的， 而數(shù)據(jù)文件則是物理上的。就是說，數(shù)據(jù)文件是真正“看得著的東西”，它在磁盤上以一個真實的文件體現(xiàn)

1、創(chuàng)建表空間：
格式:  表間名  '數(shù)據(jù)文件名'  表空間大小
                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;
                (*數(shù)據(jù)文件名 包含全路徑, 表空間大小 2000M 表是 2000兆) 
2、建好tablespace, 就可以建用戶了
          格式:  用戶名  密碼  表空間表;
                create user study identified by study default tablespace data_test;
                (*我們創(chuàng)建一個用戶名為 study,密碼為 study, 缺少表空間為 data_test -這是在第二步建好的.)
                (*缺省表空間表示 用戶study今后的數(shù)據(jù)如果沒有專門指出，其數(shù)據(jù)就保存在 data_test中, 也就是保存在對應的物理文件 e:\oracle\oradata\test\data_1.dbf中)


4. 授權給新用戶
          grant connect,resource to study; 
          --表示把 connect,resource權限授予study用戶
          grant dba to study;
          --表示把 dba權限授予給 study
5. 創(chuàng)建數(shù)據(jù)表           
      在上面，我們已建好了用戶 study 我們現(xiàn)在進入該用戶           
      sqlplusw    然后就可以在用戶study中創(chuàng)建數(shù)據(jù)表了           
      格式:  數(shù)據(jù)表名 


oracle命令建立主鍵外鍵
 
1、創(chuàng)建一張學生表
create table t_stu(  
  stuid      number(10)   primary key,  
  stuname    varchar2(20) not null,  
  stusex     varchar2(2)  default '男' check(stusex in('男','女'))
);  
   
 2、創(chuàng)建一張課程表
create table t_couse(  
  couseid     number(10)   primary key,  
  cousename   varchar2(20) not null,  
  cousetype   varchar2(4)
);  
 
3、創(chuàng)建一張學生課程成績表（包括主外鍵）
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創(chuàng)建表同SQL Server一樣，使用CREATE TABLE命令來完成。創(chuàng)建約束則使用如下命令：

語法格式：alter table命令

alter table 表名 add constraint 約束名 約束內(nèi)容。

不論創(chuàng)建表還是約束，與SQL Server基本相同，注：在Oracle中default是一個值，而SQL Server中default是一個約束，因此Oracle的default設置可以在建表的時候創(chuàng)建。

案例1：創(chuàng)建一個學生信息（INFOS）表和約束

 Oracle創(chuàng)建表和約束

 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代碼中，“/”執(zhí)行緩存區(qū)中的語句，由于緩沖區(qū)中只存儲一條剛剛保存過語句，由于每條語句沒有用分號結尾，只是保存在緩沖區(qū)，因此每條語句后面都有單獨一行“/”。

②  創(chuàng)建一個主鍵約束。

③  與 ④ ⑤ ⑥ ⑦一起創(chuàng)建各種check約束。其中⑦是唯一約束，表示該列值是唯一的，列中的值不能重復。

 Oracle中創(chuàng)建外鍵約束與SQL Server相同。比如：現(xiàn)有成績表定義如下：

 案例2：創(chuàng)建一個成績表（SCORES）表和約束

 Oracle創(chuàng)建表和約束

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創(chuàng)建自動增長列，但是Oracle中的自動增長需要借助序列（Sequence）完成，在后面章節(jié)中講解。

②  Oracle中的外鍵約束定義。

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

oracle建表、建主鍵、外鍵基本語法

-創(chuàng)建表格語法:     

create table 表名(       

字段名1  字段類型(長度)   是否為空,        

字段名2  字段類型           是否為空 );

-增加主鍵     

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

-增加外鍵:     

alter table 表名 add constraint 外鍵名 foreign key (字段名1) references 關聯(lián)表 (字段名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數(shù)據(jù)類型：

2. 創(chuàng)建表時給字段加默認值 和約束條件

創(chuàng)建表時可以給字段加上默認值 例如 : 日期字段 DEFAULT SYSDATE 這樣每次插入和修改時, 不用程序操作這個字段都能得到動作的時間

例如：IS_SEND    NUMBER(1) default 1        --是否已發(fā)

創(chuàng)建表時可以給字段加上約束條件 例如: 非空 NOT NULL ，不允許重復 UNIQUE ，關鍵字 PRIMARY KEY ，按條件檢查 CHECK (條件)， 外鍵 REFERENCES 表名(字段名)

3. 創(chuàng)建表的例子

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. 創(chuàng)建表時的命名規(guī)則和注意事項

1)表名和字段名的命名規(guī)則：必須以字母開頭，可以含符號A-Z,a-z,0-9,_,$,#

2)大小寫不區(qū)分

3)不用SQL里的保留字, 一定要用時可用雙引號把字符串括起來

4)用和實體或?qū)傩韵嚓P的英文符號長度有一定的限制

5）約束名的命名規(guī)則和語法 約束名的命名規(guī)則約束名如果在建表的時候沒有指明，系統(tǒng)命名規(guī)則是SYS_Cn(n是數(shù)字) 約束名字符串的命名規(guī)則同于表和字段名的命名規(guī)則

6）使用約束時的注意事項 約束里不能用系統(tǒng)函數(shù),如SYSDATE和別的表的字段比較 可以用本表內(nèi)字段的比較

注意事項:

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

2)創(chuàng)建表時要把較小的不為空的字段放在前面, 可能為空的字段放在后面

3)建表時如果有唯一關鍵字或者唯一的約束條件，建表時自動建了索引

4)一個表的最多字段個數(shù)也是有限制的，254個.

想在事務處理后, 做約束的

檢查 SQL> alter session set constraints deferred.

7. 由實體關系圖到創(chuàng)建表的例子 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. 較復雜的創(chuàng)建表例子 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)可以關連多個表及用集合函數(shù)生成新表,注意選擇出來的字段必須有合法的字段名稱,且不能重復。 2)用子查詢方式建立的表，只有非空NOT NULL的約束條件能繼承過來, 其它的約束條件和默認值都沒有繼承過來. 3)根據(jù)需要，可以用alter table add constraint ……再建立其它的約束條件，如primary key等.

11. Foreign Key的可選參數(shù)ON DELETE CASCADE 在創(chuàng)建Foreign Key時可以加可選參數(shù): ON DELETE CASCADE它的含義是如果刪除外鍵主表里的內(nèi)容，子表里相關的內(nèi)容將一起被刪除. 如果沒有ON DELETE CASCADE參數(shù)，子表里有內(nèi)容，父表里的主關鍵字記錄不能被刪除掉.

12. 如果數(shù)據(jù)庫表里有不滿足的記錄存在，建立約束條件將不會成功.

13. 給表創(chuàng)建和刪除同義詞的例子 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之新建表

創(chuàng)建一個名為INSURES的表

create table INSURES

(

 INSURE_NO                CHAR(18) not null,          --醫(yī)保號

 GETSURE_UNIT_NO          CHAR(9) not null,           --經(jīng)辦機構號

 INSURE_NAME              VARCHAR2(10) not null,      --姓名

 INSURE_SEX               CHAR(1) not null,           --性別

 ID_CARD_NO               CHAR(18) not null,          --身份證號

);

創(chuàng)建/修改主鍵，唯一性約束和外鍵   這里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 (。。。。)

創(chuàng)建索引

create index IDX_INSURES on INSURES (GETSURE_UNIT_NO, SONSURE_UNIT_NO, UNIT_NO, FAMILY_NO, HOSPS_NO)

創(chuàng)建一個PK的時候，是自動創(chuàng)建一個與之對應的唯一索引的。  如果不特別指定，那么這個索引的表空間和表格的空間是一樣的，但是我們不建議放在一起。

create table testone(

name varchar2(10 char))

TABLESPACE1;

ALTER TABLE TESTONE ADD CONSTRAINT  PK_TESTONE1 PRIMARY KEY(NAME) USING INDEX TABLESPACE TABLESPACE2;

作為一種好習慣，不要把索引和表格的數(shù)據(jù)存在在同一個表空間中

Oracle創(chuàng)建表語法 - create

--創(chuàng)建Oracle表（使用create關鍵字）

-- ******    

（1）創(chuàng)建新表 use 數(shù)據(jù)庫（在那個數(shù)據(jù)庫中建表） create table 表名

( 字段名1（列名） 數(shù)據(jù)類型 列的特征,

字段名2（列名） 數(shù)據(jù)類型 列的特征(NOT NULL),

...... )

（2）創(chuàng)建帶有主鍵約束的表語法 create table 表名 (

字段名1（列名） 數(shù)據(jù)類型 列的特征,

字段名2 數(shù)據(jù)類型 列的特征(NOT NULL),

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

（3）利用現(xiàn)有的表創(chuàng)建表 -- 注意：僅復制Oracle數(shù)據(jù)表結構：采用的是子查詢方式 create table 新表 as select * from 舊的表 where 1=2
（4）利用現(xiàn)有的表的結構創(chuàng)建新表 -- 注意：僅復制Oracle數(shù)據(jù)表結構：采用的是子查詢方式 create table 新表 select 字段1,字段2... from 舊的表 where 條件（舊的表字段滿足的條件）
（5）利用現(xiàn)有的表的結構創(chuàng)建新表 -- 注意：復制Oracle數(shù)據(jù)表數(shù)據(jù) create table 新表 as select * from 舊的表 where 1=1
（6）利用現(xiàn)有的表的結構創(chuàng)建新表  -- 注意：復制Oracle數(shù)據(jù)表數(shù)據(jù) create table 新表 as select 字段1,字段2... from 舊的表 where 條件（舊的表字段滿足的條件）（7）將查詢結果插入另一張表 insert into 另一張表     select * from 要查詢的表 where 條件（要查詢的表的列符合什么條件）

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. 修改列的數(shù)據(jù)類型  

•       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.清除表中所有數(shù)據(jù)  

•   truncate table employee_info; (DELETE FROM table_name或DELETE * FROM table_name) 

• 
  

• 11.刪除表  

•   drop table employee_info;  

• --   

• //下面來看看剛剛才我們對表dept和表employee_info所做的更改  

• //user_constraints視圖里面包含了剛剛才我們創(chuàng)建的所有約束，以及其他信息，  

• //你可以用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 --創(chuàng)建數(shù)據(jù)表               no              number(5)            not null ,        --pk               (列名或字段名) 數(shù)據(jù)類型(數(shù)據(jù)長度) 該數(shù)據(jù)列不能為空 ,是列之間的分隔符 --后的內(nèi)容是注釋                   constraint pk_connectdb primary key(no)           (約束)    約束名      (主鍵)    (列名)    意思就是 在數(shù)據(jù)表 test_user中所有行數(shù)據(jù) no的值不能相同(這就是主鍵的含義)

          storage (initial 10k next 10k pctincrease 0); 這個說起來比較復雜, 反正如果某個數(shù)據(jù)表要存放大量數(shù)據(jù)，就把initial和next后的值設置大一點, 否則設置小一點.

          既然上面在創(chuàng)建數(shù)據(jù)表中沒有特別指定 表空間，當然該表就存放在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 查詢?yōu)槟硞€用戶授予的，在其他模式對象上的權限          select * from user_tab_privs_recd --假如當前登錄用戶為WENZI，那么查詢結果就是WENZI在其他模式對象上的權限

5.4 查詢?yōu)槟硞€用戶授予的，在該用戶模式對象與其他模式對象上的權限    select * from all_tab_privs_recd --假如當前用戶為wenzi，則查詢結果為wenzi在整個數(shù)據(jù)庫中擁有權限的對象

角色管理

創(chuàng)建口令文件 orapwd file='..........\pwd{SID}.ora' password='***(sys的密碼)' tntries=10(口令文件最大的用戶數(shù)量)

要使某個用戶可以使用口令文件，必須為其授予SYSDBA權限，系統(tǒng)會自動將其加入到口令文件中。 grant sysdba to wenzi 當收回SYSDBA權限時，系統(tǒng)將對應的用戶從口令文件中刪除。 revoke sysdba from wenzi

查看口令文件管理的用戶 select * from v$pwfile_users

創(chuàng)建步驟：

   SQL> conn sys/h1w2D3B4 as sysdba

   SQL> startup

   [user@root ~]$ lsnrctl start

// 分為四步

//第1步：創(chuàng)建臨時表空間  

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步：創(chuàng)建數(shù)據(jù)表空間  

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步：創(chuàng)建用戶并指定表空間  

create user username identified by password  

default tablespace user_data  

temporary tablespace user_temp;  

//第4步：給用戶授予權限  

 grant connect,resource to username;  

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

//以后以該用戶登錄，創(chuàng)建的任何數(shù)據(jù)庫對象都屬于user_temp 和user_data表空間，

這就不用在每創(chuàng)建一個對象給其指定表空間了 

撤權：  

 revoke   權限...   from  用戶名;

刪除用戶命令

drop user user_name cascade;

建立表空間

CREATE TABLESPACE data01

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

UNIFORM SIZE 128k; #指定區(qū)尺寸為128k,如不指定，區(qū)尺寸默認為64k

刪除表空間

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

一、建立表空間

CREATE TABLESPACE data01

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

UNIFORM SIZE 128k; #指定區(qū)尺寸為128k,如不指定，區(qū)尺寸默認為64k

二、建立UNDO表空間

CREATE UNDO TABLESPACE UNDOTBS02

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

#注意：在OPEN狀態(tài)下某些時刻只能用一個UNDO表空間，如果要用新建的表空間，必須切換到該表空間:

ALTER SYSTEM SET undo_tablespace=UNDOTBS02;

三、建立臨時表空間

CREATE TEMPORARY TABLESPACE temp_data

TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M

四、改變表空間狀態(tài)

1.使表空間脫機

ALTER TABLESPACE game OFFLINE;

如果是意外刪除了數(shù)據(jù)文件，則必須帶有RECOVER選項

ALTER TABLESPACE game OFFLINE FOR RECOVER;

2.使表空間聯(lián)機

ALTER TABLESPACE game ONLINE;

3.使數(shù)據(jù)文件脫機

ALTER DATABASE DATAFILE 3 OFFLINE;

4.使數(shù)據(jù)文件聯(lián)機

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.增加數(shù)據(jù)文件

ALTER TABLESPACE game

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

2.手動增加數(shù)據(jù)文件尺寸

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

RESIZE 4000M;

3.設定數(shù)據(jù)文件自動擴展

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

創(chuàng)建數(shù)據(jù)表：

Oracle中建表和指定表空間

--建一個表 create table HH2(

tid number primary key ,--主鍵設定

tname varchar2(20)

);

--刪除表 drop table HH;

--表空間（相當于一個數(shù)據(jù)庫）(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 --連帶物理文件和表空間中的數(shù)據(jù)也一起刪除

--建表建約束 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.創(chuàng)建oracle數(shù)據(jù)表

創(chuàng)建oracle數(shù)據(jù)表的語法如下：

create table命令用于創(chuàng)建一個oracle數(shù)據(jù)表；括號內(nèi)列出了數(shù)據(jù)表應當包含的列及列的數(shù)據(jù)類型；tablespace則指定該表的表空間。

創(chuàng)建數(shù)據(jù)表students。

在該創(chuàng)建語句中，依次定義了student_id、student_name、student_age、status及version等列；tablespace users表示將表創(chuàng)建于表空間users中。

通過視圖user_tables可以獲得當前用戶所擁有的表信息，利用如下SQL語句可以查看表student的表空間信息。

在oracle的SQL命令行下，可以利用describe命令來查看已有數(shù)據(jù)表的表結構，如下所示。

2.數(shù)據(jù)表的相關操作

數(shù)據(jù)表創(chuàng)建之后，由于某些原因，例如，設計時的考慮不足，往往需要對其進行結構上的調(diào)整。常見的調(diào)整包括，增加新列、修改已有列、刪除、重命名已有列。另外，還可以轉移數(shù)據(jù)表的表空間。

修改數(shù)據(jù)表結構應當使用alter table命令。例如，在表student中，增加新列class_id(班級ID)的SQL語句如下圖所示。

alter table student 用于修改表student的結構；add用于增加列，注意此處沒有collumn關鍵字；小括號內(nèi)是列及列的數(shù)據(jù)類型；用戶可以一次性為表增加多個列，各列之間使用逗號進行分隔。

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

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

利用alter命令將class_id的數(shù)據(jù)類型修改為varchar2(20)。

modify(class_id varchar2(20))用于修改表student中的已有列class_id，實際相當于重新定義。該列新的數(shù)據(jù)類型為varchar2(20)。此時表student的結構如下：

通過modify選項可以將列的類型重新定義，而通過drop選項則刪除已有列，如下所示。

drop column class_id用于刪除已有列class_id；需要注意的是，此處必須添加column選項，才能表示刪除的目標是一個列。此時，表student的結構已經(jīng)修改如下：

對于數(shù)據(jù)表的列，除了增、刪、改操作之外，還可以進行重命名操作。重命名一個列，應該使用rename選項。例如，為了與其他數(shù)據(jù)表進行統(tǒng)一，需要將表student的列student_id重命名為id，則可以利用如下SQL語句。

rename column student_id to id用于將列student_id重命名為id。此時，表student的結構如下所示。

對于調(diào)整數(shù)據(jù)表結構來說，要特別注意嚴謹性。列的數(shù)據(jù)類型的修改，有可能會影響應用程序?qū)?shù)據(jù)庫進行存??；而列的刪除和重命名更需要檢查應用程序是否會出現(xiàn)關聯(lián)性錯誤。

如果數(shù)據(jù)表創(chuàng)建時，選擇了錯誤的表空間，那么可以利用alter table命令，結合move tablespace選項轉移表空間，如下圖所示。

將表student轉移至表空間users中。

alter table student用于修改表student的屬性；move tablespace users用于將表student從當前表空間轉移至表空間users。可以通過查詢視圖user_tables獲得表student轉移之后的表空間信息，如下所示。

分析查詢結果可知，利用move tablespace選項已經(jīng)成功實現(xiàn)了表空間的轉移。

刪除數(shù)據(jù)表

利用drop table命令刪除數(shù)據(jù)表

有時，由于某些約束的存在，例如，當前表的主鍵被其他表用作外鍵，會導致無法成功刪除。利用cascade constraints選項可以將約束同時刪除，從而保證drop table命令一定能夠成功執(zhí)行。

特殊的數(shù)據(jù)表dual

dual表實際屬于系統(tǒng)用戶sys，具有了數(shù)據(jù)庫基本權限的用戶，均可查詢該表的內(nèi)容，如下所示：

或者

分析查詢結果可知，dual表僅含有一行一列。該表并非為了存儲數(shù)據(jù)而創(chuàng)建的，其存在的意義在于提供強制的數(shù)據(jù)源。

在oracle中，所有查詢語句必須滿足select column_name from table_name的格式。但是，在某些場景下，數(shù)據(jù)源table_name并不明確。例如，函數(shù)sysdate()用于返回當前日期，那么在SQL命令行下調(diào)用該函數(shù)時，很難有明確的數(shù)據(jù)源，此時即可使用dual表。

利用dual表提供數(shù)據(jù)源，以獲得當前日期。

同樣，對于各種數(shù)學運算，同樣可以利用dual表作為數(shù)據(jù)源，來打印和查看輸出結果。

dual表提供了一行一列的數(shù)據(jù)格式，從而使各種表達式、函數(shù)運算在以其為數(shù)據(jù)源時，能夠輸出單行單列的形式。

由于dual表的所有者為系統(tǒng)用戶sys，因此，只有用戶利用該身份登錄數(shù)據(jù)庫才可以修改該表。但是，修改該表的內(nèi)容或者結構都應該被禁止。

oracle create tablespace

create tablespace ZGECM   
 logging datafile 'G:\oracle\product\10.2.0\ZGECM.DBF'  
 size 500M autoextend on  
 next 10M maxsize 500M extent management local  
  
  
create user sms2 identified by sms2 default tablespace ECM2   
temporary tablespace temp profile default;  
  
--6 授權給新建用戶  
grant connect,resource to sms2;  
grant dba to sms2;  
  
  
exp xuner_ecm/xuner_ecm@192.168.1.139/SMS FILE=E:/xunerecm0910_server.DMP OWNeR=xuner_ecm  
  
imp scjt/scjt@SMS fromuser=ecm touser=sms2 file='G:\sms.dmp'   
  
  
imp SMS/SMS@LOCAL_SMS fromuser=SMS touser=SMS file='G:\sms.dmp'    
  
select userenv('language') from dual;     //查詢oracle服務器端的字符  
  
select nls_charset_name(to_number('0354','0354'))  
 from dual;  
  
  
  
CREATE DATABASE LINK sms CONNECT TO sms IDENTIFIED BY sms  
USING 'SMS';  
  
  
  
insert into XSJBXXB select * from sms.xsjbxxb@zhang2  
  
insert into XSJBXXB select * from sms.xsjbxxb@to_test  
  
--創(chuàng)建dblink  
　create database link to_test  
　　connect to sms identified by sms  
　　using 'ZHANG';  
  
'(DESCRIPTION =(  
ADDRESS_LIST =(  
ADDRESS =(  
PROTOCOL = TCP)(  
HOST = 192.168.0.100)(PORT = 1521)))  
(CONNECT_DATA =(SERVICE_NAME = AAA)))'  
  
--查詢約束  
select owner,constraint_name,table_name from user_constraints   
where constraint_name='SYS_C00119759'  
  
  
  
-----------------------------------------------------  
  
  
/*分為四步 */  
/*第1步：創(chuàng)建臨時表空間  */  
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步：創(chuàng)建數(shù)據(jù)表空間  */  
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步：創(chuàng)建用戶并指定表空間  */  
create user username identified by password    
default tablespace user_data    
temporary tablespace user_temp;    
   
/*第4步：給用戶授予權限  */  
grant connect,resource,dba to username;  
  
  
--Error dropping MEM_GENINF:  
--ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源   
--Record is locked by another user  
  
--1.查看鎖  
select t2.username,t2.sid,t2.serial#,t2.logon_time   
from v$locked_object t1,v$session t2 where t1.session_id=t2.sid ;   
  
--2、Kill   
alter system kill session 'sid,serial#';   
alter system kill session '151,14678';

Oracle11g 腳本創(chuàng)建表空間和用戶

/*分為四步 */
/*第1步：創(chuàng)建臨時表空間 */
create temporary tablespace emaoyi_temp 
tempfile 'D:\app\Administrator\product\11.2.0\dbhome_1\database\emaoyi_temp.dbf' 
size 10m 
autoextend on 
next 10m maxsize 20480m 
extent management local; 

/*第2步：創(chuàng)建數(shù)據(jù)表空間 */
create tablespace emaoyi 
logging 
datafile 'D:\app\Administrator\product\11.2.0\dbhome_1\database\emaoyi.dbf' 
size 10m 
autoextend on 
next 10m maxsize 20480m 
extent management local; 

/*第3步：創(chuàng)建用戶并指定表空間 */
create user emaoyi identified by emaoyi 
default tablespace emaoyi
temporary tablespace emaoyi_temp; 

/*第4步：給用戶授予權限 */
grant connect,resource,dba to emaoyi;

--查詢當前表空間
select tablespace_name,file_id,file_name,bytes
from dba_data_files
order by file_id

where tablespace_name='emaoyi'

--刪除表空間
drop tablespace em;
--刪除用戶
drop user e [CASCADE];

• 空間
• Oracle
• 用戶