Oracle常用操作指南

目錄

  1. 用戶操作語句
  2. 表空間操作語句
  3. 數(shù)據(jù)文件操作語句
  4. 數(shù)據(jù)表操作語句
  5. 數(shù)據(jù)庫屬性操作語句

1. 用戶操作語句

  1. 查看scott用戶的默認(rèn)表空間、臨時(shí)表空間

    select username,default_tablespace,temporary_tablespace
from dba_users
where username = 'SCOTT';

  2. 查看scott用戶的系統(tǒng)權(quán)限

    select username,privilege,admin_option 
from user_sys_privs 
where username = 'SCOTT';

  3. 查看賦予scott用戶的對象權(quán)限

    select grantee,owner, table_name, t.grantor, t.privilege, t.grantable, t.hierarchy
from dba_tab_privs t
where t.grantee = 'SCOTT' ;

  4. 查看授予了scott的角色權(quán)限

    select t.grantee,t.granted_role, t.admin_option, t.default_role
from dba_role_privs t
where t.grantee = 'SCOTT';

或者

select * from user_role_privs t

  5. 查看scott用戶使用了哪些表空間

    select t.table_name, t.tablespace_name
from  dba_all_tables t
where t.owner = 'SCOTT' ;

或者

select table_name, tablespace_name from user_tables;

  6. 查看當(dāng)前用戶擁有的權(quán)限

    select t.privilege from session_privs t

  7. 查看賦給用戶(GDYXHD)于對象操作的一些權(quán)限

    select *
from  table_privileges t1
where t1.grantee = 'GDYXHD'

  8. 用戶鎖定與解鎖

    # 解鎖
alter user scott account unlock;

# 鎖定
alter user scott account lock;

  9. 查看角色(resource)權(quán)限的 系統(tǒng)權(quán)限

    select * from role_sys_privs t1 where t1.role = 'RESOURCE'

  10. 查看角色(DBA)被賦予的 角色權(quán)限

    select * from role_role_privs t where t.role = 'DBA'

  11. 查看角色(DBA)被賦予的對象權(quán)限

    select * from role_tab_privs t1 where t1.role = 'DBA'

  12. 添加用戶及用戶授權(quán)

    # 創(chuàng)建用戶“DATACENTER”，并指定其表空間
CREATE USER DATACENTER IDENTIFIED BY VALUES 'CD47F3B2976521B1'
    DEFAULT TABLESPACE DATACENTER
    TEMPORARY TABLESPACE TEMP
    PROFILE DEFAULT
    ACCOUNT UNLOCK;

 # 修改用戶角色
ALTER USER DATACENTER DEFAULT ROLE DBA,"CONNECT";

# 用戶授權(quán)
GRANT DBA TO DATACENTER;
GRANT "CONNECT" TO DATACENTER;
GRANT INSERT ANY TABLE TO DATACENTER WITH ADMIN OPTION;
GRANT DELETE ANY TABLE TO DATACENTER WITH ADMIN OPTION;
GRANT UPDATE ANY TABLE TO DATACENTER WITH ADMIN OPTION;
GRANT DROP ANY TABLE TO DATACENTER WITH ADMIN OPTION;
GRANT SELECT ANY TABLE TO DATACENTER;
GRANT UNLIMITED TABLESPACE TO DATACENTER;
GRANT CREATE ANY SEQUENCE TO DATACENTER;

  13. 修改用戶

    # 修改用戶密碼
alter user customer identified by '密碼';

# 修改用戶表空間
alter user default tablespace new_tablespace_name;

 # 修改用戶角色
ALTER USER DATACENTER DEFAULT ROLE DBA,"CONNECT";

  14. 刪除用戶

    # 級聯(lián)刪除用戶下的數(shù)據(jù)表
drop user customer cascade;

  15. 查看所有用戶：

    select * from all_users;

2. 表空間操作語句

  1. 查看表空間的名稱及大小

    SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;

  2. 查看表空間物理文件的名稱及大小

    SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;

  3. 查看表空間的使用情況

    SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;

或者

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_name;
 
或者

SELECT a.tablespace_name "表空間名",
total "表空間大小",
free "表空間剩余大小",
(total - free) "表空間使用大小",
total / (1024 * 1024 * 1024) "表空間大小(G)",
free / (1024 * 1024 * 1024) "表空間剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空間使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

  4. 查看所有表空間

    select tablespace_name from dba_data_files group by tablespace_name

3. 數(shù)據(jù)文件操作語句

  1. 查看回滾段名稱及大小

    SELECT segment_name, tablespace_name, r.status, max_extents,
(initial_extent / 1024) initialextent, (next_extent / 1024) nextextent, v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name;

  2. 查看控制文件

    SELECT NAME FROM v$controlfile;

  3. 查看日志文件

    SELECT MEMBER FROM v$logfile;

  4. 查看用戶數(shù)據(jù)總量

    SELECT sum(bytes/(1024*1024*1024)) "用戶數(shù)據(jù)大小(G)"  
 from dba_segments 
 where owner='datacenter'; 

或者

select distinct owner, sum(bytes/(1024*1024*1024)) "用戶數(shù)據(jù)大小(G)" 
 from dba_segments 
 group by owner 
 order by "用戶數(shù)據(jù)大小(G)" desc;

4. 數(shù)據(jù)表操作語句

  1. 查詢系統(tǒng)所有對象

    select owner, object_name, object_type, created, last_ddl_time, timestamp, status
from dba_objects
where owner=upper('scott')

  2. 查看系統(tǒng)所有表

    select owner, table_name, tablespace_name from dba_tables

  3. 查看所有用戶的表

    select owner, table_name, tablespace_name from all_tables

  4. 查看當(dāng)前用戶表

    select table_name, tablespace_name from user_tables

  5. 查看用戶表索引

    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
  and t.table_name = "要查詢的表"

  6. 查看主鍵

    select cu.* from user_cons_columns cu, user_constraints au
 where cu.constraint_name = au.constraint_name
  and au.constraint_type = upper('p') 
  and au.table_name = "要查詢的表"

  7. 查看唯一性約束

    select column_name from user_cons_columns cu, user_constraints au
 where cu.constraint_name = au.constraint_name 
   and au.constraint_type = upper('u')
   and au.table_name = "要查詢的表"

  8. 查看外鍵

    select * from user_constraints c where c.constraint_type = 'r' and c.table_name = "要查詢的表"

select * from user_cons_columns cl where cl.constraint_name = "外鍵名稱"

select * from user_cons_columns cl where cl.constraint_name = "外鍵引用表的鍵名"

  9. 查看表的列屬性

    select t.*,c.comments
from user_tab_columns t, user_col_comments c
where t.table_name = c.table_name 
 and t.column_name = c.column_name 
 and t.table_name = "要查詢的表"

5. 數(shù)據(jù)庫屬性操作語句

  1. 查看數(shù)據(jù)庫庫對象

    SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;

  2. 查看數(shù)據(jù)庫版本

    SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';

或

select banner from sys.v_$version;

  3. 查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式

    SELECT created, log_mode, log_mode FROM v$database;

  4. 查看oracle最大連接數(shù)

    show parameter processes

  5. 修改最大連接數(shù)

    sql>alter system set processes=value scope=spfile
–重啟數(shù)據(jù)庫
sql>shutdown force
sql>start force

  6. 查看當(dāng)前連接數(shù)

    select * from v$session where username is not null

  7. 查看不同用戶的連接數(shù)

    select username,count(username) from v$session
 where username is not null 
 group by username

  8. 查看活動(dòng)的連接數(shù)

    # 查看并發(fā)連接數(shù)
select count(*) from v$session where status='active'

  9. 查看指定程序的連接數(shù)

    # 查看jdbc連接oracle的數(shù)目
select count(*) from v$session where program='jdbc thin client'

  10. 查看指定用戶的連接數(shù)及中止用戶連接

    # 查看所有用戶的當(dāng)前連接數(shù)
select username,count(username) from v$session where username is not null group by username;

# 查看某個(gè)用戶的連接信息
select username, sid, serial# from v$session where username='cif';
 
# 殺死用戶的連接信息
alter system kill session 'sid, serial#';  --說明 sid, serial#為v$session查詢出的值

  11. 查看數(shù)據(jù)庫安裝實(shí)例(dba權(quán)限)

    select * from v$instance

  12. 查看運(yùn)行實(shí)例名

    show parameter instance_name

  13. 查看數(shù)據(jù)庫名

    show parameter db_name

  14. 查看數(shù)據(jù)庫域名

    show parameter db_domain

或者 

select value from v$parameter where name='db_domain'

  15. 查看數(shù)據(jù)庫服務(wù)名

    show parameter service_names;

或者

show parameter service;

或者

show parameter names;

或者

select value from v$parameter where name="service_names"

  16. 查看全局?jǐn)?shù)據(jù)庫名

    show parameter global

  17. 查看系統(tǒng)所有的角色

    # 系統(tǒng)中所有的角色
select * from dba_roles;

# 系統(tǒng)中角色的權(quán)限信息
select * from dba_role_privs;

# 系統(tǒng)中用戶角色權(quán)限信息
select * from user_role_privs;

  18. 修改數(shù)據(jù)庫允許的最大連接數(shù)

    alter system set processes = 300 scope = spfile;

  19. 查看游標(biāo)數(shù)量

    Select * from v$open_cursor Where user_name='system';

  20. 查詢數(shù)據(jù)庫允許的最大連接數(shù)

    select value from v$parameter where name = 'processes';

或者

show parameter processes;

  21. 查詢數(shù)據(jù)庫允許的最大游標(biāo)數(shù)

    select value from v$parameter where name = 'open_cursors'

  22. 查詢系統(tǒng)用戶為每個(gè)會(huì)話打開的游標(biāo)數(shù)

    select o.sid, osuser, machine, count(*) num_curs 
 from v$open_cursor o, v$session s 
 where user_name = 'SYSTEM' and o.sid=s.sid 
 group by o.sid, osuser, machine 
 order by num_curs desc;

  23. 查看數(shù)據(jù)庫高速緩沖區(qū)的大小

    show sga;

# 顯示數(shù)據(jù)庫塊的大小
show parameter db_block_size;

# 顯示數(shù)據(jù)庫緩存大小
show parameter db_cache_size;

  24. 緩存顧問操作

    緩存區(qū)顧問用于啟動(dòng)或關(guān)閉統(tǒng)計(jì)信息，這些信息用于預(yù)測不同緩沖區(qū)大小導(dǎo)致的行為特征。

    # 查看緩存顧問狀態(tài)
show parameter db_cache_advice;

# 修改緩存顧問狀態(tài)：ON/OFF/READY
alter system set db_cache_advice = OFF;

# 查看數(shù)據(jù)庫高速緩沖區(qū)的信息
select id,name,block_size,size_for_estimate,buffers_for_estimate 
 from v$db_cache_advice;

  25. 查看Redo日志緩存區(qū)

    # Redo日志緩存區(qū)參數(shù)是靜態(tài)參數(shù)，不能進(jìn)行動(dòng)態(tài)修改
show parameter log_buffer;
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容