浏览器搞定一切的云时代:在线Oracle数据库运行环境+可访问Linux主机环境

Oracle数据库技术 2021-10-29

1426

编者按：不错的东西，自用+推荐

【免责声明】本公众号文章仅代表个人观点，与任何公司无关，仅供参考。

编辑|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

-bash-4.2# su - oracleLast login: Thu Oct 28 20:50:03 CST 2021[oracle@modb ~]$ envHOSTNAME=modbSHELL=/bin/bashTERM=vt100HISTSIZE=1000USER=oracleLS_COLORS=rs=0:di=01;...ORACLE_SID=XEMAIL=/var/spool/mail/oraclePATH=/opt/oracle/product/18c/dbhomeXE/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/opt/oracle/product/18c/dbhomeXE/binPWD=/home/oracleHISTCONTROL=ignoredupsSHLVL=1HOME=/home/oracleLOGNAME=oracleORACLE_HOME=/opt/oracle/product/18c/dbhomeXE_=/usr/bin/env

按照左侧的方法，一步一步地操作，相信小白也可以很快入手，具体内容如下：

初始化数据库--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--检查实例状态为opensqlplus  as sysdbaselect status from v$instance;show pdbs--初始化HR Schema Demoalter session set container=xepdb1;@?/demo/schema/human_resources/hr_main.sql--依次输入密码，默认表空间，日志目录hr2021USERSTEMP$ORACLE_HOME/demo/schema/log/exitHR用户登录--检查监听lsnrctl statussqlplus hr/hr2021@modb:1521/xepdb1col TABLE_NAME for a50select 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 jWHERE e.employee_id = j.employee_idORDER BY "Months Worked";exit常用管理脚本--内置了常用的34个脚本在/home/oracle/admin目录下cd home/oracle/admin/sqlplus  as sysdbaSQL> !ls2pc_clean.sql            bind_noused.sql   segment_size.sql      sqlhis_awr.sql       wait_event.sqlash_sql_line_id.sql      cursor_purge.sql  session_kill.txt      sqlinfo_total.sql    wait_event_block.sqlash_top_sql_event.txt    ddl_metadata.sql  session_sid.sql       tablespace_used.sql  wait_event_hash.sqlash_used.txt             dml_get.sql       session_spid.sql      tabstat.sql          wait_event_sqlid.sqlawr_db_time.sql          fra_get.sql       shared_pool_free.sql  temp_used.txt        wait_session_hash.sqlawr_event_histogram.txt  param_get.sql     sql_monitor.sql       transaction_get.sql  wait_session_sqlid.sqlawr_metric_name.sql      redo_switch.sql   sql_profile.txt       undo_used.sqlSQL> @wait_event.sql   INST_ID EVENT                                                              COUNT(*)---------- ---------------------------------------------------------------- ----------         1 SQL*Net message to client                                                 1SQL> 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_PCTTablespace                      Total(MB)   Used(MB)   Free(MB) Pct. Free(%)------------------------------ ---------- ---------- ---------- ------------TEMP                                   33          0         33          100SQL>

内置脚本内容

体验了一下各种命令，我还是非常有兴趣地看看内置了的常用的34个脚本的内容。

1.查看表空间大小

[oracle@modb admin]$ cat tablespace_use.dsql--表空间set linesize 220 pagesize 10000COL SIZE_G FOR A15COL FREE_G FOR A15COL USED_PCT FOR A10COL TABLESPACE_NAME FOR A30SELECT 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使用量

[oracle@modb admin]$ cat undo_used.sql--实时的undo使用量set linesize 220set pagesize 1000col username for a20col module for a40col sql_id for a15col status for a10col machine for a20alter 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.临时表空间的使用状况

[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 1000col machine for a40col program for a40SELECT 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_IDselect 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

[oracle@modb admin]$ more wait_session_sqlid.sqlset linesize 260set pagesize 1000col sid for 99999col spid for a8col event for a30col module for a35col machine for a15col username for a10col holder for a10col final for a10col sql_id for a15col exec_gets for 99999999col seconds for a5col object_id for 999999col param for a30col sql_text for a6col PGA_USE for 9999alter 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. 获取隐含参数值

$ more param_get.sqlset linesize 220 pagesize 1000col ksppinm for a40col ksppstvl for a40col ksppdesc for a100select a.ksppinm, a.ksppdesc,b.ksppstvl,a.inst_id  from sys.x$ksppi a, sys.x$ksppcv b where upper(a.ksppinm) like upper('%&param%')   and a.indx = b.indx    order by a.ksppinm;

6. 等待事件和SQL ID

cat wait_event_sqlid.sqlset linesize 220set pagesize 1000select 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

$ 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 脚本

$ 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;