编者按：不错的东西，自用 推荐
【免责声明】本号文章仅代表个人观点，与任何公司无关，仅供参考。
编辑|SQL和数据库技术(ID:SQLplusDB)
目录：
- 初体验
- 老鸟儿实测
- 内置脚本内容
- 1.查看表空间大小
- 2. 实时的undo使用量
- 3.临时表空间的使用状况
- 4. 会话和SQL ID
- 5. 获取隐含参数值
- 6. 等待事件和SQL ID
- 7. 获取DDL
- 8.SQL Monitor 脚本
- 其他...
- 初体验建议
云时代了，一切似乎都在“云化”。
对于用户而言，好像进入了一个浏览器就可以浏览器搞定一切的时代。
现在有很多的各种数据库SQL在线运行网站，但是提供一个包含安装了数据库的在线的Linux环境的好像并不多。
听说墨天轮最近发布了“数据库在线实训平台”（https://www.modb.pro/marketlist?type=1），所以也试着尝个鲜。
初体验
Oracle作为我最为熟悉的数据库，当然首先尝试一下【Oracle 18C 在线实训环境】。
下面就开始体验之旅。
首先，1分钱购买90天的Linux测试环境。 （基本等于白送了，启动一下电脑的电费好像也不止这些。 加上31个脚本，一个字：值、值、值。 花了钱的东西好像才会珍惜，买了就要用起来）
通过网页，连接数据库--》进入实训环境。
左侧是数据库初始化的说明。 右侧是Linus的命令行界面。
老鸟儿实测
对于IT老鸟儿而言，还是随性地看看，满足满足好奇心。
我是谁？我原来是root。还有我不能看/不能干的事么？哈哈~
我在哪？看看地图不迷路！
看到docker-entrypoint-initdb.d 这个文件，可以猜测应该是docker做的镜像。
查看OS版本：cat /etc/redhat-release
OS版本是Red Hat Enterprise Linux Server release 7.9 ，这样不仅可以作为数据库的测试环境，也可以作为Linux的测试环境。
查看磁盘状态：df -h
查看安装用户oracle的环境变量：env
代码语言：javascript复制-bash-4.2# su - oracle
Last login: Thu Oct 28 20:50:03 CST 2021
[oracle@modb ~]$ env
HOSTNAME=modb
SHELL=/bin/bash
TERM=vt100
HISTSIZE=1000
USER=oracle
LS_COLORS=rs=0:di=01;...
ORACLE_SID=XE
MAIL=/var/spool/mail/oracle
PATH=/opt/oracle/product/18c/dbhomeXE/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/opt/oracle/product/18c/dbhomeXE/bin
PWD=/home/oracle
HISTCONTROL=ignoredups
SHLVL=1
HOME=/home/oracle
LOGNAME=oracle
ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE
_=/usr/bin/env
按照左侧的方法，一步一步地操作，相信小白也可以很快入手，具体内容如下：
代码语言：javascript复制初始化数据库
--Oracle Express Edition (XE)
--18C (18.4.0)
--默认创建1个PDB
--第一次进入需等待初始化建库完成
--预计5分钟
su - oracle
--查看建库进度
tail -f /opt/oracle/diag/rdbms/xe/XE/trace/alert_XE.log
--检查实例状态为open
sqlplus / as sysdba
select status from v$instance;
show pdbs
--初始化HR Schema Demo
alter session set container=xepdb1;
@?/demo/schema/human_resources/hr_main.sql
--依次输入密码，默认表空间，日志目录
hr2021
USERS
TEMP
$ORACLE_HOME/demo/schema/log/
exit
HR用户登录
--检查监听
lsnrctl status
sqlplus hr/hr2021@modb:1521/xepdb1
col TABLE_NAME for a50
select table_name,num_rows from user_tables;
SELECT e.first_name || ' ' || e.last_name "Name",
TRUNC(MONTHS_BETWEEN(j.end_date, j.start_date)) "Months Worked"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY "Months Worked";
exit
常用管理脚本
--内置了常用的34个脚本在/home/oracle/admin目录下
cd /home/oracle/admin/
sqlplus / as sysdba
SQL> !ls
2pc_clean.sql bind_noused.sql segment_size.sql sqlhis_awr.sql wait_event.sql
ash_sql_line_id.sql cursor_purge.sql session_kill.txt sqlinfo_total.sql wait_event_block.sql
ash_top_sql_event.txt ddl_metadata.sql session_sid.sql tablespace_used.sql wait_event_hash.sql
ash_used.txt dml_get.sql session_spid.sql tabstat.sql wait_event_sqlid.sql
awr_db_time.sql fra_get.sql shared_pool_free.sql temp_used.txt wait_session_hash.sql
awr_event_histogram.txt param_get.sql sql_monitor.sql transaction_get.sql wait_session_sqlid.sql
awr_metric_name.sql redo_switch.sql sql_profile.txt undo_used.sql
SQL> @wait_event.sql
INST_ID EVENT COUNT(*)
---------- ---------------------------------------------------------------- ----------
1 SQL*Net message to client 1
SQL>
SQL> @tablespace_used.sql
------------------------------ --------------- --------------- ----------
SYSTEM 0.81 0.00 99.44%
SYSAUX 0.49 0.03 93.96%
UNDOTBS1 0.07 0.00 92.95%
USERS 0.00 0.00 20.00%
TABLESPACE_NAME SIZE_G FREE_G USED_PCT
Tablespace Total(MB) Used(MB) Free(MB) Pct. Free(%)
------------------------------ ---------- ---------- ---------- ------------
TEMP 33 0 33 100
SQL>
内置脚本内容
体验了一下各种命令，我还是非常有兴趣地看看内置了的常用的34个脚本的内容。
1.查看表空间大小
代码语言：javascript复制[oracle@modb admin]$ cat tablespace_use.dsql
--表空间
set linesize 220 pagesize 10000
COL SIZE_G FOR A15
COL FREE_G FOR A15
COL USED_PCT FOR A10
COL TABLESPACE_NAME FOR A30
SELECT d.tablespace_name,
to_char(nvl(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.00') size_g,
to_char(nvl(f.bytes, 0) / 1024 / 1024 / 1024, '99,999,990.00') free_g,
to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00') || '%' used_pct
FROM dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name( )
AND d.tablespace_name = f.tablespace_name( )
AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
ORDER BY 4 DESC;
--临时表空间
select df.tablespace_name "Tablespace",
df.totalspace "Total(MB)",
nvl(FS.UsedSpace, 0) "Used(MB)",
(df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
FROM dba_TEMP_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace
FROM gV$temp_extent_pool
GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name( );
2. 实时的undo使用量
代码语言：javascript复制[oracle@modb admin]$ cat undo_used.sql
--实时的undo使用量
set linesize 220
set pagesize 1000
col username for a20
col module for a40
col sql_id for a15
col status for a10
col machine for a20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select *
from (select start_time,
username,
s.MACHINE,
s.OSUSER,
r.name,
ubafil, --Undo block address (UBA) filenum
ubablk, --UBA block number
t.status,
(used_ublk * 8192 / 1024) kbtye,
used_urec,
s1.SQL_ID,
substr(s1.SQL_TEXT,0,20)
from v$transaction t, v$rollname r, v$session s, v$sqlarea s1
where t.xidusn = r.usn
and s.saddr = t.ses_addr
and s.sql_id = s1.sql_id( )
order by 9 desc)
where rownum <= 10;
3.临时表空间的使用状况
代码语言：javascript复制[oracle@modb admin]$ cat temp_used.txt
查询temp表空间使用率：
select df.tablespace_name "Tablespace",
df.totalspace "Total(MB)",
nvl(FS.UsedSpace, 0) "Used(MB)",
(df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
FROM dba_TEMP_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace
FROM gV$temp_extent_pool
GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name( )
查询实时使用temp表空间的sql_id和sid：
set linesize 260 pagesize 1000
col machine for a40
col program for a40
SELECT se.username,
sid,
serial#,
se.sql_id
machine,
program,
tablespace,
segtype,
(su.BLOCKS*8/1024/1024) GB
FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr
order by su.BLOCKS desc;
/*需要注意的是这里查询sql_id要用v$session视图的sql_id，而不要用v$sort_usage视图的sql_id，v$sort_usage视图里面的sql_id是不准确的*/
查询历史的temp表空间的使用的SQL_ID
select a.SQL_ID,
a.SAMPLE_TIME,
a.program,
sum(trunc(a.TEMP_SPACE_ALLOCATED / 1024 / 1024)) MB
from v$active_session_history a
where TEMP_SPACE_ALLOCATED is not null
and sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
group by a.sql_id,a.SAMPLE_TIME,a.PROGRAM
order by 2 asc,4 desc;
4. 会话和SQL ID
代码语言：javascript复制[oracle@modb admin]$ more wait_session_sqlid.sql
set linesize 260
set pagesize 1000
col sid for 99999
col spid for a8
col event for a30
col module for a35
col machine for a15
col username for a10
col holder for a10
col final for a10
col sql_id for a15
col exec_gets for 99999999
col seconds for a5
col object_id for 999999
col param for a30
col sql_text for a6
col PGA_USE for 9999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select a.sid,
a.username,
a.machine,
a.module,
a.event,
a.sql_id,
round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets,
a.ROW_WAIT_OBJ# object_id,
a.BLOCKING_INSTANCE||'_'||a.blocking_session holder,
a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final,
to_char(LAST_CALL_ET) seconds,
a.p1 || '_' || a.p2 || '_' || a.p3 param,
b.spid,
trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE,
substr(c.sql_text,0,6) sql_text
from v$session a, v$process b,v$sql c
where a.paddr = b.addr( )
and a.status = 'ACTIVE'
and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and
a.wait_class = 'Idle')
and a.sql_id=c.sql_id( )
and a.sql_child_number=c.CHILD_NUMBER( )
order by a.sql_id, a.machine
/
5. 获取隐含参数值
代码语言：javascript复制$ more param_get.sql
set linesize 220 pagesize 1000
col ksppinm for a40
col ksppstvl for a40
col ksppdesc for a100
select a.ksppinm, a.ksppdesc,b.ksppstvl,a.inst_id
from sys.x$ksppi a, sys.x$ksppcv b
where upper(a.ksppinm) like upper('%¶m%')
and a.indx = b.indx
order by a.ksppinm;
6. 等待事件和SQL ID
代码语言：javascript复制cat wait_event_sqlid.sql
set linesize 220
set pagesize 1000
select a.inst_id,a.event, a.sql_id,max(round(decode(c.executions,0,c.buffer_gets,c.buffer_gets/c.executions))) exec_gets, count(*)
from gv$session a,gv$sql c
where a.status='ACTIVE'
and not (a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle')
and a.sql_id=c.sql_id( )
and a.sql_child_number=c.CHILD_NUMBER( )
and a.inst_id=c.inst_id
group by a.inst_id,a.event, a.sql_id
order by a.inst_id,count(*) desc, a.sql_id
;
7. 获取DDL
代码语言：javascript复制$ more ddl_metadata.sql
set linesize 260
set long 999999
set pagesize 1000
select dbms_metadata.get_ddl(upper('&object_type'),upper('&object_name'),upper('&owner')) FROM DUAL;
8.SQL Monitor 脚本
代码语言：javascript复制$ cat sql_monitor.sql
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
SQL_ID => '&SQL_ID',
TYPE => 'TEXT',
REPORT_LEVEL => 'ALL') AS REPORT
FROM dual;
其他
等等...
初体验建议
- 版本最新化
可以看到这个实训环境的数据库版本是Oracle 18C Express Edition也就是精简的免费版，现在最新的版本是 21c Express Edition，可以适当升升级！ 不过对于数据库的核心技术而言各版本基本差距不大，以实验为目的版本差别也无伤大雅。
- 支持手机浏览。
还真想在手机上也能够尝试一下测试数据库，但是很遗憾排版基本是混乱的。 如果能够稍微调整一下手机的适应排版就完美了。
3.部分乱码
查看表空间大小的脚本（tablespace_use.dsql）中的中文是乱码。
