Oracle 基础SQL
查看当前数据库名
select name from v$database;
查看当前数据库实例名
select instance_name from v$instance;
查看进程的名称和描述
select name,description from v$bgprocess;
查询用户的默认表空间
select default_tablespace,username from dba_users;
创建自动扩展的表空间
SQL> create smallfile tablespace tp2
datafile 'tp2.dbf'
size 10M autoextend on next 1M maxsize 20M
extent management local autoallocate
segment space management auto;
代码详细解释：
| 行数 | 解释 |
| 1 | 表空间是 smallfile，也就是小文件表空间，它可以包含多个数据文件或临时文件。默认下就是 smallfile，可以省略。替换项是 bigfile，只包含一个数据文件或临时文件。 |
| 2 | 数据文件的名称 |
| 3 | 创建的数据文件的大小是 10M，当数据文件充满时，会自动扩大 1M，最大为 20M。 |
| 4 | 表空间使用位图来分配区间，其大小会自动设置。这是默认的，可以不写。 |
| 5 | 表空间中的段使用位图来跟踪块的使用情况。这是默认的，可以不写。 |
创建数据表
create table 表名称(
列名称 类型 [default 默认值]
列名称 类型 [default 默认值]
)
将查询结果作为表来创建
create table 新表名称 as select [表字段] from 旧表
重命名表
RENAME 旧表名 TO 新表名
添加一列
alter table 表名称 add(列名 类型 [default 默认值])
修改某列的默认值
alter table 表名称 modify(列名 类型 [default 默认值])
修改列名
alter table 表名称 rename column 旧列名 to 新列名
删除某列
alter table 表名称 drop (列名称)
将某列隐藏访问
alter table 表名称 set unused (列名称)
复制表结构
create table 新表名称 as select * from 旧表
添加唯一约束(UK) :指在表中某一字段不允许重复,使用unique
alter table 表名 add (列名 类型 [default 默认值 unique)
非空约束：指在表中某一字段不允许为空
alter table 表名 add (列名 类型 [default 默认值 not null)
主键约束:非空约束+唯一约束
alter table 表名 add constraints 字段 primary key(id)
删除约束
alter table <table_name> drop constraint <constraint_name>
软删除
delete from 表名
截断表
truncate table 表名
删除表
drop table
SQL 各种连接
创建视图
CREATE VIEW 视图名称 AS SELECT 字段 FROM 来源数据表
删除视图
drop view 视图名称
PL/SQL
结构
DECLARE --声明部分。例如定义常量，变量，引用的函数或过程等。
BEGIN --执行部分。包含变量赋值，过程控制等。
EXCEPTION --处理异常。包含错误处理语句。
END; --结束部分。
/ *添加这个斜杠来执行 PL/SQL 语句块。*/
声明变量
DECLARE 变量名称 [CONSTANT] 类型 [NOT NULL] [:=value];
存储过程
创建存储过程必须拥有 CREATE PROCEDURE
系统权限或者 CREATE ANY PROCEDURE
系统权限
创建存储过程
CREATE PROCEDURE PRO_NAME(arg_id IN NUMBER) AS v_sname VARCHAR2(20); --接收学生姓名
BEGIN SELECT s_name INTO v_sname FROM student WHERE s_id=arg_id; --把查询出来的值赋给变量 v_sname DBMS_OUTPUT.put_line('student''s name is : ' || v_sname); END;
IN
代表定义的参数是输入参数，是默认的，可以省略 IN。NUMBER
是输入参数的类型。AS
后面声明了一个变量用来接收学生姓名，用于打印出查询出来的学生姓名。BEGIN
和END
之间是执行的查询操作
自动化作业
定期备份表空间 system
编写bash 脚本
连接实列
创建作业
创建作业需要用到 DBMS_SCHEDULER
软件包的 CREATE_JOB
过程，它需要的参数可以使用 desc DBMS_SCHEDULER
查看到，参数详情可以参阅 DBMS_SCHEDULER 。
下面就创建一个作业，实现在每周的星期五备份表空间 system。
参数解释：
| 参数 | 说明 |
| job_name | 作业名。 |
| job_type | 作业类型。这里是调用的 bash 脚本，所以为 EXECUTABLE。它还有其他值，可以调用 sql 脚本，plsql 语句块等等。 |
| job_action | 作业执行的动作。这里是执行 backup_system.sh这个 bash 脚本。 |
| repeat_interval | 这里指在每周五早上 4 点执行备份操作。 |
| start_date | 开始日期。 |
| job_class | 作业类。这里使用的是默认的作业类。 |
| auto_drop | 这里设为 FALSE ，表示不会在作业完成后自动删除。 |
| comments | 这个作业的描述。 |
| enabled | 指示作业是否应在创建后立即启用。这里是立即启用。 |
查询执行的作业: select job_name,repeat_interval from dba_scheduler_jobs where job_name='BACKUP_SYSTEM';
查询作业的执行历史: select job_name,log_date,operation,status from dba_scheduler_job_log where job_name='BACKUP_SYSTEM';
修改作业
可以调用 DBMS_SCHEDULER
包的一些过程实现修改作业，启动，暂停，停止作业等操作。
还有一些其他过程：
run_job
：启动作业enable
：启动暂停的作业disable
：暂停作业copy_job
：复制作业
删除作业
exec DBMS_SCHEDULER.drop_job(job_name=>'BACKUP_SYSTEM');
Crontab
当使用者使用 crontab
后，该项工作会被记录到 /var/spool/cron/
里。不同用户执行的任务记录在不同用户的文件中。
通过 crontab
命令，我们可以在固定的间隔时间或指定时间执行指定的系统指令或脚本。时间间隔的单位可以是分钟、小时、日、月、周的任意组合。
其中特殊字符的意义：
| 特殊字符 | 意义 |
| * | 任何时刻 |
| , | 分隔时段，例如0 7,9 * * * command代表 7:00 和 9:00 |
| - | 时间范围，例如30 7-9 * * * command代表 7 点到 9 点之间每小时的 30 分 |
| /n | 每隔 n 单位间隔，例如*/10 * * * *每 10 分钟 |
使用 crontab 的基本语法如下：
crontab [-u username] [-l|-e|-r]
其常用的参数有：
| 选项 | 意思 |
-u | 只有 root 才能进行这个任务，帮其他使用者创建/移除 crontab 工作调度 |
-e | 编辑 crontab 工作内容 |
-l | 列出 crontab 工作内容 |
-r | 移除所有的 crontab 工作内容 |
我们这里还是以定期备份表空间为例。首先执行如下命令以添加一个任务计划：
$ crontab -e
选择后我们会进入一个添加计划的界面，按 i
键便可编辑文档，在文档的最后一行加上这样一行命令，实现每周日 9 点执行备份操作。
00 09 * * 0 home/oracle/BACKUP_SYSTEM.sh
查看任务情况
$ crontab -l
确认 cron 是否成功的在后台启动
$ ps aux | grep cron
#或者使用下面
$ pgrep cron
查看定时任务的日志反馈
$ sudo tail -f /var/log/syslog
删除定时任务
$ crontab -r
SQL 优化
追踪SQL
追踪 SQL 是为了找出那些执行时间缓慢，消耗资源过高的 SQL 语句。可以使用 SQL TRACE
工具和 DBMS_MONITOR
包。推荐使用 DBMS_MONITOR
包，它具有更高的灵活性。
使用DBMS_MONITOR
进行sql 追踪
设置标识符--myid
exec dbms_session.set_identifier('myid');
查看标识符-myid
select sid,serial#,username,client_identifier from v$session where client_identifier='myid';
启动追踪，第二个参数用于等待，第三个参数用于绑定变量
exec dbms_monitor.client_id_trace_enable('myid',true,false);
停止追踪--myid
exec dbms_monitor.client_id_trace_disable('myid');
使用 TKPROF 转换跟踪文件格式
bash下输入以下命令，获取追踪文件信息：
$ cd /u01/app/oracle/diag/rdbms/xe/xe/trace
$ tkprof xe_ora_1539.trc myid.prf explain=sys/Syl12345
查看文件中包含每条 SQL 语句解析，执行，获取这三个步骤的统计信息
cat /u01/app/oracle/diag/rdbms/xe/xe/trace/myid.prf
用
v$sqlarea
视图找出需要优化的 SQL
select disk_reads,executions,disk_reads/decode(executions,0,1,executions) rds_exec_ratio,sql_text
from v$sqlarea where sql_text like '%student%' order by disk_reads desc;
执行计划
数据库执行 SQL 语句，会先生成一个执行计划，然后按照执行计划里的步骤顺序完成。
生成 plan_table 表，存储执行计划
@?/rdbms/admin/utlxplan.sql
查看 plan_table 表结构
desc plan_table;
分析 sql 语句
explain plan for select * from student where s_age between 20 and 50;
查看执行计划
select operation,options,object_name,id,parent_id,cost from plan_table;
字段解释
| 字段 | 说明 |
| operation | 在该步骤中执行的内部操作的名称 |
| options | 操作上的变化 |
| object_name | 操作的对象名 |
| id | 分配给执行计划中每个步骤的编号 |
| parent_id | 在 ID 步骤的输出上操作的下一个执行步骤的 ID |
| cost | 根据优化程序的查询方法估算的操作成本 |
SQL 的执行顺序
寻找节点的顺序是自顶向下，自左向右。
所以整个执行顺序是 2->4->5->3->1->0
使用 AUTOTRICE
autotrace 中执行计划以缩进来表示父子节点关系。展示了 SQL 语句的执行计划以及统计信息。从输出结果中我们也能看出有较高的执行成本以及物理读取数。
启用 autotrice
set serveroutput on;
set autotrace trace;
关闭 autotrace
set autotrace off;
利用索引来进行SQL 查询优化
清除缓存
清除缓存。执行 SQL 语句的时候会生成缓存以便下一次执行相同语句更快，这一步是为了避免缓存对分析结果的影响。
alter system flush buffer_cache;
创建索引
create index idx_stu on [表名称]([字段名]);
分析索引
analyze index idx_stu compute statistics;
执行查询语句
select [字段名] from [表名称] where [条件]
