大连建设委员会网站游戏代理怎么做

小伙伴们是不是安装完测试库或是想练习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
$ORACLE_HOME/demo/schema/human_resources/hr_main.sql
--# OE
$ORACLE_HOME/demo/schema/order_entry/oe_main.sql

优先使用下面建库一键SQL

2. GitHub资源​

二、部署步骤（以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

[oracle@OL97 ~]$ 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.04SYS@FREE> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 FREEPDB1                       READ WRITE NO
SYS@FREE> alter session set container=FREEPDB1;Session altered.CREATE TABLESPACE hr_data2    DATAFILE 'hr_data01.dbf' SIZE 100M AUTOEXTEND ON;
CREATE TEMPORARY TABLESPACE hr_tempTablespace created.SYS@FREE>   2    TEMPFILE 'hr_temp01.dbf' SIZE 50M;Tablespace created.SYS@FREE>  @/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
[oracle@OL97 ~]$

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_4U@orclpdb1  -- 表结构验证
SELECT table_name FROM user_tables;  -- 应返回EMPLOYEES, DEPARTMENTS等
DESC employees;  -- 查看字段结构HR@localhost: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)HR@localhost: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 username='HR';  -- 状态应为OPEN
SELECT * FROM dba_tab_privs WHERE grantee='HR';  -- 查看对象权限

四、实操应用场景

​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. 其他语言集成测试

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_name='King';  -- 分析执行计划

五、常见问题解决 

问题	解决方案
脚本找不到​	从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;