oracle超詳細(xì)語法和備份工具

這篇具有很好參考價值的文章主要介紹了oracle超詳細(xì)語法和備份工具。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點擊"舉報違法"按鈕提交疑問。

oracle基礎(chǔ)語法

在 Oracle 開發(fā)中，客戶端把 SQL 語句發(fā)送給服務(wù)器，服務(wù)器對 SQL 語句進(jìn)行編譯、執(zhí)行，把執(zhí)行的結(jié)果返回給客戶端。常用的SQL語句大致可以分為五類：

數(shù)據(jù)定義語言（DDL），包括 CREATE（創(chuàng)建）命令、 ALTER（修改）命令、 DROP（刪除）命令等。

數(shù)據(jù)操縱語言（DML），包括 INSERT（插入）命令、 UPDATE（更新）命令、 DELETE（刪除）命令、 SELECT … FOR UPDATE（查詢）等。

數(shù)據(jù)查詢語言（DQL），包括基本查詢語句、 Order By 子句、 Group By 子句等。

事務(wù)控制語言（TCL），包括 COMMIT（提交）命令、 SAVEPOINT（保存點）命令、ROLLBACK（回滾）命令。     

數(shù)據(jù)控制語言（DCL）， GRANT（授權(quán)）命令、 REVOKE（撤銷）命令。

select - 從數(shù)據(jù)庫中提取數(shù)據(jù)

update - 更新數(shù)據(jù)庫中的數(shù)據(jù)

delete - 從數(shù)據(jù)庫中刪除數(shù)據(jù)

insert into - 向數(shù)據(jù)庫中插入新數(shù)據(jù)

create database - 創(chuàng)建新數(shù)據(jù)庫

altr database - 修改數(shù)據(jù)庫

create lable - 創(chuàng)建新表

alter lable - 變更（改變）數(shù)據(jù)庫表

drop lable - 刪除表

create index - 創(chuàng)建索引（搜索鍵）

drop index - 刪除索引

查看當(dāng)前連接數(shù)據(jù)庫的用戶

show user

查看數(shù)據(jù)庫當(dāng)前連接數(shù)

select count(*) from v$session;

查看用戶下的表

select * from tab;

查看指定用戶擁有的表

select table_name from dba_tables where owner='XIAOXIONG';

用戶切換

conn 用戶名/密碼 [as sysdba]

conn sys/123456 as sysdba

解鎖用戶

alter user sys(用戶名) account unlock;

alter user sys(用戶名) identified by 123456(新密碼);

新建用戶

create user xiaoxiong identified by 123456;

create user student--用戶名
  identified by "123456"--密碼
  default tablespace USERS--表空間名
  temporary tablespace temp --臨時表空間名
  profile DEFAULT    --數(shù)據(jù)文件（默認(rèn)數(shù)據(jù)文件）
  account unlock; -- 賬戶是否解鎖（lock:鎖定、unlock解鎖)

修改用戶密碼

alter user xiaoxiong identified by 123456789;

刪除用戶

drop user xiaoxiong;

賦予用戶所有權(quán)限

grant dba to xiaoxiong;

查看用戶使用時間限制

select * from dba_profiles where resource_name = 'PASSWORD_LIFE_TIME';

查詢密碼有效時長

select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';

查看所有用戶密碼到期日

select username,account_status,expiry_date,profile from dba_users;

查看指定用戶的過期時間

select username, expiry_date, created from dba_users where username = 'XIAOXIONG';

設(shè)置當(dāng)前用戶密碼時長

alter profile default limit password_life_time '90';--90天期限
alter profile default limit password_life_time unlimited; --永久期限

查看當(dāng)前用戶的系統(tǒng)權(quán)限和表級權(quán)限

select * from user_sys_privs;

select * from user_tab_privs;

查看用戶下所有的表

select * from user_tables;

查詢當(dāng)前用戶所擁有的表的名稱和所在的表空間名稱

SELECT table_name,tablespace_name FROM user_tables;

查詢當(dāng)前用戶所擁有的所有對象的名稱、類型、創(chuàng)建時間、狀態(tài)等信息

SELECT object_name,object_type,created,status FROM user_objects;

查詢當(dāng)前用戶可以訪問的表

SELECT table_name FROM all_tables;

查詢當(dāng)前數(shù)據(jù)庫中所有的表，sys用戶查看

SELECT table_name,tablespace_name,owner FROM dba_tables;

查看當(dāng)前用戶的角色

select * from user_role_privs;

賦予用戶鏈接數(shù)據(jù)庫權(quán)限

grant connect to xiaoxiong;

賦予用戶只可以創(chuàng)建實體但是沒有創(chuàng)建數(shù)據(jù)結(jié)構(gòu)的權(quán)限

grant resource to xiaoxiong;

賦予用戶登錄的權(quán)限

grant create session to xiaoxiong;

賦予用戶表操作的權(quán)限

grant create table to xiaoxiong;

賦予用戶表空間操作的權(quán)限

grant unlimited tablespace to xiaoxiong;

賦予用戶訪問任務(wù)表的權(quán)限同理可以賦予update 和delete

grant select on table to xiaoxiong;

取消用戶對表操作的權(quán)限

rovoke create table from xiaoxiong;

查詢指定用戶系統(tǒng)權(quán)限

select * from dba_sys_privs where grantee = 'xiaoxiong';

查詢指定用戶對象權(quán)限

select * from dba_tab_privs where grantee = 'xiaoxiong';

查詢數(shù)據(jù)庫名

select name from v$database;

查詢數(shù)據(jù)庫實例名

select instance_name from v$instance;

查看數(shù)據(jù)庫歸檔狀態(tài)

select log_mode from v$database;

關(guān)閉數(shù)據(jù)庫

shutdown immediate

啟動到mount

startup mount

開啟歸檔模式

alter database archivelog;

打開數(shù)據(jù)庫

alter database open;

查看數(shù)據(jù)庫運行狀態(tài)

select status from v$instance;

查看數(shù)據(jù)文件、控制文件、日志文件的物理路徑

select * from dba_data_files;

修改日志歸檔目錄

alter system set log_archive_dest_1='location=/db/app/oracle/oradata/orcl';

查看日志歸檔路徑

show parameter log_archive_dest;

查看歸檔日志格式

show parameter log_archive_format；

修改歸檔日志格式

alter system set log_archive_format ="archive_%t_%s_%r.log" scope=spfile;

查看歸檔日志空間占用

show parameter db_recovery;

創(chuàng)建表空間

create tablespace xiaoxiong datafile 'xiaoxiong.dbf' size 20M;

查詢表空間的基本信息

SELECT tablespace_name,extent_management,segment_space_management,contents,status FROM dba_tablespaces;

刪除表空間

drop tablespace xiaoxiong;

刪除帶內(nèi)容的表空間

drop tablespace animal including contents and datafiles;

查看表空間存放位置

select * from dba_data_files;

查看所有表空間的大小

select tablespace_name ,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;

查看所有表空間存放的服務(wù)器路徑

select tablespace_name,file_name from dba_data_files;

查詢表空間

select name from v$datafile;

查詢表空間空閑大小

select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space;

修改表空間大小

ALTER DATABASE datafile '/db/app/oracle/product/11.2.0/dbs/daxiong.dbf' RESIZE 50M;

創(chuàng)建表

create table xiaoxiong(name varchar(12),age varchar(12)) tablespace daxiong;

刪除表

drop table xiaoxiong;

插入表數(shù)據(jù)

insert into xiaoxiong(name,age) values('dada',22);

批量插入表數(shù)據(jù)

INSERT ALL INTO da VALUES(11,'aaa') INTO da VALUES(22,'ccc') INTO da VALUES(33,'ddd') SELECT * FROM da;

修改表數(shù)據(jù)

update xiaoxiong set name = 'xx' where age = 20;

追加新的列

alter table xiaoxiong add(shengri varchar(12));

修改列名

alter table xiaoxiong rename column shengri to data;

刪除列

alter table xiaoxiong drop column data;

刪除表內(nèi)容

delete from xiaoxiong where age = 20;

將表移動到指定表空間

alter table table_name move tablespace_name;

查詢數(shù)據(jù)庫字符集

SELECT PARAMETER ,VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

角色管理

數(shù)據(jù)庫預(yù)定義角色
CONNECT：CREATE SESSION

RESOURCE：CREATE SEQUENCE、CREATE TRIGGER、CREATE CLUSTER、CREATE PROCEDURE、CREATE TYPE、CREATE OPERATOR、CREATE TABLE、CREATE INDEXTYPE

DBA：基本全有

查看所有角色

select * from dba_users;

創(chuàng)建角色

CREATE ROLE role1;

賦予角色權(quán)限

grant connect, resource to role1;

撤銷角色權(quán)限

revoke connect, resource from role1;

查看系統(tǒng)同目前所有的角色

SELECT role FROM dba_roles;

查詢用戶USER1所有用的角色和默認(rèn)角色

SELECT granted_role,default_role FROM dba_role_privs WHERE grantee='XIAOXIONG';

查詢角色role1中包含的系統(tǒng)權(quán)限

SELECT privilege,admin_option FROM role_sys_privs WHERE role='role1';

查看其它用戶未提交的事務(wù)

SELECT START_DATE,START_TIME,START_SCN,START_SCNB,XID FROM v$TRANSACTION order by start_date , start_time;

索引

CREATE [UNIQUE∣BITMAP] INDEX /*索引類型*/
[<用戶方案名>.]<索引名>
ON <表名>(<列名> | <列名表達(dá)式> [ASC∣DESC] [,…n])
[LOGGING | NOLOGGING] /*指定是否創(chuàng)建相應(yīng)的日志記錄*/
[COMPUTE STATISTICS] /*生成統(tǒng)計信息*/
[COMPAESS | NOCOMPRESS] /*對復(fù)合索引進(jìn)行壓縮*/
[TABLESPACE <表空間名>] /*索引所屬表空間*/
[SORT | NOSORT] /*指定是否對表進(jìn)行排序*/
[REVERSE]


UNIQUE：指定索引所 基于的列（或多列）值 必須唯一。默認(rèn)的索引 是非唯一索引。Oracle 建議不要在表上顯式定 義UNIQUE索引。

BITMAP：指定創(chuàng)建位圖索引而不是 B*索引。位圖索引保存的行標(biāo)識符 與作為位映射的鍵值有關(guān)。位映射 中的每一位都對應(yīng)于一個可能的行 標(biāo)識符，位設(shè)置意味著具有對應(yīng)行 標(biāo)識符的行包含該鍵值。

<用戶方案名>：表示包含索 引的方案。若忽略則Oracle 在自己的方案中創(chuàng)建索引。

 ON子句：在指定表的列中 創(chuàng)建索引，ASC和DESC分別 表示升序索引和降序索引。

<列名表達(dá)式>：用指定表的列、 常數(shù)、SQL函數(shù)和自定義函數(shù)創(chuàng) 建的表達(dá)式，用于創(chuàng)建基于函數(shù) 的索引。指定列名表達(dá)式以后用 基于函數(shù)的索引查詢時，必須保 證查詢該列名表達(dá)式不為空。

LOLOGGING | NOLOGGING：LOGGING 選項規(guī)定在創(chuàng)建索引時， 創(chuàng)建相應(yīng)的日志記錄， NOGGING選項則表示創(chuàng)建 索引時不產(chǎn)生重做日志信 息，默認(rèn)為LOGGING。

COMPUTE STATISTICS：COMPUTE STATISTICS選項表示在創(chuàng)建索引時 直接生成索引的統(tǒng)計信息，這樣可 以避免以后對索引進(jìn)行分析操作。

COMPAESS | NOCOMPRESS：對 于復(fù)合索引，如果指定了 COMPRESS選項，則可以在創(chuàng)建 索引時對重復(fù)的索引值進(jìn)行壓縮， 以節(jié)省索引的存儲空間，但對索 引進(jìn)行壓縮后將會影響索引的使 用效率，默認(rèn)為NOCOMPRESS。

SORT | NOSORT：默認(rèn)情況下，Oracle在 創(chuàng)建索引時會對表中的記錄進(jìn)行排序，如 果表中的記錄已經(jīng)按照順序排序，可以指 定NOSORT選項，這樣可以省略創(chuàng)建索引 時對表進(jìn)行的排序操作，加快索引的創(chuàng)建 速度。但若索引列或多列的行不按順序保 存，Oracle就會返回錯誤，默認(rèn)為SORT。

REVERSE：指定以反序索引塊的 字節(jié)，不包含行標(biāo)識符。 NOSORT不能與該選項一起指定。


查看表中的索引

SELECT * FROM ALL_INDEXES WHERE TABLE_NAME='DADA';

查詢指定表的索引列是哪個

select * from all_IND_COLUMNS where table_name = 'DADA';

查詢當(dāng)前登錄用戶表索引

select * from USER_INDEXES where table_name = 'DADA';

查詢當(dāng)前用戶創(chuàng)建的表的索引列

select * from USER_IND_COLUMNS where table_name = 'DADA';

B樹索引

為xiaoxiong的age列創(chuàng)建索引，指定索引段在daxiong的表空間

create index age_index on xiaoxiong(age) tablespace daxiong;


普通索引

create index index_text_txt on dada(age);

索引的使用

age列上的索引來查詢age=1的信息
SELECT * FROM dada WHERE age=1;

age上的索引來對年紀(jì)按照age進(jìn)行排序
SELECT * FROM dada ORDER BY age;

age上的索引來查詢age大于22的信息	
SELECT * FROM dada WHERE age>22;

唯一索引

create unique index <index_name> on <table_name>(<coiumn_name>);

位圖索引

create bitmap index <index_name> on <table_name>(<column_name>);

組合索引

create index <index_name> on <table_name>(<column_name1><column_name2>);

基于函數(shù)索引

create index <index_name> on <table_name>(column_name) reverse;
create index <index_name> on <table_name>(upper(column_name));

反向鍵索引

create index <index_name> on <table_name>(column_name) reverse;

重置索引

alter index age_index rebuild;

刪除索引

drop index age_index;

將索引移動到指定的表空間

alter index index_name rebuild tablespace tablespace_name;

手動為索引分配空間

alter index age_index allocate extent(size 256k);

回收索引未使用的存儲空間

alter index age_index deallocate unused;

分區(qū)

范圍分區(qū)

create table members (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
(
 partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd')),
 partition p2 values less than (to_date('2023-03-01', 'yyyy-mm-dd')),
 partition pmax values less than (maxvalue)
);


上面的例子中定義了3個分區(qū)：

所有create_time小于'2023-02-01'的數(shù)據(jù)（不包含）被分配在分區(qū)p1中。
所有create_time小于'2023-03-01'的數(shù)據(jù)（不包含）被分配在p2中。
所有create_time大于等于'2023-03-01'的數(shù)據(jù)被分配在pmax中，如果沒有這個分區(qū)，那么插入大于等于'2023-03-01'的數(shù)據(jù)時，會因為沒有合適的存儲分區(qū)而報錯。


查看指定分區(qū)

select * from worker_202301 partition (WORKER20230129);

添加分區(qū)

ALTER TABLE worker_202301 
ADD PARTITION WORKER20230131 
VALUES LESS THAN 
(TO_DATE(' 2023-02-01 00:00:00', 
'SYYYY-MM-DD HH24:MI:SS', 
'NLS_CALENDAR=GREGORIAN'));

刪除分區(qū)

ALTER TABLE worker_202301(表名) DROP PARTITION WORKER20230131(分區(qū)名);


列表分區(qū)

列表分區(qū)適用于一個字段只有固定的幾個值


創(chuàng)建列表

create table WORKER_202302
(
  id        VARCHAR2(100) not null,
  name      VARCHAR2(200),
  technology      VARCHAR2(100),
  save_date DATE
)
partition by list (technology)
(
 partition technology_java values ('java'),
 partition technology_python values ('python'),
 partition technology_c values ('c')
);

create index IDX_WORKER_ID202301 on WORKER_202301 (ID)
  local;
create index IDX_WORKER_ID_NAME202301 on WORKER_202301 (ID, NAME)
  local;


插入數(shù)據(jù)

insert into worker_202302 (id,name,technology,save_date) values ('1','哪吒','java',to_date('2023/2/1 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202302 (id,name,technology,save_date) values ('2','云韻','java',to_date('2023/2/1 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202302 (id,name,technology,save_date) values ('3','美杜莎','python',to_date('2023/2/2 00:45:19','yyyy-MM-dd hh24:mi:ss'));

查詢分區(qū)數(shù)據(jù)

SELECT * FROM worker_202302 PARTITION (technology_python);

exp命令

導(dǎo)出scott用戶的dept表和emp表

exp scott/tiger FILE=/home/oracle/scott1.exp TABLES=(dept,emp)

導(dǎo)出xiaoxiong用戶的所有數(shù)據(jù)

exp xiaoxiong/123456 file=/home/oracle/test.exp full=y
	
導(dǎo)出scott用戶的dept表，但不導(dǎo)出表中的數(shù)據(jù)和索引
	  
exp scott/tiger FILE=/home/oracle/soctt2.exp TABLES=DEPT ROWS=N INDEXES=N
	
導(dǎo)出分區(qū)emp表上的兩個分區(qū)m和n：
	  
exp scott/tiger FILE=/home/oracle/scott3.exp TABLES=(emp:m,emp:n)
	
用戶user1導(dǎo)出其他用戶的表：需要EXP_FULL_DATABASE系統(tǒng)權(quán)限
	  
exp user1/1234 FILE=/home/oracle/scott4.exp TABLES= scott.dept

expdp 命令

創(chuàng)建邏輯目錄（目錄必須存在）
create directory dpdata as '/home/oracle/dump';

按用戶導(dǎo)出
expdp system/123456@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata;

導(dǎo)出整個數(shù)據(jù)庫
expdp system/123456 DIRECTORY=dpdata DUMPFILE=full.dmp FULL=y;

按表名導(dǎo)出
expdp system/123456@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata;

按查詢條件導(dǎo)出
expdp system/123456@orcl directory=dpdata dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';

imp命令

system是登陸用戶，123456是用戶密碼，orcl是數(shù)據(jù)庫名稱，file是存放要導(dǎo)入文件的目錄

imp system/123456@127.0.0.1:1521/orcl file=/home/oracle/imp_test.dmp full=y

impdp 命令

導(dǎo)入到指定用戶

impdp scott/tiger DIRECTORY=dpdata DUMPFILE=expdp.dmp SCHEMAS=scott;

導(dǎo)入表空間

impdp system/123456 DIRECTORY=dpdata DUMPFILE=tablespace.dmp TABLESPACES=example;

導(dǎo)入數(shù)據(jù)庫

impdp system/123456 DIRECTORY=dpdata DUMPFILE=full.dmp FULL=y;

RMAN備份和恢復(fù)

RMAN備份

非一致性備份整個數(shù)據(jù)庫（打開狀態(tài)下全庫備份）

數(shù)據(jù)庫需處于歸檔模式
全量備份
RMAN> backup database format '/home/oracle/dump/full_%d_%s_%T_%p.bak';

對于非一致性備份，必須在備份完畢后，手工對當(dāng)前的redo-log進(jìn)行歸歸檔。 因為在使用備份恢復(fù)數(shù)據(jù)庫時，需要使用當(dāng)前重做日志中的重做記錄。
RMAN> sql 'alter system archive log current';

查看建立的備份集與備份片段的信息
RMAN> list backup of database;

表空間備份

RMAN> backup tablespace daxiong format '/home/oracle/dump/full_%d_%s_%T_%p.bak';

查看建立的表空間備份信息

RMAN> list backup of tablespace daxiong;

查看某個表對應(yīng)的序號及表空間

select file_name, file_id, tablespace_name from dba_data_files where file_id in (select distinct file_id from dba_extents where segment_name='DA');

查詢表空間對應(yīng)的數(shù)據(jù)文件及其序號

select file_name, file_id, tablespace_name from dba_data_files;

備份對應(yīng)的表

RMAN> backup datafile 4 format '/home/oracle/dump/full_%d_%s_%T_%p.bak';

查看建立的表備份信息

RMAN> list backup of datafile 4;

備份控制文件

自動備份：開啟該參數(shù)后，當(dāng)在RMAN中執(zhí)行backup或copy命令時，RMAN都會對控制文件進(jìn)行一次自動備份?？梢酝ㄟ^format命令指定其存儲路徑

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/dump/full_%d_%s_%T_%p.bak';

單獨手動備份

RMAN> backup current controlfile format '/home/oracle/dump/full_%d_%s_%T_%p.bak';

在執(zhí)行其他備份的同時，備份控制文件

RMAN> backup tablespace tb1 include current controlfile;

備份歸檔重做日志

單獨備份重做日志

RMAN> backup archivelog all format '/home/oracle/dump/full_%d_%s_%T_%p.bak';

執(zhí)行其他備份的同時，備份歸檔日志

RMAN> backup database current controlfile **plus archivelog** format '/home/oracle/dump/full_%d_%s_%T_%p.bak'



一致性狀態(tài)下的全庫備份（脫機全庫備份）

RMAN> shutdown immediate
RMAN> startup mount
RMAN> backup database format '/home/oracle/dump/full_%d_%s_%T_%p.bak';
RMAN> alter database open;

RMAN恢復(fù)

完全恢復(fù)和不完全恢復(fù)；

restore和recover：

restore 是還原，文件級的恢復(fù)。就是物理文件還原。

recover 是恢復(fù)，數(shù)據(jù)級的恢復(fù)。邏輯上恢復(fù)，比如應(yīng)用歸檔日志、重做日志，全部同步，保持一致。

常用命令：

restore 命令：用于還原已經(jīng)備份的數(shù)據(jù)文件。
（1）restore database 還原所有的數(shù)據(jù)文件。
（2）restore tablespace 還原特定表空間的數(shù)據(jù)文件。
（3）restore datafile 還原特定的數(shù)據(jù)文件。
（4）restore controlfile 還原控制文件。
（5）restore archivelog 還原歸檔日志文件。

recover 命令：當(dāng)數(shù)據(jù)庫需要應(yīng)用歸檔日志文件恢復(fù)數(shù)據(jù)文件時，使用recover命令。使用該命令數(shù)據(jù)庫系統(tǒng)會自動應(yīng)用歸檔的日志文件。
（1）recover database 恢復(fù)所有的數(shù)據(jù)文件。
（2）recover tablespace 恢復(fù)特定表空間的數(shù)據(jù)文件。
（3）recover datafile 恢復(fù)特定的數(shù)據(jù)文件。


歸檔模式下的完全恢復(fù)

模擬介質(zhì)故障，通過系統(tǒng)刪除daxiong表空間的物理文件；

mv daxiong.dbf daxiong.dbf.bak

恢復(fù)daxiong表空間

RMAN> restore tablespace daxiong;
RMAN> recover tablespace daxiong;

完全恢復(fù)

啟動數(shù)據(jù)庫到加載狀態(tài)

RMAN> shutdown immediate;
RMAN> startup mount;

執(zhí)行恢復(fù)
RMAN> restore database;
RMAN> recover database;

文章來源地址http://www.zghlxwxcb.cn/news/detail-667378.html

到了這里，關(guān)于oracle超詳細(xì)語法和備份工具的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！