oracle12c數(shù)據(jù)庫管理常用語句

oracle12c數(shù)據(jù)庫管理常用語句:
select name from v$database; #查看數(shù)據(jù)庫名:
desc v$database;
select instance_name from v$instance; #數(shù)據(jù)庫實(shí)例名
以上三條sql語句，只能查詢到當(dāng)前連接的實(shí)例和該實(shí)例下的數(shù)據(jù)庫。

創(chuàng)新互聯(lián)于2013年成立，先為法庫等服務(wù)建站，法庫等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為法庫企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。

-- DBA/ALL/USER/V$/GV$/SESSION/INDEX開頭的絕大部分都是視圖
-- DBA_TABLES意為DBA擁有的或可以訪問的所有的關(guān)系表。
-- ALL_TABLES意為某一用戶擁有的或可以訪問的所有的關(guān)系表。
-- USER_TABLES意為某一用戶所擁有的所有的關(guān)系表。
-- 當(dāng)某一用戶本身就為數(shù)據(jù)庫DBA時(shí)，DBA_TABLES與ALL_TABLES等價(jià)。
-- DBA_TABLES >= ALL_TABLES >= USER_TABLES
-- 需要注意的是在ORACLE數(shù)據(jù)庫中大小寫是敏感的，而此三表中數(shù)據(jù)默認(rèn)都是大寫的，所以在進(jìn)行查詢的時(shí)候注意小寫的數(shù)據(jù)可能會(huì)造成數(shù)據(jù)無法查到。

SELECT FROM dba_views WHERE view_name LIKE 'DBA%';
SELECT FROM dba_views WHERE view_name LIKE 'ALL%';
SELECT FROM dba_views WHERE view_name LIKE 'USER%';
SELECT FROM dba_views WHERE viewname LIKE 'V$%'; -- 針對某個(gè)實(shí)例的視圖
SELECT FROM dba_views WHERE viewname LIKE 'GV$%'; -- 全局視圖，針對多個(gè)實(shí)例環(huán)境
SELECT FROM dba_views WHERE view_name LIKE 'SESSION%';
SELECT * FROM dba_views WHERE view_name LIKE 'INDEX%';

SELECT count(1) FROM dba_tables;
SELECT count(1) FROM all_tables;
SELECT count(1) FROM user_tables;

-- V$/GV$開頭的絕大部分都是V$/GV$表的別名
SELECT FROM dba_synonyms WHERE synonym_name LIKE 'V$%';
SELECT FROM dba_synonyms WHERE synonym_name LIKE 'GV$%';

-- X$沒有對應(yīng)的X_$
SELECT * FROM dba_synonyms WHERE synonym_name LIKE 'X$%';

-- 比較常用的DBA開頭的視圖有
select from dba_users; --數(shù)據(jù)庫用戶信息
select from dba_roles; --角色信息
select from dba_segments; --表段信息
select from dba_extents; --數(shù)據(jù)區(qū)信息
select from dba_objects; --數(shù)據(jù)庫對象信息
select from dba_lobs; --lob數(shù)據(jù)信息
select from dba_tablespaces; --數(shù)據(jù)庫表空間信息
select from dba_data_files; --數(shù)據(jù)文件設(shè)置信息
select from dba_temp_files; --臨時(shí)數(shù)據(jù)文件信息
select from dba_rollback_segs; --回滾段信息
select from dba_ts_quotas; --用戶表空間配額信息
select from dba_free_space; --數(shù)據(jù)庫空閑空間信息
select from dba_profiles; --數(shù)據(jù)庫用戶資源限制信息
select from dba_sys_privs; --用戶的系統(tǒng)權(quán)限信息
select from dba_tab_privs; --用戶具有的對象權(quán)限信息
select from dba_col_privs; --用戶具有的列對象權(quán)限信息
select from dba_role_privs; --用戶具有的角色信息
select from dba_audit_trail; --審計(jì)跟蹤記錄信息
select from dba_stmt_audit_opts; --審計(jì)設(shè)置信息
select from dba_audit_object; --對象審計(jì)結(jié)果信息
select from dba_audit_session; --會(huì)話審計(jì)結(jié)果信息
select from dba_indexes; --用戶模式的索引信息

-- 比較常用的ALL開頭的視圖有
select from all_users; --數(shù)據(jù)庫所有用戶的信息
select from all_objects; --數(shù)據(jù)庫所有的對象的信息
select from all_def_audit_opts; --所有默認(rèn)的審計(jì)設(shè)置信息
select from all_tables; --所有的表對象信息
select from all_indexes; --所有的數(shù)據(jù)庫對象索引的信息
select from all_tab_comments; --查詢所有用戶的表,視圖等
select from all_col_comments; --查詢所有用戶的表的列名和注釋.
select from all_tab_columns; --查詢所有用戶的表的列名等信息(詳細(xì)但是沒有備注)

-- 比較常用的ALL開頭的視圖有
select from user_objects; --用戶對象信息
select from user_source; --數(shù)據(jù)庫用戶的所有資源對象信息
select from user_segments; --用戶的表段信息
select from user_tables; --用戶的表對象信息
select from user_tab_columns; --用戶的表列信息
select from user_constraints; --用戶的對象約束信息
select from user_sys_privs; --當(dāng)前用戶的系統(tǒng)權(quán)限信息
select from user_tab_privs; --當(dāng)前用戶的對象權(quán)限信息
select from user_col_privs; --當(dāng)前用戶的表列權(quán)限信息
select from user_col_comments; -- 查詢本用戶的表的列名和注釋
select from user_role_privs; --當(dāng)前用戶的角色權(quán)限信息
select from user_indexes; --用戶的索引信息
select from user_ind_columns; --用戶的索引對應(yīng)的表列信息
select from user_cons_columns; --用戶的約束對應(yīng)的表列信息
select from user_clusters; --用戶的所有簇信息
select from user_clu_columns; --用戶的簇所包含的內(nèi)容信息
select * from user_cluster_hash_expressions; --散列簇的信息

-- 比較常用的V$開頭的別名有
select from v$database; --數(shù)據(jù)庫信息
select from v$datafile; --數(shù)據(jù)文件信息
select from v$controlfile; --控制文件信息
select from v$logfile; --重做日志信息
select from v$instance; --數(shù)據(jù)庫實(shí)例信息
select from v$log; --日志組信息
select from v$loghist; --日志歷史信息
select from v$sga; --數(shù)據(jù)庫SGA信息
select from v$parameter; --初始化參數(shù)信息
select from v$process; --數(shù)據(jù)庫服務(wù)器進(jìn)程信息
select from v$bgprocess; --數(shù)據(jù)庫后臺(tái)進(jìn)程信息
select from v$controlfile_record_section; --控制文件記載的各部分信息
select from v$thread; --線程信息
select from v$datafile_header; --數(shù)據(jù)文件頭所記載的信息
select from v$archived_log; --歸檔日志信息
select from v$archive_dest; --歸檔日志的設(shè)置信息
select from v$logmnr_contents; --歸檔日志分析的DML DDL結(jié)果信息
select from v$logmnr_dictionary; --日志分析的字典文件信息
select from v$logmnr_logs; --日志分析的日志列表信息
select from v$tablespace; --表空間信息
select from v$tempfile; --臨時(shí)文件信息
select from v$filestat; --數(shù)據(jù)文件的I/O統(tǒng)計(jì)信息
select from v$undostat; --Undo數(shù)據(jù)信息
select from v$rollname; --在線回滾段信息
select from v$session; --會(huì)話信息
select from v$transaction; --事務(wù)信息
select from v$rollstat; --回滾段統(tǒng)計(jì)信息
select from v$pwfile_users; --特權(quán)用戶信息
select from v$sqlarea; --當(dāng)前查詢過的sql語句訪問過的資源及相關(guān)的信息
select from v$sql; --與v$sqlarea基本相同的相關(guān)信息
select * from v$sysstat; --數(shù)據(jù)庫系統(tǒng)狀態(tài)信息

-- 比較常用的SESSION開頭的視圖有
select from session_roles; --會(huì)話的角色信息
select from session_privs; --會(huì)話的權(quán)限信息

-- 比較常用的INDEX開頭的視圖有
select * from index_stats; --索引的設(shè)置和存儲(chǔ)信息

-- 偽表，參考o(jì)racle 中 dual 詳解：http://blog.csdn.net/ozhouhui/article/details/7935196
select * from dual; --系統(tǒng)偽列表信息
select sysdate from dual; --可將Sysdate視為一個(gè)其結(jié)果為當(dāng)前日期和時(shí)間的函數(shù)，在任何可以使用Oracle函數(shù)的地方都可以使用Sysdate。也可以將它視為每個(gè)表的一個(gè)隱藏的列或偽列。
select current_date from dual; --報(bào)告會(huì)話的時(shí)區(qū)中的系統(tǒng)日期。注：可以設(shè)置自己的時(shí)區(qū)，以區(qū)別于數(shù)據(jù)庫的時(shí)區(qū)。
select SYSTIMESTAMP from dual; --報(bào)告TIMESTAMP數(shù)據(jù)類型格式的系統(tǒng)日期。

-- 系統(tǒng)權(quán)限
-- GRANTEE 接受該權(quán)限的用戶名
-- OWNER 對象的擁有者
-- GRANTOR 賦予權(quán)限的用戶
SELECT FROM dba_sys_privs WHERE grantee = 'SYS';
SELECT FROM dba_sys_privs WHERE grantee = 'CONNECT';
SELECT * FROM dba_sys_privs WHERE grantee = 'RESOURCE';

-- 角色權(quán)限
-- 查看某個(gè)用戶有哪些角色
select from dba_role_privs where grantee='SYS';
-- 查看某個(gè)角色被賦予了哪些用戶
SELECT FROM dba_role_privs WHERE granted_role = 'DBA';

-- 對象權(quán)限
SELECT * FROM dba_tab_privs;

-- 授予某個(gè)用戶某些角色
GRANT connect,resource TO 'USER';
GRANT dba to 'USER'; --給普通用戶授予dba角色時(shí)，要重新連接才能生效
REVOKE dba to 'USER';
-- 直接授予某個(gè)用戶某些權(quán)限
GRANT CREATE VIEW TO 'USER';

-- 查看某個(gè)系統(tǒng)用戶是否有SYSDBA或者SYSOPER權(quán)限
-- oracle:DBA,SYSDBA,SYSOPER三者的區(qū)別：
select * from V$PWFILE_USERS;

-- 鎖定、解鎖用戶
SELECT * FROM dba_users WHERE username = 'SCOTT';
ALTER USER SCOTT account LOCK; --鎖定用戶
ALTER USER SCOTT account UNLOCK; --解鎖用戶
COMMIT;

SELECT password FROM dba_users WHERE username = 'SCOTT';
alter user SCOTT identified by new_password; --修改用戶密碼

-- SERVICE_NAMES:
SELECT FROM global_name; -- 查看oracle的全局?jǐn)?shù)據(jù)庫名
SELECT FROM v$database; -- 查看數(shù)據(jù)庫名 show parameter db_name;

-- 數(shù)據(jù)庫實(shí)例名對應(yīng)著SID
-- SID:
-- linux下在配置oracle環(huán)境變量的情況可以使用 echo $ORACLE_SID,如果沒有可以使用ps -ef |grep oracle 來查詢，結(jié)果中的xxxx就是對應(yīng)的SID。
-- oracle 2548 1 0 Aug17 ? 00:00:00 ora_pmon_xxxx
-- 在windows環(huán)境下,oracle是以后臺(tái)服務(wù)的方式被管理的,所以看"控制面板->管理工具->服務(wù) 里面的名稱:"OracleServiceORCL",則ORCL就是sid;
SELECT * FROM v$instance; --查看數(shù)據(jù)庫實(shí)例名 show parameter instance_name;
select instance from v$thread;

-- show parameter是oracle的命令，不是標(biāo)準(zhǔn)SQL語句
-- 可以在sqlplus或者pl/sql dev的命令窗口執(zhí)行
-- show parameter aaaa;等價(jià)于SELECT FROM v$parameter WHERE name like '%aaaa%';
SELECT FROM v$parameter WHERE name like '%name%'; --等價(jià)于show parameter name;
select * from v$parameter where name like '%db_domain%'; --查詢數(shù)據(jù)庫域名

select username from all_users where username like '%SCOTT%';
drop user SCOTT cascade;
commit;

-- ERROR at line 1:
-- ORA-01940: cannot drop a user that is currently connected

select 'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL#||''''||';' as KILLER from v$session where username='SCOTT';
-- KILLER
-- ALTER SYSTEM KILL SESSION '363,35';
-- ALTER SYSTEM KILL SESSION '364,51';
commit;

select * from dba_roles where role like '%CONNECT%';
drop role CONNECT;
commit;

select * from dba_tablespaces where tablespace_name like 'EXAMPLE';
drop tablespace EXAMPLE including contents and datafiles cascade constraints ;
-- including contents 刪除表空間中的內(nèi)容，如果刪除表空間之前表空間中有內(nèi)容，而未加此參數(shù)，表空間刪不掉，所以習(xí)慣性的加此參數(shù)。
-- including datafiles 刪除表空間中的數(shù)據(jù)文件。
-- cascade constraints 同時(shí)刪除 tablespace 中表的外鍵參照。

-- 如果需要?jiǎng)?chuàng)建全局 DBLink，則需要先確定用戶有創(chuàng)建 dblink 的權(quán)限：
select * from user_sys_privs where privilege like upper('%DATABASE LINK%');

-- 如果沒有，則需要使用 sysdba 角色給用戶賦權(quán)：
grant create public database link to dbusername;

-- 如果創(chuàng)建全局 dblink，必須使用 systm 或 sys 用戶，在 database 前加 public。
create / public / database link dblink1
connect to dbusername identified by dbpassword
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

-- 創(chuàng)建dblink后，就可以直接在dblink上創(chuàng)建視圖
create or replace view cptp as (select SJDH from dbusername.cptp@dblink1); drop view cptp;

-- 鎖表查詢SQL
SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id　= o.object_id
AND l.session_id = s.sid;

-- 解除鎖表
alter system kill session 'sid, serial#';

-- 備份某個(gè)表
create table new_table as select * from old_table;

-- 查看數(shù)據(jù)庫是否在rac環(huán)境的集群中的
show parameter cluster_database;
select * from v$parameter where name = 'cluster_database';

-- 列操作
-- 增加和修改列不需要加關(guān)鍵字COLUMN
-- 刪除單列的話，一定要加COLUMN，刪除多列的時(shí)候，不能加COLUMN關(guān)鍵字

-- 增加一列
alter table emp4 add test varchar2(10);
-- 修改一列
alter table emp4 modify test varchar2(20);
-- 刪除一列
alter table emp4 drop column test;
-- 增加多列
alter table emp4 add (test varchar2(10),test2 number);
-- 修改多列
alter table emp4 modify (test varchar2(20),test2 varchar2(20));
-- 刪除多列
alter table emp4 drop (test,test2);

-- Windows下以管理員身份啟動(dòng)數(shù)據(jù)庫
net start oracleserviceorcl -- 后面的orcl是你安裝的數(shù)據(jù)庫實(shí)例名
net start oracleoradb11g_home1tnslistener --非必須

-- linux下以sysdba用戶登錄，然后啟動(dòng)數(shù)據(jù)庫
sqlplus / as sysdba
startup

-- sqlplus登陸方式
sqlplus / as sysdba --以操作系統(tǒng)權(quán)限認(rèn)證的oracle sys管理員登陸

sqlplus /nolog
conn / as sysdba --以操作系統(tǒng)權(quán)限認(rèn)證的oracle sys管理員登陸

sqlplus sys/password@orcl as sysdba --以sys用戶登陸必須使用as sysdba

sqlplus /nolog --不在cmd或者teminal當(dāng)中暴露密碼的登陸方式
conn sys/password as sysdba

sqlplus --不顯露密碼的方式登陸
Enter user-name：sys
Enter password：password as sysdba --以sys用戶登陸的話 必須要加上as sysdba子句

sqlplus scott/tiger@orcl --非管理員用戶登陸

desc v$database; --查詢v$database數(shù)據(jù)庫的表結(jié)構(gòu)

--在sqlplus中執(zhí)行sql腳本，下面兩種方式都可以
START file_name
@file_name

--判斷表是否存在，如果存在則刪除
declare
num number;
begin
select count(1) into num from all_tables where TABLE_NAME = 'EMP' and OWNER='SCOTT';
if num=1 then
execute immediate 'drop table EMP';
end if;
end;
/
--創(chuàng)建表
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
可以將上述存儲(chǔ)過程加載到每一個(gè)create table前面。

--ORACLE 判斷序列是否存在,如果存在就刪除

declare
V_NUM number;

BEGIN
----多次刪除時(shí)，每次都將v_num設(shè)置成為0
V_NUM := 0;
----判斷序列 seq_name_1 是否存在（區(qū)分大小寫）
select count(0) into V_NUM from user_sequences where sequence_name = 'SEQ_BUSINESS_PROCESS_INDEX_ID';
----如果存在立即刪除
if V_NUM > 0 then
execute immediate 'DROP SEQUENCE SEQ_BUSINESS_PROCESS_INDEX_ID';
end if;
END;

-- 設(shè)置sqlplus模式顯示總行數(shù)
show pagesize; --查看當(dāng)前的pagesize
set pagesize 300;

-- 設(shè)置sqlplus模式顯示行寬度
show linesize; --查看當(dāng)前的linesize
set linesize 300;

-- 修改安裝目錄glogin.sql文件才能保證之前的設(shè)置永久生效
set pagesize 300;
set linesize 300;

-- 刪除表對象
select 'drop table '||segment_name from dba_segments where owner='VPMUSER' and segment_type='TABLE';
-- 創(chuàng)建表對象
select
'create table '||segment_name || ' as select * from '||segment_name ||'@DBLINK'
from dba_segments where owner='VPMUSER' and segment_type='TABLE';

-- 檢查表是否完整導(dǎo)入
select segment_name from dba_segments@aaa where owner='VPMUSER' and segment_type='TABLE'
and (segment_name not like 'BIN$%'
and segment_name not like '%201%')
minus
select segment_name from dba_segments where owner='VPMUSER' and segment_type='TABLE' and segment_name not like 'BIN$%'

--查詢用戶所有表的語句1
select t.table_name,t.comments from user_tab_comments t

--查詢用戶所有表的語句2:
select r1, r2, r3, r5
from (select a.table_name r1, a.column_name r2, a.comments r3
from user_col_comments a),
(select t.table_name r4, t.comments r5 from user_tab_comments t)
where r4 = r1

-- 查找表的所有索引（包括索引名，類型，構(gòu)成列）：
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 = 要查詢的表
-- 查找表的主鍵（包括名稱，構(gòu)成列）：
select cu. from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查詢的表

-- 查找表的唯一性約束（包括名稱，構(gòu)成列）：
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'U' and au.table_name = 要查詢的表

-- 查找表的外鍵（包括名稱，引用表的表名和對應(yīng)的鍵名，下面是分成多步查詢）：
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 = 外鍵引用表的鍵名

-- 查詢表的所有列及其屬性
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 = 要查詢的表

--備份表數(shù)據(jù)
create table emp as select * from scott.emp

--還原表數(shù)據(jù)
insert into emp select * from scott.emp

--查看已經(jīng)執(zhí)行過的sql這些是存在共享池中的，用戶名需要大寫，必須具有DBA 的權(quán)限
select * from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('用戶名') order by t.LAST_ACTIVE_TIME desc

--ORACLE11G 字符集更改（這里更改為AL32UTF8）
sqlplus sys as sysdba

--執(zhí)行下面命令，有可能造成數(shù)據(jù)庫中已有數(shù)據(jù)混亂的情況，所以在進(jìn)行操作前，要進(jìn)行數(shù)據(jù)庫的備份操作
shutdown immediate;
STARTUP MOUNT;
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE character set INTERNAL_USE AL32UTF8;
ALTER SESSION SET SQL_TRACE=FALSE;
shutdown immediate;
startup;

--察看 NLS_LANG 信息：
SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';

UPDATE STAFF
SET MODIFY_TIME = TO_DATE('2016/04/22 00:01:00', 'yyyy/MM/dd hh34:mi:ss')
WHERE MODIFY_TIME < TO_DATE('2016/04/22 00:01:00', 'yyyy/MM/dd hh34:mi:ss');

UPDATE STAFF
SET MODIFY_TIME = TO_TIMESTAMP('19-03-2008 02:36:00.360000', 'dd-MM-yyyy hh34:mi:ss.ff')
WHERE STAFF_ID = '01';

查詢垃圾信息：
SELECT t.object_name,t.type ,t.original_name FROM user_recyclebin t;

清除垃圾信息：purge table origenal_tableName;
purge index origenal_indexName;

刪除回收站所有垃圾信息：
PURGE recyclebin;
刪除Table不進(jìn)入Recycle的方法：
drop table tableName purge;

查看密碼策略:

select * from dba_profiles where profile='default';

網(wǎng)頁標(biāo)題：oracle12c數(shù)據(jù)庫管理常用語句
文章起源：http://bm7419.com/article30/jcicpo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、網(wǎng)站維護(hù)、定制開發(fā)、服務(wù)器托管、定制網(wǎng)站、品牌網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)