字符型

类型 大小 描述
varchar2 0~4000 可变长度字符串
nvarchar2 0~1000 Unicode字符串的可变长字符型数据
char 0~2000 定长字符型数据
nchar 0~1000 Unicode字符集定长字符型数据
long 0~2GB 变长字符串

数字型

类型 进制 描述
number(p,s) 十进制 p最大精度38位,s小数位数
float 二进制 126位整数

日期

类型 大小 描述
date 公元前4712-1-1~9999-12-31 存储日期和时间
timestamp 公元前4712-1-1~9999-12-31 精确到小数秒,显示上下午

其他数据类型

类型 大小 描述
blob 4GB 二进制
clob 4GB 字符串
bfile 视操作系统 存储非结构化数据到数据库外的文件中

创建表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE table_name
(
column_name datatype [NULL|NOT NULL],
...,
PRIMARY KEY(),
CONSTRAINT table_name constraint_name FOREIGN KEY (column_name) REFERENCE table_name(column_name) ON DELETE CASCADE,
CONSTRAINT constraint_name CHECK(condition),
CONSTRAINT constraint_name UNIQUE(column_name)
)
|AS SELECT column_name1,column_name2,...FROM source_table;
DROP TABLE table_name;

删除表

1
2
3
4
5
6
DROP TABLE table_name;
# 执行最快,删除数据、结构、索引、约束、触发器和索引,存储过程和索引invalid状态,直接生效,不可回滚,不释放空间
TRUNCATE TABLE table_name;
# 执行较快,只删除数据,直接生效,不可回滚,释放空间
DELETE FROM table_name [WHERE condition];
# 执行最慢,只删除数据,commit 后生效,可回滚,不释放空间

操作表列

1
2
3
4
5
ALTER TABLE table_name
ADD column_name datatype [NULL|NOT NULL]
|MODIFY column_name new_datatype|NULL|NOT NULL
|DROP COLUMN column_name;
-- 删除列时通常追加 CASCADE CONSTRAINTS ,以删除于该列有关的约束

操作主键

1
2
3
ALTER TABLE table_name
ADD CONSTRAINTS constraint_name PRIMARY KEY(column_name)
|DROP CONSTRAINTS constraint_name;

操作外键

1
2
3
ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCE table_name(column_name) ON DELETE CASCADE
|DROP CONSTRAINT constraint_name;

操作CHECK约束

1
2
3
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK(condition)
|DROP CONSTRAINT constraint_name;

操作UNIQUE约束

1
2
3
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE(column_name)
|DROP CONSTRAINT constraint_name;

添加数据

1
2
3
INSERT INTO table_name(column_name1,column_name2,...)
VALUES(data1,data2,...)
|SELECT column_name1,column_name2...FROM table_name2;

修改数据

1
UPDATE table_name SET column_name1=data1,column_name2=data2,...[WHERE condition];

删除数据

1
2
DELETE FROM table_name [WHERE condition];
TRUNCATE TABLE table_name;

查询数据

1
SELECT column_name1,column_name2,...FROM table_name [WHERE condition];

MERGE语句

1
MERGE INTO table_name1 USING table_name2 ON(condition) WHEN MATCHED THEN ... WHEN NOT MATCHED THEN ...;

SELECT 语句

1
SELECT [DISTINCT|ALL] select_list FROM table_list [WHERE ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...];

select_list

1
*|[schema.] {table|view} .*|expr[	[AS ]c_alias]

expr

1
"||" 连接的字符串 | 函数

ORDER BY …

1
{expr|positon|c_alias} {ASC|DESC} {NULLS FIRST|NULLS LAST}[ {expr|positon|c_alias} {ASC|DESC} {NULLS FIRST|NULLS LAST},...]

模糊查询关键字like

1
'_'替代一个字符,'%'替代多个字符

从给定值中选取查询

1
IN(data1,data2,...)

连接

1
2
3
4
5
6
7
8
-- 连接,只能查询匹配记录
SELECT select_list FROM table_name1 INNER JOIN table_name2 ON condition;
-- 左外连接
SELECT select_list FROM table_name1 LEFT JOIN table_name2 ON condition;
-- 右外连接
SELECT select_list FROM table_name1 RIGHT JOIN table_name2 ON condition;
-- 全外连接
SELECT select_list FROM table_name1 FULL JOIN table_name2 ON condition;

内置函数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
ABS(n)    -- n绝对值
MOD(n2,n1)    -- n2对n1取余
SIGN(n)    -- n的符号
CEIL(n)    -- 大于等于n的最小整数
FLOOR(n)    -- 小于等于n的最大整数
SQRT(n)    -- n的平方根
POWER(n2,n1)    -- n2的n1次幂
EXP(n)    -- e的n次幂
LOG(n1,n2)    -- n1为底n2的对数
LN(n)    -- n的自然对数
ROUND(n2,n1)    -- n2小数部分四舍五入至n1位
TRUNC(n2,n1)    -- n2截取至n1位
CHR(n)    -- 把n根据ASCII转换成字符
ASCII(char)    -- 参数首字母的ASCII值
LENGTH(char)    -- 字符串长度
SUBSTR(char,position[,substring_length])    -- 截取字符串
CONCAT(char1,char2)    -- 连接字符串
INSTR(string,substring[,position[,occurrence]])    -- 查找字符串
UPPER(char)    -- 转换成大写
LOWER(char)    -- 转换成小些
INITCAP(char)    -- 单词首字母大写
NLSSORT(char[,nslparam])    -- 按指定方式排序'NLS_SORT=SCHINESE_PINYIN_M'
REPLACE(char,search_string[,replacement_string])    -- 字符串替换,默认删除
RPAD(expr1,n[,expr2])    -- 用expr2右填充expr1至长度为n,默认空格
LPAD(expr1,n[,expr2])    -- 用expr2左填充expr1至长度为n,默认空格
TRIM([LEADING|TRAILING|BOTH] [trim_character FROM] trim_source)    -- 删除字符串首尾指定字符
SYSDATE    -- 获取系统当前日期
SYSTIMESTAMP    -- 获取系统当前时间
DBTIMEZONE    -- 获取数据库当前时区
ADD_MONTHS(date,integer)    -- 指定日期增加指定月份数
SESSIONTIMEZONE    -- 获取当前会话的时区
LAST_DAY(date)    -- 获取指定日期对应月份的最后一天
NEXT_DAY(date,char)    -- 获取下周char的日期
CURRENT_DATE    -- 获取会话时区的当前日期
EXTRACT(datetime)    -- 从指定时间中获取指定部分
MONTHS_BETWEEN(date1,date2)    -- 获取两个时间之间的月份数
NET_TIME(date,timezone1,timezone2)    -- 获取时区1中的时间转换到时区2后的时间
TO_CHAR(n[,fmt])    -- 转换为字符类型
TO_DATE(n[,fmt])    -- 转换为时间类型
TO_NUMBER(n[,fmt])    -- 转换为数字类型
LNNVL(condition)    -- 排除指定条件函数
NVL(expr1,expr2)    -- expr1为空时返回expr2
NVL2(expr1,expr2,expr3)    -- expr1为空时返回expr3,不为空返回expr2
AVG([DISTINCT|ALL ]expr)    -- 获取平均值
COUNT(*|[DISTINCT|ALL ]expr)    -- 获取数量
SUM([DISTINCT|ALL ]expr)    -- 获取和
SELECT USER FROM DUAL;    -- 返回当前会话的登录名
USERENV(param)    -- 返回当前会话的信息
SYS_CONTEXT(namespace,param)    -- 返回oracle已创建的context
DECODE(expr,search,result[,search1,result1...])    -- expr结果是search返回result

查看所有默认表空间

1
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

查看指定用户默认表空间

1
SELECT DEFAULT_STAPCE,USERNAME FROM DBA_USERS WHERE USERNAME='username';

创建表空间

1
2
3
4
5
6
7
8
CREATE TABLESPACE tablespace_name
DATAFILE filename
SIZE size
[AUTOEXTEND [ON NEXT size|OFF]]
[MAXSIZE size]
[PERMANENT|TEMPORARY]     永久/临时表空间,默认永久
[EXTENT MANAGEMENT [DICTIONARY|LOCAL     字典/本地管理方式,默认本地
[AUTOALLOCATE|UNIFORM. [SIZE integer[K|M]]]];

重命名表空间

1
ALTER TABLESPACE oldname RENAME TO newname;

修改表空间大小

1
ALTER DATABASE DATAFILE filename RESIZE size;

增加表空间大小

1
ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size;

设置表空间读写状态

1
ALTER TABLESPACE tablespace_name READ {ONLY|WRITE};

设置表空间可用状态

1
ALTER TABLESPACE tablespace_name {ONLINE|OFFLINE [NORMAL|TEMPORARY|IMMEDIATE]};

创建大文件表空间

1
CREATE BIGFILE TABLESPACE tablespace_name DATAFILE filename SIZE size;

删除表空间

1
2
3
DROP TABLESPACE tablespace_name
[INCLUDING CONTENTS AND DATAFILES]     数据文件删除
[CASCADE CONSTRAINTS];    完整性删除

查看表空间大小

1
SELECT TABLESPACE_NAME,FILE_NAME,BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=tablespace_name;

查看表空间剩余 空间

1
SELECT TABLESPACE_NAME,BYTES FROM DBA_FREE_SPACES;

创建/修改用户

1
2
3
4
5
6
7
8
CREATE|ALTER
USER user_name
IDENTIFIED BY password
[DEFAULT TABLESPACE tablespace_name ]
[QUOTA size|UNLIMITED ON tablespace_name ]    用户使用表空间的最大值
[PROFILE profile ]    概要文件
[PASSWORD EXPIRE ]    用户密码过期
[ACCOUNT LOCK|UNLOCK];    默认锁定状态

删除用户

1
DROP USER user_name CASCADE;

授予系统权限

1
2
3
4
GRANT
system_privileges|ALL PRIVILEGES     权限
TO {user IDENTIFIED BY password|role }    用户/角色
[WITH ADMIN OPTION];    授予其他用户或角色系统权限

授予对象权限

1
2
3
4
5
6
GRANT
object_privilege|ALL     权限
ON schema.object     对象
TO user_name|role_name     用户/角色
[WITH ADMIN OPTION ]    授予其他用户或角色系统权限
[WITH THE GRANT ANY OBJECT];    授予其他用户或角色对象权限

撤销系统权限

1
2
REVOKE system_privilege FROM
user|role;

撤销对象权限

1
2
3
4
5
REVOKE
object_privilege |ALL
ON schema.object FROM
user_name|role_name
[CASCADE CONSTRAINTS];

数据字典

数据 字典
系统权限 DBA_SYS_PRIVS
对象权限 DBA_TAB_PRIVS
用户角色 DBA_ROLE_PRIVS

创建角色

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE|ALTER     //创建/修改
ROLE role_name
[NOT IDENTIFIED|IDENDIFIED BY [password]];
GRANT     //填充权限
system_privilege|ALL PRIVILEGES
TO role_name
[WITH ADMIN OPTION];
-- 角色创建完成后不能直接使用,需将角色赋予用户才能使用
GRANT role_name TO user_name;
SET ROLE role_name    -- 设置角色生效
SET ROLE ALL    -- 设置所有角色生效
SET ROLE ALL EXCEPT role_name    -- 设置只有role_name失效
SET ROLE NONE    -- 设置所有角色失效

删除角色

1
DROP ROLE role_name;

脱机备份(冷备份)/恢复

1
关闭数据库服务后直接复制需要的文件,包括数据文件和控制文件

联机备份(热备份)

1
2
3
4
5
6
7
8
9
ARCHIVE LOG LIST                                        查看本机数据库的日志状态
ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;   设置日志模式为归档
SHUTDOWN IMMEDIATE;                                     关闭数据库
STARTUP MOUNT;                                          启动mount实例
ALTER DATABASE ARCHIVELOG;                              更改数据库为归档日志模式
ALTER DATABASE OPEN;                                    更改数据库状态为打开模式
ALTER TABLESPACE tablespace_name BEGIN BACKUP;          开始备份数据库
复制文件到其他目录
ALTER TABLESPACE tablespace_name END BACKUP;            结束备份操作

恢复

1
2
3
4
5
6
7
ALTER SYSTEM ARCHIVE LOG CURRENT;                       归档当前日志
ALTER SYSTEM SWITCH LOGFILE;                            切换日志文件
SELECT * FROM v$RECOVER_FILE                            获取文件编号
ALTER DATABASE DATAFILE file_id OFFLINE DROP;           把要恢复的数据文件脱机
ALTER DATABASE OPEN;                                    更改数据库状态为打开模式
RECOVER DATAFILE file_id;                               恢复数据文件
ALTER DATABASE DATAFILE file_id ONLINE;                 设置数据文件联机

EXP工具导出数据

1
exp db_user/password                                  登陆数据库的用户名和密码,非SYS

EXP工具直接导出表

1
exp db_user/password file="filename.dmp" tables="table_name,..."

EXP工具导出表空间

1
exp db_user/password file="filename.dmp" tablespaces="tablespaces_name"

EXPDP导出数据

1
2
3
CREATE DIRECTORY directory_name AS 'file_name';         目录名称 文件名称
GRANT READ,WRITE ON DIRECTORY directory_name TO db_user;    授权用户使用该目录
#expdp db_user/password directory=directory_name dumpfile=file_name tables=table_name;

IMP导入数据

1
imp db_user/password

IMP直接导入表

1
imp db_user/password file="filename.dmp" tables="table_name,..."

IMPDP导入数据

1
impdp db_user_password

IMPDP直接导入表

1
impdp db_user/password directory=dir dumpfile=filename.dmp tables=table_name;

RMAN工具配置

1
2
3
4
5
6
CONN /AS SYSDBA;    连接恢复目录数据库
CREATE USER rman_user IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name;    创建恢复用户
GRANT RECOVERY_CATALOG_OWNER TO rman_user;    为新创建的用户授权
#rman
CONN CATALOG rman_user/password;    连接新创建的用户
CREATE CATALOG;    创建恢复目录

RMAN工具使用

1
2
3
4
#rman target db_user/password@servicename catalog rman_user/password    连接恢复目录数据库
CONNECT TARGET db_user/password@servicename;    连接目标数据库
CONNECT CATALOG rman_user/password@servicename;    连接恢复目录数据库
REGISTER database;    在恢复目录数据库中注册数据库

手动分配通道

1
2
3
4
5
6
7
关闭目标数据库,启动到mount状态,运行:
run
{
    ALLOCATE CHANNEL channel_name1 DEVICE TYPE {sbt|disk};
    ...
    BACKUP [level] [backup type] [option]
}

自动分配通道

1
2
3
CONFIGURE DEVICE TYPE {sbt|disk} PARALLELISM n;    指定通道类型和名称
CONFIGURE DEFAULT DEVICE TYPE {sbt|disk};    指定默认设备类型
BACKUP [level] [backup type] [option];

BACKUP 参数

1
2
3
level           备份增量,1234或者FULL(全备份)
backup type     对象类型,databasedatafiletablespacecontrolfilecopyarchivelog all
option          channel备份使用的通道 maxsetsize定义备份集的最大值

RESTORE还原

1
2
RESTORE database_object;
database_object:    DATABASE(mount),TABLESPACE(open),DATAFILE,CONTROLFILE(mount),ARCHIVELOG,SPFILE(mount)

RECOVER同步恢复

1
2
RECOVER database_object;
database_object:    DATABASE(mount),TABLESPACE(open),DATAFILE