Oracle杂记总结

安浅66

已于 2024-02-03 09:46:31 修改

阅读量386

点赞数

分类专栏： Oracle 文章标签： oracle 数据库

于 2022-05-21 23:57:57 首次发布

本文链接：https://blog.csdn.net/tua1234/article/details/124904262

版权

Oracle 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文总结了Oracle数据库的安装、exp/imp命令使用、RAC架构介绍、表空间管理、SQL执行时间查询、Scheduler调度技巧以及SQL*Plus命令的实战应用，适合Oracle运维与开发人员参考。

摘要由CSDN通过智能技术生成

Oracle杂记总结

我的Oracle笔记

Linux安装11g
exp命令导出
 imp命令导入
 RAC
查看表空间大小
 查询SQL执行时间
 查询时间差
 Scheduler
sqlplus

Linux安装11g

参考这篇博文

linux安装Oracle11G https://www.cnblogs.com/mmzs/p/9033112.html

exp命令导出

exp命令用于把数据从数据库导出至本地服务器,格式为dmp文件。
格式：exp 用户名/密码@实例名 file=./1.dmp log=./1.log tables=table1
重要参数：
file=./1.dmp 导出文件名为1.dmp
full=y 导出全库
tables=table1,table2 导出表1、表2
log=./1.log 执行日志输出到1.log中
注意：此操作在cmd或者Linux命令行下执行，而非SQL。

imp命令导入

imp命令用于把本地的dmp文件导入到Oracle数据库中。
格式：imp 用户名/密码@实例名 file=./1.dmp full=y
重要参数：
file=./1.dmp 将1.dmp导入数据库
full=y 全量导入
log=./1.log 执行日志输出到1.log中
ignore=y 忽略创建错误
注意：此操作在cmd或者Linux命令行下执行，而非SQL。

RAC

数据库集群

数据库的横向扩展是通过数据库集群实现的。数据库集群也有两种主要形式，一种是主备（主从）架构，也就是只有一台服务器上的数据库可以访问，其他服务器上数据库不能访问或者只能进行读操作；另外一种是多活架构，这种架构中所有服务器都可以对外提供服务（可同时读写），根据集群中节点是否可以共享数据，多活架构又分为两种。一种是非共享数据的多活，该种情况下集群节点不能共享数据，每个节点负责不同的数据。另外一种多活架构是共享存储集群架构，比较典型的就是Oracle RAC（全称Oracle Real Application Cluster）。在该架构中集群中多个节点运行的是同一个数据库实例，数据完全一致，并且用户层面无论从哪个节点访问，获取到的数据都是相同的。

Oracle RAC

架构图如下：

在这里插入图片描述

如何查看相关节点

select * from v$instance; 查看当前在RAC的哪个节点上
select * from v$active_instances; 查看哪些节点目前是Active状态
select * from gv$instance; 查看一共有哪些节点

查看表空间大小

查询表空间及大小：

SELECT T.TABLESPACE_NAME,ROUND(SUM(BYTES/(1024*1024)),0) TS_SIZE FROM DBA_TABLESPACES T, DBA_DATA_FILES D WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME GROUP BY T.TABLESPACE_NAME;

查询表空间物理文件及大小：

SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES/(1024*1024),0) TOTAL_SPACE FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;

查询表空间的使用情况：
方法1：

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,C.BYTES FREE,(B.BYTES*100)/A.BYTES "% USED ",(C.BYTES*100)/A.BYTES "% FREE " FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

方法2：

SELECT A.TABLESPACE_NAME "表空间名",  
B.TOTAL "表空间大小",   
A.FREE "表空间剩余大小",   
(B.TOTAL - A.FREE) "表空间使用大小",   
B.TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",   
A.FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",   
(B.TOTAL - A.FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",   
ROUND((B.TOTAL - A.FREE) / B.TOTAL, 4) * 100 "使用率 %"   
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,   
(SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B   
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME

查询SQL执行时间

查询SQL执行时间：

SELECT C.SAMPLE_TIME 执行时间,  
A.ELAPSED_TIME_DELTA / 1000000 "执行耗时(S)",  
B.SQL_TEXT SQL文本,  
TO_CHAR(SUBSTR(B.SQL_TEXT,1,400))  SQL截取  
FROM DBA_HIST_SQLSTAT A  
LEFT JOIN DBA_HIST_SQLTEXT B ON A.SQL_ID = B.SQL_ID  
LEFT JOIN DBA_HIST_ACTIVE_SESS_HISTORY C ON C.SQL_ID = B.SQL_ID  
WHERE PARSING_SCHEMA_NAME= 'SCOTT' AND C.SAMPLE_TIME IS NOT NULL  
ORDER BY C.SAMPLE_TIME DESC;

查询时间差

查询最大时间差：

select max(to_number(
substr(
(
to_timestamp(to_char(结束字段,'yyyy-mm-dd hh24:mi:ss.ff6'),'yyyy-mm-dd hh24:mi:ss.ff6') -
to_timestamp(to_char(开始字段,'yyyy-mm-dd hh24:mi:ss.ff6'),'yyyy-mm-dd hh24:mi:ss.ff6')
)
*86400000,2,9)
)
) from 表 a 
where a.开始字段 > 左区间
and a.结束字段 < 右区间

Scheduler

查询job

SELECT * FROM DBA_SCHEDULER_JOBS;

repeat_interval

Oracle 11g版本中引入了Scheduler(调度)来取代之前版本的JOB（任务）。repeat_interval是指用户定义间隔多长时间执行指定的任务。如果不指定该参数，则任务只执行一次。

repeat_interval语法

repeat_interval = regular_schedule | combined_schedule

regular_schedule = frequency_clause
[“;” interval_clause] [“;” bymonth_clause] [“;” byweekno_clause]
[“;” byyearday_clause] [“;” bydate_clause] [“;” bymonthday_clause]
[“;” byday_clause] [“;” byhour_clause] [“;” byminute_clause]
[“;” bysecond_clause] [“;” bysetpos_clause] [“;” include_clause]
[“;” exclude_clause] [“;” intersect_clause][“;” periods_clause]
[“;” byperiod_clause]

combined_schedule = schedule_list

常见子名

frequency_clause
语法如下：
frequency_clause = “FREQ” “=” ( predefined_frequency | user_defined_frequency )
predefined_frequency = “YEARLY” | “MONTHLY” | “WEEKLY” | “DAILY” |
“HOURLY” | “MINUTELY” | “SECONDLY”
user_defined_frequency = named_schedule
指定重复的类型，这个参数必须指定。各个值的含意从字面就可以理解：YEARLY指定按年重复,MONTHLY指定按月重复,WEEKLY指定按周重复,DAILY指定按日重复,HOURLY指定按小时重复,MINUTELY指定按分钟重复,SECONDLY指定按秒重复。

示例：使用MINUTELY作为重复类型：

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME => ‘tst1’,
JOB_TYPE => ‘plsql_block’,
JOB_ACTION => ‘insert into emp select * from scott.emp where empno=7396’,
REPEAT_INTERVAL => ‘freq=minutely’,
ENABLED => TRUE
);
END;
interval_clause
语法如下：
interval_clause = “INTERVAL” “=” intervalnum
intervalnum = 1 through 99
该参数指定重复的间隔，默认为1，取值范围是1-99

示例：使用MINUTELY作为重复类型，指定间隔2分钟

BEGIN  
 SYS.DBMS_SCHEDULER.CREATE_JOB(  
	 JOB_NAME        => 'tst1',  
	 JOB_TYPE        => 'plsql_block',  
	 JOB_ACTION      => 'insert into emp select * from scott.emp where empno=7396',  
	 REPEAT_INTERVAL =>  'freq=minutely;interval=2',  
	 ENABLED         => TRUE    
 );  
END;

bymonth_clause
语法如下：
bymonth_clause = “BYMONTH” “=” monthlist
monthlist = month ( “,” month)*
month = numeric_month | char_month
numeric_month = 1 | 2 | 3 … 12
char_month = “JAN” | “FEB” | “MAR” | “APR” | “MAY” | “JUN” |
“JUL” | “AUG” | “SEP” | “OCT” | “NOV” | “DEC”
该参数用于指定哪个月或哪几个月执行任务。可以用数字指定月份，也可以用三个字母简写指定月份。

示例：指定每3、6、9、12月执行job

BEGIN
 SYS.DBMS_SCHEDULER.CREATE_JOB(  
	 JOB_NAME        => 'tst1',
	 JOB_TYPE        => 'plsql_block',
	 JOB_ACTION      => 'insert into emp select * from scott.emp where empno=7396',
	 REPEAT_INTERVAL => 'freq=monthly;bymonth=3,6,9,12',
	 ENABLED         => TRUE
 );
END;

byweekno_clause
语法如下：
byweekno_clause = “BYWEEKNO” “=” weeknumber_list
weeknumber_list = weeknumber ( “,” weeknumber)*
weeknumber = [minus] weekno
weekno = 1 through 53
该参数用于指定在一年中的第几周执行job，每年有52或53周，该参数只针对FREQ=YEARLY有效。
byyearday_clause
语法如下：
byyearday_clause = “BYYEARDAY” “=” yearday_list
yearday_list = yearday ( “,” yearday)*
yearday = [minus] yeardaynum
yeardaynum = 1 through 366
该参数指定一年中的第几天执行job，有效值为1-366，也可以在数字前指定"-“代表该年的倒数第几天如”-2"表示12月30日。
bydate_clause
语法如下：
bydate_clause = “BYDATE” “=” date_list
date_list = date ( “,” date)*
date = [YYYY]MMDD [ offset | span ]
该参数用于指定哪一天执行job，格式为[YYYY]MMDD。可以用span参数指定连续的日期如bydate=0110,0111,0112,0113,0114与bydate=0110+span:5d等价。可以用offset参数对日期做调整，增加/减少几天(d)或几周(w)如bydate=0520-offset:5d，表示指定5月15日执行。
byday_clause
语法如下：
byday_clause = “BYDAY” “=” byday_list
byday_list = byday ( “,” byday)*
byday = [weekdaynum] day
weekdaynum = [minus] daynum
daynum = 1 through 53 /* if frequency is yearly /
daynum = 1 through 5 / if frequency is monthly */
day = “MON” | “TUE” | “WED” | “THU” | “FRI” | “SAT” | “SUN”
指定一周中的周几执行job，使用单词的前三个字母指定。如果指定的freq=yearly，则daynum可以是1-53，指定一年的第几周。如果freq=monthly，则daynum可以是1-5，指定一个月的第几周。使用"-"表示倒数。
bymonthday_clause
语法如下：
bymonthday_clause = “BYMONTHDAY” “=” monthday_list
monthday_list = monthday ( “,” monthday)*
monthday = [minus] monthdaynum
monthdaynum = 1 through 31
指定每月的第几天执行job，有效值为1-31，也可以使用"-"符号指定该月的倒数第几天。
byhour_clause、byminute_clause、bysecond_clause
语法如下：
byhour_clause = “BYHOUR” “=” hour_list
hour_list = hour ( “,” hour)*
hour = 0 through 23
byminute_clause = “BYMINUTE” “=” minute_list
minute_list = minute ( “,” minute)*
minute = 0 through 59
bysecond_clause = “BYSECOND” “=” second_list
second_list = second ( “,” second)*
second = 0 through 59
这些参数指定job执行时的具体的时、分、秒。byhour指定几点，byminute指定几分，bysecond指定几秒

sqlplus

sqlplus命令

sqlplus username/password@service_name
–使用本地tnsname.ora中监听
sqlplus username/password@db_ip:dp_port/service_name
–不使用本地tnsname.ora中监听

sqlplus导出文件

sqlplus username/password@service_name <<EOF
spool example.xls
代码块
@sql.sql
spool off
exit;
EOF

sqlplus设置执行参数

set colsep ‘,’;　　　 //数据集输出分隔符
set echo off;　　　 //显示每个 sql语句,缺省为 on
set feedback off;　　　 //回显本次sql命令处理的记录条数,缺省为 on
set heading off;　　　 //输出域标题,缺省为 on
set linesize 80;　　　 //单行字符个数,缺省为80
set pagesize 0;　　　 //设置每页行数,缺省为 24,为了避免分页可设定为 0
set termout off;　　　 //显示脚本中的命令的执行结果,缺省为 on
set trimout on;　　　 //去除标准输出每行的拖尾空格,缺省为 off
set trimspool on;　　　 //去除重定向(spool)输出中每行的拖尾空格,缺省为 off
set timing off;　　　 //显示每条sql命令的耗时,缺省为off
set verify off;　　　 //是否显示替代变量被替代前后的语句