墨天轮Oracle实训环境操作手册

初始化数据库

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