Oralce常用SQL指令集

轉：http://www.javaeye.com/topic/431907

 

一、系統的預設使用者 

 

1. sys;//系統管理員，擁有高許可權     
2. system;//本地管理員，次高許可權     
3. scott;//普通使用者，密碼預設為tiger,預設未解鎖   

二、登陸 

1. sqlplus / as sysdba;//登陸sys帳戶     
2. sqlplus sys as sysdba;//同上     
3. sqlplus scott/tiger;//登陸普通使用者scott  

三、系統管理使用者 

1. create user zhangsan;//在系統管理員帳戶下，建立使用者zhangsan     
2. alert user scott identified by tiger;//修改密碼  

四，授予許可權 
1、預設的普通使用者scott預設未解鎖，不能進行那個使用，建立的使用者也沒有任何許可權，必須授予許可權 

1. /*管理員授權*/    
2. grant create session to zhangsan;//授予zhangsan使用者建立session的許可權，即登陸許可權     
3. grant unlimited session to zhangsan;//授予zhangsan使用者使用資料表空間的許可權     
4. grant create table to zhangsan;//授予建立表的許可權     
5. grante drop table to zhangsan;//授予刪除表的許可權     
6. grant insert table to zhangsan;//插入表的許可權     
7. grant update table to zhangsan;//修改表的許可權     
8. grant all to public;//這條比較重要，授予所有許可權(all)給所有使用者(public)    

2、oralce對許可權管理比較嚴謹，普通使用者之間也是預設不能互相訪問的，需要互相授權 

1. /*oralce對許可權管理比較嚴謹，普通使用者之間也是預設不能互相訪問的*/    
2. grant select on tablename to zhangsan;//授予zhangsan使用者查看指定表的許可權     
3. grant drop on tablename to zhangsan;//授予刪除表的許可權     
4. grant insert on tablename to zhangsan;//授予插入的許可權     
5. grant update on tablename to zhangsan;//授予修改表的許可權     
6. grant insert(id) on tablename to zhangsan;     
7. grant update(id) on tablename to zhangsan;//授予對指定表特定欄位的插入和修改許可權，注意，只能是insert和update     
8. grant alert all table to zhangsan;//授予zhangsan使用者alert任意表的許可權    

五、撤銷許可權 

1. 基本文法同grant,關鍵字為revoke  

六、查看許可權 

1. select * from user_sys_privs;//查看目前使用者所有許可權     
2. select * from user_tab_privs;//查看所用使用者對錶的許可權    

七、動作表的使用者的表 

1. /*需要在表名前加上使用者名稱，如下*/    
2. select * from zhangsan.tablename    

八、許可權傳遞 
即使用者A將許可權授予B，B可以將操作的許可權再授予C，命令如下： 

1. grant alert table on tablename to zhangsan with admin option;//關鍵字 with admin option     
2. grant alert table on tablename to zhangsan with grant option;//關鍵字 with grant option效果和admin類似    

九、角色 
角色即許可權的集合，可以把一個角色授予給使用者 

1. create role myrole;//建立角色     
2. grant create session to myrole;//將建立session的許可權授予myrole     
3. grant myrole to zhangsan;//授予zhangsan使用者myrole的角色      
4. drop role myrole;刪除角色     
5. /*但是有些許可權是不能授予給角色的，比如unlimited tablespace和any關鍵字*/    

 

 

 

 

尋找資料庫中所有欄位  以對應的表 
select C.column_name,C.TABLE_NAME from dba_tab_columns C where owner=‘’

查每個科目class 分數scro前三名 
select id, name, class, scro 
   from (select row_number() over(partition by class order by scro desc)  cnt, 
                id, 
                name, 
                class, 
                scro 
           from student) a 
  where a.cnt <= 3; 

尋找排序後的前三行 
  select * 
  from (select rw.*, rownum 
          from (select * 
                  from student d 
                 where d.class = 'b' 
                 order by d.scro desc) rw 
         where rw.id >= 1 
         order by rw.class desc) n 
where rownum <= 3 

表複製 
insert into table_a (id,name,age) select b.id,b.name,b.age from table_b; 

--刪除表資料的觸發器 
CREATE OR REPLACE PROCEDURE delete_data 
IS 
BEGIN 
   delete from test ; 
   COMMIT; 
EXCEPTION 
   WHEN NO_DATA_FOUND 
   THEN 
      NULL; 
   WHEN OTHERS 
   THEN 
      RAISE; 
END delete_data; 

--定時刪除  每隔5分鐘執行一次的計劃 
DECLARE 
  X NUMBER; 
BEGIN 
  SYS.DBMS_JOB.SUBMIT 
  ( job       => X 
   ,what      => 'delete_data;' 
   ,next_date => to_date('25/08/2008 00:00:00','dd/mm/yyyy hh24:mi:ss') 
   ,interval  => 'sysdate+1/24/12' 
   ,no_parse  => FALSE 
  ); 
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); 
COMMIT; 
END; 

--查看當前oracle計劃 
select job,next_date,next_sec,failures,broken from user_jobs; 

--刪除一個job 
begin 
dbms_job.remove(46);--46為job號 
end; 

--給新表插入舊錶對應欄位的所有資料 
insert into aaa(id,name) select b.id,b.name from bbb; 

文章搜尋：     【點擊打包該文章】 

select * from v$version; 查看伺服器版本
select * from all_users;                ##查看所有使用者 
select name from v$database;             ##查看當前資料庫 
database test;                    ##進入test資料庫 
select * from v$instance;                ##查看所有的資料庫執行個體 
shutdown immediate                ##關閉資料庫 
alter user sys identified by new_password;        ##更改使用者密碼 
select username,password from dba_users; ##查看當執行個體中的使用者和密碼 
show parameter control_files;           ## 查看控制檔案; 
select member from v$logfile;        ##查看記錄檔 
show parameter ;                 ## 查看資料庫參數 
select * from user_role_privs;        ##查看目前使用者的角色 
select username,default_tablespace from user_users; ##查看目前使用者的預設資料表空間 
alter user system identified by [password]        ##修改使用者的密碼 
ALTER USER "SCOTT" ACCOUNT UNLOCK                 ##解鎖SCOTT使用者 
show parameter processes;                    ##查看最大會話數 

 select username,sid,serial# from v$session;  查看當前會話串連

select * from all_tables;  ##查看當前庫的所有資料表
 

查看錶結構：desc all_tables; 

建立使用者並賦予許可權 
###----------------------------建立使用者並賦予許可權------------------------------------####- 
create user mpss 
  identified by "mpss12" 
  default tablespace TS_MPSS_DATA 
  temporary tablespace TEMP; 
  
  給使用者賦予許可權 
grant connect to mpss; 
grant resource,create session to mpss;  開發角色 
grant create procedure to dbuser； ＃這些許可權足夠用於開發及生產環境 

給使用者授權 
grant dba to spms;--授予DBA許可權 
grant unlimited tablespace to lxg;--授予不限制的資料表空間 
grant select any table to lxg;--授予查詢任何錶 
grant select any dictionary to lxg;--授予 查詢 任何字典 

刪除使用者 
drop user mpss cascade; 

建資料表空間 

CREATE TABLESPACE "TS_MPSS_DATA" 
    LOGGING 
    DATAFILE '/mpss/data/ts_mpss_data.dbf' SIZE 1024M 
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO 

建立暫存資料表空間

CREATE 
    TEMPORARY TABLESPACE "SWVIP" TEMPFILE '/app/oracle/oradata/ 
    sworacle/SWVIP.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM 
    SIZE 1M 

查看錶空間 
###----------------------------查看錶空間大小------------------------------------####- 
SELECT D.TABLESPACE_NAME "Name", 
TO_CHAR(((((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024)) /(A.BYTES / 1024 / 1024))*100,'99,990.9')  "used(%)", 
TO_CHAR((DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024),'999,990.9') "Free (M)" 
FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F 
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME 
AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME; 
###--------------------------------------------------------------------------------------####- 

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", 
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)" 
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS 
FROM DBA_DATA_FILES 
GROUP BY TABLESPACE_NAME) D, 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE 
FROM DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME) F 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
UNION ALL --if have tempfile 
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)", 
NVL(FREE_SPACE,0) "FREE_SPACE(M)" 
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS 
FROM DBA_TEMP_FILES 
GROUP BY TABLESPACE_NAME) D, 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE, 
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE 
FROM V$TEMP_SPACE_HEADER 
GROUP BY TABLESPACE_NAME) F 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ; 

  查看錶空間物理檔案的名稱及大小; 
  
###--------------------資料表空間物理檔案的名稱及大小------------------------####- 
  select tablespace_name, file_id, file_name, 
    round(bytes/(1024*1024),0) total_space 
    from dba_data_files 
    order by tablespace_name; 
###------------------------------------------------------------------------------------####-   
    

查看資料檔案放置的路徑 
###------------------------------------------------------------------------------------####-   

SQL> col file_name format a50 
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 
###------------------------------------------------------------------------------------####-   

 查看資料庫庫對象 

   select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status; 

擴資料表空間 
###------------------------------------------------------------------------------------####- 
alter tablespace G000 add datafile '/dev/vgbilling/rg000_lv03' SIZE 7500m; 
給表G000增加一個7500m的邏輯卷'/dev/vgbilling/rg000_lv03' 
###------------------------------------------------------------------------------------####- 

檢查被長時間鎖的對象 
###------------------------------------------------------------------------------------####- 

SQL>select a.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status from v$locked_object a,dba_objects b where a.object_id=b.object_id; 
###------------------------------------------------------------------------------------####- 

--建立視圖給hy使用者（用leon使用者） 

 

create or replace view view_tableName as 

select column。。。 from table; 

 

-- Grant/Revoke object privileges 

grant select, update on RES_XIM_CARD to hy; 

 

--建立同義字 

create synonym RES_XIM_CARD for YY.RES_XIM_CARD ; 

sys使用者登陸 
建立資料表空間： 
SQL> create tablespace lmsstemp datafile 'F:/ORADATA/LMSS/LMSSTEMP01.DBF' SIZE 1 
024M extent management local; 

給資料表空間增加資料檔案 
alter tablespace lmsstemp add datafile 'F:/ORADATA/LMSS/LMSSTEMP02.DBF' SIZE 1 
024M; 

更改資料表空間為自動擴充 
SQL> alter database datafile 'F:/ORADATA/LMSS/LMSSTEMP01.DBF' autoextend on; 

查看錶空間資訊 
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;