oracle基础语法
在 Oracle 开发中，客户端把 SQL 语句发送给服务器，服务器对 SQL 语句进行编译、执行，把执行的结果返回给客户端。常用的SQL语句大致可以分为五类：
数据定义语言（DDL），包括 CREATE（创建）命令、 ALTER（修改）命令、 DROP（删除）命令等。
数据操纵语言（DML），包括 INSERT（插入）命令、 UPDATE（更新）命令、 DELETE（删除）命令、 SELECT … FOR UPDATE（查询）等。
数据查询语言（DQL），包括基本查询语句、 Order By 子句、 Group By 子句等。
事务控制语言（TCL），包括 COMMIT（提交）命令、 SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
数据控制语言（DCL）， GRANT（授权）命令、 REVOKE（撤销）命令。
select - 从数据库中提取数据
update - 更新数据库中的数据
delete - 从数据库中删除数据
insert into - 向数据库中插入新数据
create database - 创建新数据库
altr database - 修改数据库
create lable - 创建新表
alter lable - 变更（改变）数据库表
drop lable - 删除表
create index - 创建索引（搜索键）
drop index - 删除索引
查看当前连接数据库的用户
show user
查看数据库当前连接数
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 --数据文件（默认数据文件）
account unlock; -- 账户是否解锁（lock:锁定、unlock解锁)
修改用户密码
alter user xiaoxiong identified by 123456789;
删除用户
drop user xiaoxiong;
赋予用户所有权限
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';
设置当前用户密码时长
alter profile default limit password_life_time '90';--90天期限
alter profile default limit password_life_time unlimited; --永久期限
查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
查看用户下所有的表
select * from user_tables;
查询当前用户所拥有的表的名称和所在的表空间名称
SELECT table_name,tablespace_name FROM user_tables;
查询当前用户所拥有的所有对象的名称、类型、创建时间、状态等信息
SELECT object_name,object_type,created,status FROM user_objects;
查询当前用户可以访问的表
SELECT table_name FROM all_tables;
查询当前数据库中所有的表，sys用户查看
SELECT table_name,tablespace_name,owner FROM dba_tables;
查看当前用户的角色
select * from user_role_privs;
赋予用户链接数据库权限
grant connect to xiaoxiong;
赋予用户只可以创建实体但是没有创建数据结构的权限
grant resource to xiaoxiong;
赋予用户登录的权限
grant create session to xiaoxiong;
赋予用户表操作的权限
grant create table to xiaoxiong;
赋予用户表空间操作的权限
grant unlimited tablespace to xiaoxiong;
赋予用户访问任务表的权限 同理可以赋予update 和delete
grant select on table to xiaoxiong;
取消用户对表操作的权限
rovoke create table from xiaoxiong;
查询指定用户系统权限
select * from dba_sys_privs where grantee = 'xiaoxiong';
查询指定用户对象权限
select * from dba_tab_privs where grantee = 'xiaoxiong';
查询数据库名
select name from v$database;
查询数据库实例名
select instance_name from v$instance;
查看数据库归档状态
select log_mode from v$database;
关闭数据库
shutdown immediate
启动到mount
startup mount
开启归档模式
alter database archivelog;
打开数据库
alter database open;
查看数据库运行状态
select status from v$instance;
查看数据文件、控制文件、日志文件的物理路径
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;
创建表空间
create tablespace xiaoxiong datafile 'xiaoxiong.dbf' size 20M;
查询表空间的基本信息
SELECT tablespace_name,extent_management,segment_space_management,contents,status FROM dba_tablespaces;
删除表空间
drop tablespace xiaoxiong;
删除带内容的表空间
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;
查看所有表空间存放的服务器路径
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;
创建表
create table xiaoxiong(name varchar(12),age varchar(12)) tablespace daxiong;
删除表
drop table xiaoxiong;
插入表数据
insert into xiaoxiong(name,age) values('dada',22);
批量插入表数据
INSERT ALL INTO da VALUES(11,'aaa') INTO da VALUES(22,'ccc') INTO da VALUES(33,'ddd') SELECT * FROM da;
修改表数据
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;
删除表内容
delete from xiaoxiong where age = 20;
将表移动到指定表空间
alter table table_name move tablespace_name;
查询数据库字符集
SELECT PARAMETER ,VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
角色管理
数据库预定义角色
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;
创建角色
CREATE ROLE role1;
赋予角色权限
grant connect, resource to role1;
撤销角色权限
revoke connect, resource from role1;
查看系统同目前所有的角色
SELECT role FROM dba_roles;
查询用户USER1所有用的角色和默认角色
SELECT granted_role,default_role FROM dba_role_privs WHERE grantee='XIAOXIONG';
查询角色role1中包含的系统权限
SELECT privilege,admin_option FROM role_sys_privs WHERE role='role1';
查看其它用户未提交的事务
SELECT START_DATE,START_TIME,START_SCN,START_SCNB,XID FROM v$TRANSACTION order by start_date , start_time;
索引
CREATE [UNIQUE∣BITMAP] INDEX /*索引类型*/
[<用户方案名>.]<索引名>
ON <表名>(<列名> | <列名表达式> [ASC∣DESC] [,…n])
[LOGGING | NOLOGGING] /*指定是否创建相应的日志记录*/
[COMPUTE STATISTICS] /*生成统计信息*/
[COMPAESS | NOCOMPRESS] /*对复合索引进行压缩*/
[TABLESPACE <表空间名>] /*索引所属表空间*/
[SORT | NOSORT] /*指定是否对表进行排序*/
[REVERSE]
UNIQUE：指定索引所 基于的列（或多列）值 必须唯一。默认的索引 是非唯一索引。Oracle 建议不要在表上显式定 义UNIQUE索引。
BITMAP：指定创建位图索引而不是 B*索引。位图索引保存的行标识符 与作为位映射的键值有关。位映射 中的每一位都对应于一个可能的行 标识符，位设置意味着具有对应行 标识符的行包含该键值。
<用户方案名>：表示包含索 引的方案。若忽略则Oracle 在自己的方案中创建索引。
ON子句：在指定表的列中 创建索引，ASC和DESC分别 表示升序索引和降序索引。
<列名表达式>：用指定表的列、 常数、SQL函数和自定义函数创 建的表达式，用于创建基于函数 的索引。指定列名表达式以后用 基于函数的索引查询时，必须保 证查询该列名表达式不为空。
LOLOGGING | NOLOGGING：LOGGING 选项规定在创建索引时， 创建相应的日志记录， NOGGING选项则表示创建 索引时不产生重做日志信 息，默认为LOGGING。
COMPUTE STATISTICS：COMPUTE STATISTICS选项表示在创建索引时 直接生成索引的统计信息，这样可 以避免以后对索引进行分析操作。
COMPAESS | NOCOMPRESS：对 于复合索引，如果指定了 COMPRESS选项，则可以在创建 索引时对重复的索引值进行压缩， 以节省索引的存储空间，但对索 引进行压缩后将会影响索引的使 用效率，默认为NOCOMPRESS。
SORT | NOSORT：默认情况下，Oracle在 创建索引时会对表中的记录进行排序，如 果表中的记录已经按照顺序排序，可以指 定NOSORT选项，这样可以省略创建索引 时对表进行的排序操作，加快索引的创建 速度。但若索引列或多列的行不按顺序保 存，Oracle就会返回错误，默认为SORT。
REVERSE：指定以反序索引块的 字节，不包含行标识符。 NOSORT不能与该选项一起指定。
查看表中的索引
SELECT * FROM ALL_INDEXES WHERE TABLE_NAME='DADA';
查询指定表的索引列是哪个
select * from all_IND_COLUMNS where table_name = 'DADA';
查询当前登录用户表索引
select * from USER_INDEXES where table_name = 'DADA';
查询当前用户创建的表的索引列
select * from USER_IND_COLUMNS where table_name = 'DADA';
B树索引
为xiaoxiong的age列创建索引，指定索引段在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上的索引来对年纪按照age进行排序
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>);
基于函数索引
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;
分区
范围分区
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个分区：
所有create_time小于'2023-02-01'的数据（不包含）被分配在分区p1中。
所有create_time小于'2023-03-01'的数据（不包含）被分配在p2中。
所有create_time大于等于'2023-03-01'的数据被分配在pmax中，如果没有这个分区，那么插入大于等于'2023-03-01'的数据时，会因为没有合适的存储分区而报错。
查看指定分区
select * from worker_202301 partition (WORKER20230129);
添加分区
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'));
删除分区
ALTER TABLE worker_202301(表名) DROP PARTITION WORKER20230131(分区名);
列表分区
列表分区适用于一个字段只有固定的几个值
创建列表
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;
插入数据
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'));
查询分区数据
SELECT * FROM worker_202302 PARTITION (technology_python);
exp命令
导出scott用户的dept表和emp表
exp scott/tiger FILE=/home/oracle/scott1.exp TABLES=(dept,emp)
导出xiaoxiong用户的所有数据
exp xiaoxiong/123456 file=/home/oracle/test.exp full=y
导出scott用户的dept表，但不导出表中的数据和索引
exp scott/tiger FILE=/home/oracle/soctt2.exp TABLES=DEPT ROWS=N INDEXES=N
导出分区emp表上的两个分区m和n：
exp scott/tiger FILE=/home/oracle/scott3.exp TABLES=(emp:m,emp:n)
用户user1导出其他用户的表：需要EXP_FULL_DATABASE系统权限
exp user1/1234 FILE=/home/oracle/scott4.exp TABLES= scott.dept
expdp 命令
创建逻辑目录（目录必须存在）
create directory dpdata as '/home/oracle/dump';
按用户导出
expdp system/123456@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata;
导出整个数据库
expdp system/123456 DIRECTORY=dpdata DUMPFILE=full.dmp FULL=y;
按表名导出
expdp system/123456@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata;
按查询条件导出
expdp system/123456@orcl directory=dpdata dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';
imp命令
system是登陆用户，123456是用户密码，orcl是数据库名称，file是存放要导入文件的目录
imp system/123456@127.0.0.1:1521/orcl file=/home/oracle/imp_test.dmp full=y
impdp 命令
导入到指定用户
impdp scott/tiger DIRECTORY=dpdata DUMPFILE=expdp.dmp SCHEMAS=scott;
导入表空间
impdp system/123456 DIRECTORY=dpdata DUMPFILE=tablespace.dmp TABLESPACES=example;
导入数据库
impdp system/123456 DIRECTORY=dpdata DUMPFILE=full.dmp FULL=y;
RMAN备份和恢复
RMAN备份
非一致性备份整个数据库（打开状态下全库备份）
数据库需处于归档模式
全量备份
RMAN> backup database format '/home/oracle/dump/full_%d_%s_%T_%p.bak';
对于非一致性备份，必须在备份完毕后，手工对当前的redo-log进行归归档。 因为在使用备份恢复数据库时，需要使用当前重做日志中的重做记录。
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;
查看某个表对应的序号及表空间
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');
查询表空间对应的数据文件及其序号
select file_name, file_id, tablespace_name from dba_data_files;
备份对应的表
RMAN> backup datafile 4 format '/home/oracle/dump/full_%d_%s_%T_%p.bak';
查看建立的表备份信息
RMAN> list backup of datafile 4;
备份控制文件
自动备份：开启该参数后，当在RMAN中执行backup或copy命令时，RMAN都会对控制文件进行一次自动备份。可以通过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';
在执行其他备份的同时，备份控制文件
RMAN> backup tablespace tb1 include current controlfile;
备份归档重做日志
单独备份重做日志
RMAN> backup archivelog all format '/home/oracle/dump/full_%d_%s_%T_%p.bak';
执行其他备份的同时，备份归档日志
RMAN> backup database current controlfile **plus archivelog** format '/home/oracle/dump/full_%d_%s_%T_%p.bak'
一致性状态下的全库备份（脱机全库备份）
RMAN> shutdown immediate
RMAN> startup mount
RMAN> backup database format '/home/oracle/dump/full_%d_%s_%T_%p.bak';
RMAN> alter database open;
RMAN恢复
完全恢复和不完全恢复；
restore和recover：
restore 是还原，文件级的恢复。就是物理文件还原。
recover 是恢复，数据级的恢复。逻辑上恢复，比如应用归档日志、重做日志，全部同步，保持一致。
常用命令：
restore 命令：用于还原已经备份的数据文件。
（1）restore database 还原所有的数据文件。
（2）restore tablespace 还原特定表空间的数据文件。
（3）restore datafile 还原特定的数据文件。
（4）restore controlfile 还原控制文件。
（5）restore archivelog 还原归档日志文件。
recover 命令：当数据库需要应用归档日志文件恢复数据文件时，使用recover命令。使用该命令数据库系统会自动应用归档的日志文件。
（1）recover database 恢复所有的数据文件。
（2）recover tablespace 恢复特定表空间的数据文件。
（3）recover datafile 恢复特定的数据文件。
归档模式下的完全恢复
模拟介质故障，通过系统删除daxiong表空间的物理文件；
mv daxiong.dbf daxiong.dbf.bak
恢复daxiong表空间
RMAN> restore tablespace daxiong;
RMAN> recover tablespace daxiong;
完全恢复
启动数据库到加载状态
RMAN> shutdown immediate;
RMAN> startup mount;
执行恢复
RMAN> restore database;
RMAN> recover database;
文章来源地址https://www.toymoban.com/news/detail-667378.html
文章来源：https://www.toymoban.com/news/detail-667378.html
到了这里，关于oracle超详细语法和备份工具的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
