Oracle常用SQL語句記錄

看的東西越來越多，忘得也越來越快。把oracle相關的一些SQL語句記錄在此備查。

1. 檢視目前連接配接會話

select * from v$session;
           

2. 終止會話

alter system kill session 'sid,serial#'; -- 标記會話為終止，等待pmon程序清除會話
           

或者

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION; -- 等待事務完成後終止

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE; -- 立即終止
           

其中sid和serial#為v$session中的sid值和serial#值。

3. 檢視資料庫系統相關資訊

SELECT * FROM V$SYSSTAT;   -- 系統各種運作時間

select * from v$sesstat;   -- 系統會話所占用的時間

select * from v$librarycache;  -- 檢視庫緩沖區命中率

select * from v$library_cache_memory;  -- 檢視命名空間記憶體使用情況

select * from v$rowcache;  -- 檢視資料字典緩沖區命中率
           

4. 檢視表相關資訊

-- 檢視使用者表索引資訊
select t.*, i.index_type
  from user_ind_columns t, user_indexes i
 where t.index_name = i.index_name
   and t.table_name = i.table_name;

-- 檢視使用者表主鍵資訊
select cu.*
  from user_cons_columns cu, user_constraints au
 where cu.constraint_name = au.constraint_name
   and au.constraint_type = 'P';

-- 檢視系統中所有表主鍵資訊
select cu.*
  from all_cons_columns cu, all_constraints au
 where cu.CONSTRAINT_NAME = au.CONSTRAINT_NAME
   and au.CONSTRAINT_TYPE = 'P';

-- 檢視系統中所有表的相關資訊
select atc.owner as Owner,
       atc.TABLE_NAME as TableName,
       atc.COLUMN_NAME as 字段名,
       (case when ac.CONSTRAINT_TYPE = 'P' then 1 else 0 end) 是否主鍵,
       atc.DATA_TYPE as 類型,
       atc.DATA_LENGTH as 長度,
       atc.NULLABLE as 是否NULL
  from all_tab_cols atc
  left join all_constraints ac
    on atc.OWNER = ac.OWNER
   and atc.TABLE_NAME = ac.TABLE_NAME;

-- 檢視使用者表和列資訊
select * from user_tab_cols;

-- 檢視使用者限制列資訊
select * from user_cons_columns;

-- 檢視使用者限制資訊
select * from user_constraints;

-- 檢視系統中所有表和列資訊
select * from all_tab_cols;
           

5. 将表從一個使用者名下遷移到另一個使用者名下

create table user2.table01 as
select * from user1.table01

drop user1.table01
           

6. 鎖定使用者和解鎖使用者

alter user test account lock;     -- 鎖定test使用者
alter user scott account unlock;  -- 解鎖scott使用者
           

7. 資料導入導出

exp username/[email protected] file=C:\data.dmp tables=tablename1,tablename2  -- 資料導出
imp username/password@tnsname full=y file=C:\data.dmp ignore=y               -- 資料導入
           

這兩個指令直接在終端執行，不是SQL語句。其中username，password，tnsname和tablename1，tablename2分别為使用者名，密碼，TNS名稱，要導出的表名稱。如果不加表名稱，則導出該使用者有權限的所有表。這個方案的好處是可以在本機上導出遠端資料庫的表，然後再導入另一個遠端資料庫中，但是可能會存在字元集轉換的問題（如果是從ZHS16GBK導入AL32UTF8，可通過将每張表每個字段/列的資料類型占用空間增大解決。比如原來是varchar2(10)改成varchar2(20)）。

注意exp導出的檔案隻能由imp導入，不能由impdp導入，反過來，expdp導出的檔案也隻能由impdp導入，不能由imp直接導入。

如果需要導出符合條件的表資料，則建立xxx.par檔案，其内容如下

file=C:\data.dmp
log=C:\log.log
tables=(tablename1, tablename2)
query="where DATE>=add_months(sysdate,-4) OR DATE IS NULL"
           

然後執行

exp username/[email protected] parfile=xxx.par
           

8. 建立DBlink并将資料導入到目前資料庫中

create public database link DBLinkName 
connect to username identified by password using 'TNSname';    -- 建立DBlink
create table tableName as select * from [email protected]; -- 将資料複制到本地
           

其中DBLinkName, username, password, TNSname分别是要建立的dblink名稱，要連接配接的資料庫使用者名，密碼，TNS名稱。tableName，tableName2分别是要建立的本地表名稱和遠端資料庫上的表名稱。注意建立DBlink需要使用者有相應的系統權限（create database link）。

9. 修改字元集

SQL>CONNECT SYSTEM/systempassword as sysdba;   // 連接配接為system使用者且作為sysdba登陸
SQL>SHUTDOWN IMMEDIATE                         // 停止資料庫服務
SQL>STARTUP MOUNT;                             // 啟動mount
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; //跳過超子集檢測
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP                                     // 重新開機資料庫
           

以上指令需用sqlplus登陸資料庫後執行，但修改字元集可能導緻資料庫中原有資料顯示為亂碼，慎用。

10. 檢視單張表占用空間大小

--- 檢視真實占用空間大小（系統使用者, 比如system）
select num_rows * avg_row_len from all_tables where table_name = 'TABLE_NAME';
--- 或者（普通使用者）
analyze table TABLE_NAME compute statistics;
select num_rows * avg_row_len from user_tables where table_name = 'TABLE_NAME';

--- 檢視配置設定的空間大小
select bytes from dba_segments where segment_name = 'TABLE_NAME';
           

其中大寫的TABLE_NAME就是想查詢大小的表名。

11. 檢視之前執行過的SQL語句

--- 檢視2016年7月26日上午11點之後系統執行過的SQL語句
select * from v$sqlarea where 
to_date(first_load_time, 'yyyy-mm-dd/hh24:mi:ss') > 
to_date('2016-07-26/11:00:00', 'yyyy-mm-dd/hh24:mi:ss');
           

注意執行該語句的使用者需擁有dba權限才行。

12. in/not in

select * from A ta where ta.a not in ( select tb.b from B tb);
           

13. 建立表空間

create  tablespace db_test --表空間名
datafile 'C:\app\Administrator\oradata\orcl\TBS_FACTS.dbf' --實體檔案 表空間資料檔案存放路徑
size 50m  --大小初始值
autoextend on  --自動擴充
next 50m maxsize 10240m  --每次擴充50m，最大為10240m
           

14. 批量修改列

UPDATE tablename SET id = REPLACE(id, 'aaa', 'kkk');
           

其中tablename為表名，id為列名，假設id原始值為

aaa10

aaa20

aaa21

則此語句功能為把id列替換為

kkk10

kkk20

kkk21

版權聲明：本文為CSDN部落客「weixin_33753845」的原創文章，遵循CC 4.0 BY-SA版權協定，轉載請附上原文出處連結及本聲明。

原文連結：https://blog.csdn.net/weixin_33753845/article/details/92493848