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

SQLplusDB

发布于 2022-08-22 13:29:08

1.2K0

发布于 2022-08-22 13:29:08

文章被收录于专栏：Oracle数据库技术

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

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

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

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

初始化数据库

--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.查看表空间大小

[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使用量

[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.临时表空间的使用状况

[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

[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. 获取隐含参数值

$ 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('%&param%')
   and a.indx = b.indx 
   order by a.ksppinm;

6. 等待事件和SQL ID

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

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