网站正在维护中 模板,苏州新途网络科技有限公司,wordpress 优惠券主题,wordpress文章标签只调用一个小伙伴们是不是安装完测试库或是想练习sql的环境中#xff0c;没有官方示例库#xff0c;其实就初始化实例图形界面中的dbca#xff0c;勾选示例库#xff0c;静默安装的话制定-sampleSchema true#xff0c;甚至OCM考试中的那些HR、OE、SH这些都不知道表结构#xff0c;…小伙伴们是不是安装完测试库或是想练习sql的环境中没有官方示例库其实就初始化实例图形界面中的dbca勾选示例库静默安装的话制定-sampleSchema true甚至OCM考试中的那些HR、OE、SH这些都不知道表结构还需要练习sql、做优化是不是有点蒙。
因为23 ai RPM安装版本的测试库无官方schema每次测试都很痛苦现在将官方的Smaple Schemas sql一次性导入库演练起来。
官方描述Oracle Database Sample Schemas
Oracle Database Sample Schemas
The new and improved Oracle Database 23c Sample Schemas contain several improvements:
All active data sets have been refreshed
Order Entry (OE) has been archived
Product Media (PM) has been archived
Schemas are installed independently from each other
SYS/SYSTEM user account access is no longer required
SQL*Loader is no longer required
The scripts are compatible with Oracle Database 19c and upwards, including Oracle Database Cloud Services.
一、脚本位置与获取方式
1. 官方脚本路径下载后放入对应路径 HR/OE主脚本 --# HR
$ORACLE_HOME/demo/schema/human_resources/hr_main.sql
--# OE
$ORACLE_HOME/demo/schema/order_entry/oe_main.sql
包含用户创建、表结构、数据填充及约束。 依赖脚本 hr_cre.sql建表hr_popul.sql数据oe_cre.sql、oe_popul.sql 优先使用下面建库一键SQL
2. GitHub资源 官方示例库oracle-db-examples含历史版本脚本独立备份库hr-schema-scriptsOracle维护发个baidu共享通过网盘分享的文件db-sample-schemas-23.3.zip 链接: https://pan.baidu.com/s/1CZWuhUUPHpmTigYqz3SQGg?pwde8tr 提取码: e8tr Oracle Smaple Schema 从21c开始需手动安装脚本需从GitHub下载后放入$ORACLE_HOME/demo/schema/对应目录 二、部署步骤以HR为,OE、SH都要建表空间
1. 环境准备
CREATE TABLESPACE hr_data DATAFILE hr_data01.dbf SIZE 100M AUTOEXTEND ON;
CREATE TEMPORARY TABLESPACE hr_temp TEMPFILE hr_temp01.dbf SIZE 50M;
2. 执行主脚本
sqlplus / as sysdba $ORACLE_HOME/demo/schema/human_resources/hr_install.sql
安装过程中会交互输入 HR用户密码如 hr_password默认表空间HR_DATA临时表空间HR_TEMP [oracleOL97 ~]$ sqlplus / as sysdbaSQL*Plus: Release 23.0.0.0.0 - Production on Sat Jun 14 18:27:21 2025
Version 23.8.0.25.04Copyright (c) 1982, 2025, Oracle. All rights reserved.Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04SYSFREE show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 FREEPDB1 READ WRITE NO
SYSFREE alter session set containerFREEPDB1;Session altered.CREATE TABLESPACE hr_data2 DATAFILE hr_data01.dbf SIZE 100M AUTOEXTEND ON;
CREATE TEMPORARY TABLESPACE hr_tempTablespace created.SYSFREE 2 TEMPFILE hr_temp01.dbf SIZE 50M;Tablespace created.SYSFREE /opt/oracle/demo/schema/db-sample-schemas-23.3/human_resources/hr_install.sqlThank you for installing the Oracle Human Resources Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the hr_install.log log file.Enter a password for the user HR:Enter a tablespace for HR [USERS]: hr_data
Do you want to overwrite the schema, if it already exists? [YES|no]: YES
****** Creating REGIONS table ....
****** Creating COUNTRIES table ....
****** Creating LOCATIONS table ....
****** Creating JOB_HISTORY table ....
Table created.
Index created.Table altered.****** Creating EMP_DETAILS_VIEW view ...View created.****** Creating indexes ...
Comment created.
Session altered.
****** Populating REGIONS table ....PL/SQL procedure successfully completed.****** Populating COUNTRIES table ....PL/SQL procedure successfully completed.****** Populating LOCATIONS table ....PL/SQL procedure successfully completed.****** Populating DEPARTMENTS table ....Table altered.PL/SQL procedure successfully completed.****** Populating JOBS table ....PL/SQL procedure successfully completed.****** Populating EMPLOYEES table ....PL/SQL procedure successfully completed.****** Populating JOB_HISTORY table ....PL/SQL procedure successfully completed.Commit complete.Table altered.Trigger created.Commit complete.Installation
-------------
Verification:Table provided actual
----------- ---------- ----------
regions 5 5
countries 25 25
departments 27 27
locations 23 23
employees 107 107
jobs 19 19
job_history 10 10Thank you!
--------------------------------------------------------
The installation of the sample schema is now finished.
Please check the installation verification output above.You will now be disconnected from the database.Thank you for using Oracle Database!Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
[oracleOL97 ~]$
3. 用户解锁与授权
ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY hr_password;
-- 基础权限
GRANT CONNECT, RESOURCE, CREATE VIEW TO hr;-- 细粒度访问控制按照策略建
BEGIN DBMS_RLS.ADD_POLICY(hr,employees,hr_policy,hr,emp_policy_fn,SELECT);
END;
三、验证步骤
1. 连接与对象检查
-- 连接测试
sqlplus hr/Oracle_4Uorclpdb1 -- 表结构验证
SELECT table_name FROM user_tables; -- 应返回EMPLOYEES, DEPARTMENTS等
DESC employees; -- 查看字段结构HRlocalhost:1521/FREEPDB1 DESC EMPLOYEES;Name Null? Type----------------------------------------------------------------- -------- --------------------------------------------EMPLOYEE_ID NOT NULL NUMBER(6)FIRST_NAME VARCHAR2(20)LAST_NAME NOT NULL VARCHAR2(25)EMAIL NOT NULL VARCHAR2(25)PHONE_NUMBER VARCHAR2(20)HIRE_DATE NOT NULL DATEJOB_ID NOT NULL VARCHAR2(10)SALARY NUMBER(8,2)COMMISSION_PCT NUMBER(2,2)MANAGER_ID NUMBER(6)DEPARTMENT_ID NUMBER(4)HRlocalhost:1521/FREEPDB1 2. 数据完整性校验
-- 示例数据查询
SELECT * FROM employees WHERE employee_id 100; -- 应返回Steven King记录
SELECT COUNT(*) FROM employees; -- 107行为标准数据量-- 外键约束验证
INSERT INTO departments (department_id) VALUES (999); -- 应触发主键缺失报错
3. 权限与状态确认
SELECT username, account_status FROM dba_users WHERE usernameHR; -- 状态应为OPEN
SELECT * FROM dba_tab_privs WHERE granteeHR; -- 查看对象权限
四、实操应用场景
1. SQL/PLSQL开发测试
-- 部门平均薪资查询
SELECT d.department_name, AVG(e.salary)
FROM employees e
JOIN departments d ON e.department_id d.department_id
GROUP BY d.department_name;-- 条件编译示例调试模式开关
ALTER SESSION SET PLSQL_CCFLAGS debug:TRUE;
CREATE OR REPLACE PROCEDURE get_employee IS
BEGIN$IF $$debug $THENDBMS_OUTPUT.PUT_LINE(Debug: Query started);$END-- 业务逻辑
END;
2. 其他语言集成测试 Python连接HR import cx_Oracle
conn cx_Oracle.connect(hr, hr_password, localhost/orclpdb1)
cursor conn.cursor()
cursor.execute(SELECT first_name, salary FROM employees)
for row in cursor: print(f{row[0]}: {row[1]})
3. 安全与性能实践 权限最小化 REVOKE DBA FROM hr; -- 回收高危权限
CREATE ROLE hr_limited;
GRANT SELECT ON hr.employees TO hr_limited; -- 按需授权
索引优化
CREATE INDEX emp_dept_idx ON employees(department_id); -- 加速部门查询
EXPLAIN PLAN FOR SELECT * FROM employees WHERE last_nameKing; -- 分析执行计划
五、常见问题解决 问题 解决方案 脚本找不到 从GitHub下载db-sample-schemas并复制到$ORACLE_HOME/demo/schema/ HR用户被锁定 ALTER USER hr ACCOUNT UNLOCK; 表空间不足 扩展表空间ALTER TABLESPACE hr_data ADD DATAFILE hr_data02.dbf SIZE 200M; 文章转载自： http://www.morning.qytyt.cn.gov.cn.qytyt.cn http://www.morning.yrcxg.cn.gov.cn.yrcxg.cn http://www.morning.gnhsg.cn.gov.cn.gnhsg.cn http://www.morning.znkls.cn.gov.cn.znkls.cn http://www.morning.zlnf.cn.gov.cn.zlnf.cn http://www.morning.bdfph.cn.gov.cn.bdfph.cn http://www.morning.jcbjy.cn.gov.cn.jcbjy.cn http://www.morning.tbplf.cn.gov.cn.tbplf.cn http://www.morning.frnjm.cn.gov.cn.frnjm.cn http://www.morning.mygbt.cn.gov.cn.mygbt.cn http://www.morning.rcyrm.cn.gov.cn.rcyrm.cn http://www.morning.dyxlj.cn.gov.cn.dyxlj.cn http://www.morning.nzlqt.cn.gov.cn.nzlqt.cn http://www.morning.qpnmd.cn.gov.cn.qpnmd.cn http://www.morning.cbvlus.cn.gov.cn.cbvlus.cn http://www.morning.pinngee.com.gov.cn.pinngee.com http://www.morning.mllmm.cn.gov.cn.mllmm.cn http://www.morning.bypfj.cn.gov.cn.bypfj.cn http://www.morning.krzrg.cn.gov.cn.krzrg.cn http://www.morning.hjsrl.cn.gov.cn.hjsrl.cn http://www.morning.bhxzx.cn.gov.cn.bhxzx.cn http://www.morning.htjwz.cn.gov.cn.htjwz.cn http://www.morning.ngkgy.cn.gov.cn.ngkgy.cn http://www.morning.yrctp.cn.gov.cn.yrctp.cn http://www.morning.srmpc.cn.gov.cn.srmpc.cn http://www.morning.rhzzf.cn.gov.cn.rhzzf.cn http://www.morning.cqrenli.com.gov.cn.cqrenli.com http://www.morning.ldpjm.cn.gov.cn.ldpjm.cn http://www.morning.sqqkr.cn.gov.cn.sqqkr.cn http://www.morning.bnbtp.cn.gov.cn.bnbtp.cn http://www.morning.ngqty.cn.gov.cn.ngqty.cn http://www.morning.pzdxg.cn.gov.cn.pzdxg.cn http://www.morning.xq3nk42mvv.cn.gov.cn.xq3nk42mvv.cn http://www.morning.ygbq.cn.gov.cn.ygbq.cn http://www.morning.jwgmx.cn.gov.cn.jwgmx.cn http://www.morning.nbnpb.cn.gov.cn.nbnpb.cn http://www.morning.rnmdp.cn.gov.cn.rnmdp.cn http://www.morning.kzpxc.cn.gov.cn.kzpxc.cn http://www.morning.mbzlg.cn.gov.cn.mbzlg.cn http://www.morning.rhnn.cn.gov.cn.rhnn.cn http://www.morning.fllx.cn.gov.cn.fllx.cn http://www.morning.qfrmy.cn.gov.cn.qfrmy.cn http://www.morning.xfhms.cn.gov.cn.xfhms.cn http://www.morning.ftwlay.cn.gov.cn.ftwlay.cn http://www.morning.tfrlj.cn.gov.cn.tfrlj.cn http://www.morning.dpwcl.cn.gov.cn.dpwcl.cn http://www.morning.seoqun.com.gov.cn.seoqun.com http://www.morning.rmfwh.cn.gov.cn.rmfwh.cn http://www.morning.wiitw.com.gov.cn.wiitw.com http://www.morning.wscfl.cn.gov.cn.wscfl.cn http://www.morning.fsbns.cn.gov.cn.fsbns.cn http://www.morning.ctqbc.cn.gov.cn.ctqbc.cn http://www.morning.ssjee.cn.gov.cn.ssjee.cn http://www.morning.qqfcf.cn.gov.cn.qqfcf.cn http://www.morning.wnhgb.cn.gov.cn.wnhgb.cn http://www.morning.hbqfh.cn.gov.cn.hbqfh.cn http://www.morning.nqpxs.cn.gov.cn.nqpxs.cn http://www.morning.tgpgx.cn.gov.cn.tgpgx.cn http://www.morning.npbkx.cn.gov.cn.npbkx.cn http://www.morning.mxhcf.cn.gov.cn.mxhcf.cn http://www.morning.tnjkg.cn.gov.cn.tnjkg.cn http://www.morning.skql.cn.gov.cn.skql.cn http://www.morning.gjzwj.cn.gov.cn.gjzwj.cn http://www.morning.mxgpp.cn.gov.cn.mxgpp.cn http://www.morning.hpspr.com.gov.cn.hpspr.com http://www.morning.nqrlz.cn.gov.cn.nqrlz.cn http://www.morning.jzgxp.cn.gov.cn.jzgxp.cn http://www.morning.qhjkz.cn.gov.cn.qhjkz.cn http://www.morning.c7513.cn.gov.cn.c7513.cn http://www.morning.fjglf.cn.gov.cn.fjglf.cn http://www.morning.fbylq.cn.gov.cn.fbylq.cn http://www.morning.hyfrd.cn.gov.cn.hyfrd.cn http://www.morning.zlrsy.cn.gov.cn.zlrsy.cn http://www.morning.wyrkp.cn.gov.cn.wyrkp.cn http://www.morning.bpptt.cn.gov.cn.bpptt.cn http://www.morning.kpcxj.cn.gov.cn.kpcxj.cn http://www.morning.rpms.cn.gov.cn.rpms.cn http://www.morning.lgsqy.cn.gov.cn.lgsqy.cn http://www.morning.zxybw.cn.gov.cn.zxybw.cn http://www.morning.nydtt.cn.gov.cn.nydtt.cn
