1、 Oracle的系統(tǒng)常用表

• 場景：用最多的場景如: 代碼生成器開發(fā)或?qū)?code>SQL腳本語句創(chuàng)建表或索引睡毒、視圖、寫存儲過程冗栗、改變表結(jié)構(gòu)(擴展字段演顾，修改字段，修改字段長度)等隅居。

2、語法SQL使用

--第一種方法: 查詢dba_tab_columns

select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where table_name =upper('表名') order by COLUMN_NAME

--這種方法需要有DBA權(quán)限

--第二種方法: 查詢 user_tab_cols
select  COLUMN_NAME,DATA_TYPE,DATA_LENGTH   from  user_tab_cols   where table_name=upper('表名')

order by COLUMN_NAME
--這種方法只能查找當前用戶下的表

--第三種方法: 查詢ALL_TAB_COLUMNS
select distinct COLUMN_NAME,DATA_TYPE,DATA_LENGTH 
from ALL_TAB_COLUMNS
WHERE TABLE_NAME= upper('表名')
--這種方法可以查詢所有用戶下的表

---------------------------補充-------------------------------------------------------------

--增加cw_srcbpb表字段
alter table cw_srcbpb  add (SRCBPB_RJBPBL varchar2(100) );
--修改cw_srcbpb表字段
alter table cw_srcbpb  modify (SRCBPB_RJBPBL number(30,3) );

--Oracle查看所有表和字段

--獲取表：

select table_name from user_tables; --當前用戶的表       
select table_name from all_tables; --所有用戶的表   
select table_name from dba_tables; --包括系統(tǒng)表
select table_name from dba_tables where owner='LBSP'; --獲取用戶***所擁有的表這里的用戶名要記得是用大寫的胎源。
-- 獲取表字段：其實這里是根據(jù)用戶的權(quán)限來獲取字段的屬性（表名要大寫）
select * from user_tab_columns where Table_Name='用戶表';--獲取用戶表的所有字段還有字段的屬性棉钧。
select * from all_tab_columns where Table_Name='用戶表';--獲取用戶表的所有字段還有字段的屬性。
select * from dba_tab_columns where Table_Name='用戶表';--獲取用戶表的所有字段還有字段的屬性涕蚤。

--獲取表注釋：
select * from user_tab_comments
--user_tab_comments：table_name,table_type,comments
--相應的還有dba_tab_comments宪卿，all_tab_comments，這兩個比user_tab_comments多了ower列万栅。

--獲取字段注釋：
select * from user_col_comments
--user_col_comments：table_name,column_name,comments
--相應的還有dba_col_comments佑钾，all_col_comments，這兩個比user_col_comments多了ower列烦粒。
--查詢出用戶所有表的索引
select   *   from   user_indexes
--查詢用戶表的索引(非聚集索引): 
select   *   from   user_indexes where   uniqueness='NONUNIQUE'
--查詢用戶表的主鍵(聚集索引): 
select   *   from   user_indexes where   uniqueness='UNIQUE' 
--查詢表的索引
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and
t.table_name='NODE'
--查詢表的主鍵
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and
au.constraint_type = 'P' AND cu.table_name = 'NODE'
--查找表的唯一性約束（包括名稱休溶，構(gòu)成列）： 
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name=au.constraint_name and
cu.table_name='NODE'
--查找表的外鍵
select * from user_constraints c where c.constraint_type = 'R' and c.table_name='STAFFPOSITION';
--查詢外鍵約束的列名： 
select * from user_cons_columns cl where cl.constraint_name = '外鍵名稱';
--查詢引用表的鍵的列名：
select * from user_cons_columns cl where cl.constraint_name = '外鍵引用表的鍵名';

--刪除表空間
drop tablespace AHTBSPACETEST including contents and datafiles cascade constraints
--查詢當前索引的狀態(tài)：
select distinct index_name,table_owner,table_name,status from user_indexes

3、結(jié)合[圖片上傳失敗...(image-96c373-1556882948303)]

聲明聲明式高級用法

3.1 創(chuàng)建表：以T_CLASS班級表和T_STUDENT學生表為主

• 關鍵字： [圖片上傳失敗...(image-993e49-1556882948301)]

  執(zhí)行 扰她、[圖片上傳失敗...(image-edd152-1556882948301)]

  即時（馬上） 兽掰、[圖片上傳失敗...(image-e88487-1556882948301)]

  開始 、[圖片上傳失敗...(image-7b2d7c-1556882948301)]

  結(jié)束义黎、[圖片上傳失敗...(image-1d6b40-1556882948301)]

  如果 禾进、[圖片上傳失敗...(image-41f705-1556882948301)]

  結(jié)束語句 [圖片上傳失敗...(image-5dc867-1556882948301)]

  關鍵字的使用，而commit一般SQL腳本只有一個

• 函數(shù)：[圖片上傳失敗...(image-faeb43-1556882948301)]

  轉(zhuǎn)換小寫

• 系統(tǒng)表：user_tables

• 注意：結(jié)束以 【/ 斜桿】結(jié)束

declare
    iCnt number := 0; 
begin 
    select count(*) into iCnt from user_tables  where  lower(table_name) = lower('T_CLASS'); 
    if iCnt = 0 then -- 如果查詢不到這個表就創(chuàng)建這個表
    execute immediate  'create table T_CLASS 
    (
        CLASS_ID    NUMBER(11) NOT NULL ,
        CLASS_NAME    VARCHAR2(50) NOT NULL,
        CLASS_NO    VARCHAR2(30) NOT NULL,
        CLASS_NUM    NUMBER(10) NOT NULL
    )'; 
   --- 對表和表屬性加標注
    execute immediate 'comment on table T_CLASS is ''班級表''';
    execute immediate 'alter table T_CLASS add constraint PK_CLASS_ID primary key (CLASS_ID)' ;
    execute immediate 'comment on column T_CLASS.CLASS_NAME is ''班級名稱''';
    execute immediate 'comment on column T_CLASS.CLASS_NO is ''班級編號''';
    execute immediate 'comment on column T_CLASS.CLASS_NUM is ''班級人數(shù)''';
    end if; 
end; 
/

declare
    iCnt number := 0; 
begin 
    select count(*) into iCnt from user_tables  where  lower(table_name) = lower('T_STUDENT'); 
    if iCnt = 0 then -- 如果查詢不到這個表就創(chuàng)建這個表
    execute immediate  'create table T_STUDENT 
    (
        USER_ID    NUMBER(11) NOT NULL ,
        ID_CARD_CODE    VARCHAR2(50) NOT NULL,
        USER_NAME    VARCHAR2(30) NOT NULL,
        PASS_WORD    VARCHAR2(30) NOT NULL,
        AGE    NUMBER(10)  NOT NULL,
        ADDRESS    VARCHAR2(255) ,
        CREATE_TIME    DATE
    )'; 
    execute immediate 'comment on table T_STUDENT is ''學生用戶表''';
    execute immediate 'alter table T_STUDENT add constraint PK_USER_ID primary key (USER_ID)' ;
    execute immediate 'comment on column T_STUDENT.ID_CARD_CODE is ''身份證編號''';
    execute immediate 'comment on column T_STUDENT.USER_NAME is ''用戶名''';
    execute immediate 'comment on column T_STUDENT.PASS_WORD is ''用戶密碼''';
    execute immediate 'comment on column T_STUDENT.AGE is ''年齡''';
    execute immediate 'comment on column T_STUDENT.ADDRESS is ''地址''';
    execute immediate 'comment on column T_STUDENT.CREATE_TIME is ''創(chuàng)建時間''';
    end if; 
end; 
/

3.2 創(chuàng)建索引

• 關鍵字： [圖片上傳失敗...(image-5125c4-1556882948301)]

  廉涕、[圖片上傳失敗...(image-47422b-1556882948301)]

• 系統(tǒng)表： user_ind_columns

--對學生表的身份證ID加唯一索引ID_CARD_CODE
declare
  iCnt number := 0;
begin
  select count(*) into iCnt from user_ind_columns  where  lower(index_name) = lower('stu_idx_card_code') and lower(table_name)=lower('T_STUDENT');
  if iCnt = 0 then  
     execute immediate 'create unique index stu_idx_card_code on T_STUDENT(id_card_code)'; 
  end if;
end;
/

--創(chuàng)建學生表序列Id     
declare
  iCount integer;
begin
  select count(*) into iCount from user_sequences where upper(sequence_name)=upper('SQ_T_STUDENT');
  if iCount = 0 then
    execute immediate
      'create sequence SQ_T_STUDENT
        minvalue 1
        maxvalue 999999999999999999999999999
        start with 1
        increment by 1
        nocache';
  end if;
end;
/   

3.3 刪除索引

• 關鍵字： [圖片上傳失敗...(image-584c3d-1556882948301)]

  泻云、[圖片上傳失敗...(image-ac7a59-1556882948301)]

• 系統(tǒng)表： user_ind_columns

--刪除T_STUDENT的唯一索引
declare
  iCnt number := 0;
begin
  select count(*) into iCnt from user_ind_columns where lower(index_name) = lower('stu_idx_card_code') and lower(table_name)=lower('T_STUDENT');
  if iCnt > 0 then  
     execute immediate 'drop index stu_idx_card_code '; 
  end if;
end;
/

--刪除學生表序列Id     
declare
  iCount integer;
begin
  select count(*) into iCount from user_sequences where upper(sequence_name)=upper('SQ_T_STUDENT');
  if iCount > 0 then
    execute immediate  'drop sequence SQ_T_STUDENT ';
  end if;
end;
/   

3.4 創(chuàng)建視圖與刪除視圖

• 關鍵字： [圖片上傳失敗...(image-567dd9-1556882948301)]

  艇拍、[圖片上傳失敗...(image-129e36-1556882948301)]

  、[圖片上傳失敗...(image-d30a65-1556882948301)]

  宠纯、[圖片上傳失敗...(image-f9ef0d-1556882948301)]

• 系統(tǒng)表： all_views

--傳統(tǒng)創(chuàng)建視圖腳本
 CREATE OR REPLACE VIEW V_STUDENT AS
    SELECT
       user_id,
       user_name ,  
       pass_word ,  
       age,       
       create_time, 
       id_card_code
     FROM T_STUDENT
WITH READ ONLY;   --只讀視圖
--聲明式刪除視圖
declare
  iCnt number := 0;
begin 
  SELECT count(*) into iCnt  FROM all_views where upper(view_name)=upper('V_STUDENT');
  if iCnt > 0 then
    execute immediate 'drop VIEW V_STUDENT ';
  end if;
end;
/
 --聲明式創(chuàng)建視圖腳本,WITH READ ONLY表示只讀視圖
declare
  iCnt number := 0;
begin 
  SELECT count(*) into iCnt  FROM all_views where upper(view_name)=upper('V_STUDENT');
  if iCnt = 0 then
    execute immediate 'CREATE OR REPLACE VIEW V_STUDENT AS
        SELECT
          user_id,
          user_name ,  
          pass_word ,  
          age,       
          create_time, 
          ID_CARD_CODE
        FROM T_STUDENT
        WITH READ ONLY
      ';
  end if;
end;
/

4卸夕、創(chuàng)建用戶

• 四步曲 ：（其中文件位置、文件大小婆瓜、自增量快集、命名之類可按需自定義）
• 1、 創(chuàng)建臨時表空間：

create temporary tablespace MyTestSpace_Temp tempfile 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\SEAN_TEMP01.DBF'size 10m autoextend on next 10m maxsize 100m extent management local

• 2 廉白、創(chuàng)建數(shù)據(jù)表空間：

create tablespace mytest_data logging datafile 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\SEAN_DATA01.DBF' size 10m autoextend on next 10m maxsize 100m extent management local

• 3 个初、創(chuàng)建用戶并指定表空間：

create tablespace mytest_data logging datafile 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\SEAN_DATA01.DBF' size 10m autoextend on next 10m maxsize 100m extent management local

• 4 、給用戶授予權(quán)限：

grant connect,resource,dba to mytest

5猴蹂、Oracle權(quán)限與改表空間

• 1院溺、給用戶賦予DBA權(quán)限：

grant dba to mytest

• 2、撤銷用戶對所有表空間都可使用的權(quán)限（相當于撤銷用戶在表空間上的DBA權(quán)限）：

revoke unlimited tablespace from mytest

• 3 將用戶在SYSTEM表空間的配額置為0：

alter user mytest  quota 0 on SYSTEM

*4 設置用戶在mytest_data 表空間上配額不受限

alter user mytest  quota unlimited on mytest_data ;

• 5磅轻、修改表空間：

alter table TD_USERS move tablespace TABLESPACE_A // 第一步：將表TD_USERS移至表空間tablesapce_A下
alter index TD_USERS_ID rebuild tablespace TABLESPACE_A // 第二步：修改該表的索引的表空間