上一篇【第22篇】Oracle用户与权限管理详解下一篇【第24篇】Oracle数据字典与动态性能视图详解摘要模式Schema是数据库对象的集合包含表、索引、视图、序列、同义词、存储过程等。本文详细讲解Oracle各类模式对象的创建、修改、删除与管理涵盖表分区、索引优化、视图应用、序列生成等实用技能帮助开发人员和管理员高效管理数据库对象。一、模式与对象概述1.1 什么是模式Schema模式是数据库对象的逻辑集合与用户账户一一对应。当用户被创建时同名模式同时创建。-- 查看所有模式用户SELECTusernameASschema_name,created,default_tablespaceFROMdba_usersORDERBYusername;-- 查看当前模式SELECTSYS_CONTEXT(USERENV,CURRENT_SCHEMA)AScurrent_schemaFROMDUAL;-- 切换当前模式无需更改用户ALTERSESSIONSETCURRENT_SCHEMAscott;1.2 模式对象类型对象类型说明创建命令TABLE表CREATE TABLEINDEX索引CREATE INDEXVIEW视图CREATE VIEWSEQUENCE序列CREATE SEQUENCESYNONYM同义词CREATE SYNONYMPROCEDURE存储过程CREATE PROCEDUREFUNCTION函数CREATE FUNCTIONPACKAGE包CREATE PACKAGETRIGGER触发器CREATE TRIGGERTYPE自定义类型CREATE TYPE-- 查看某个模式下的所有对象SELECTobject_type,COUNT(*)FROMdba_objectsWHEREownerSCOTTGROUPBYobject_typeORDERBYobject_type;二、表Table管理2.1 创建普通表-- 基本语法CREATETABLE[schema.]table_name(column1 datatype[DEFAULTexpr][column_constraint],column2 datatype[DEFAULTexpr][column_constraint],...[table_constraint])[TABLESPACEtablespace_name][PCTFREE n][PCTUSED n][STORAGE(...)][CACHE|NOCACHE];-- 示例创建员工表CREATETABLEscott.emp(empno NUMBER(4)PRIMARYKEY,ename VARCHAR2(10)NOTNULL,job VARCHAR2(9),mgr NUMBER(4),hiredateDATEDEFAULTSYSDATE,sal NUMBER(7,2)CHECK(sal0),comm NUMBER(7,2),deptno NUMBER(2)REFERENCESscott.dept(deptno))TABLESPACEusers_data PCTFREE10PCTUSED40STORAGE(INITIAL64KNEXT64K MINEXTENTS1MAXEXTENTS UNLIMITED);2.2 创建分区表-- 范围分区按日期CREATETABLEscott.orders(order_id NUMBER(10),customer_id NUMBER(6),order_dateDATE,total_amt NUMBER(10,2))TABLESPACEusers_dataPARTITIONBYRANGE(order_date)(PARTITIONp2024_q1VALUESLESS THAN(TO_DATE(2024-04-01,YYYY-MM-DD))TABLESPACEusers_data,PARTITIONp2024_q2VALUESLESS THAN(TO_DATE(2024-07-01,YYYY-MM-DD))TABLESPACEusers_data,PARTITIONp2024_q3VALUESLESS THAN(TO_DATE(2024-10-01,YYYY-MM-DD))TABLESPACEusers_data,PARTITIONp2024_q4VALUESLESS THAN(TO_DATE(2025-01-01,YYYY-MM-DD))TABLESPACEusers_data);-- 列表分区CREATETABLEscott.customer_region(cust_id NUMBER(6),cust_name VARCHAR2(30),region VARCHAR2(10))PARTITIONBYLIST(region)(PARTITIONp_eastVALUES(EAST,NORTHEAST),PARTITIONp_westVALUES(WEST,NORTHWEST,SOUTHWEST),PARTITIONp_otherVALUES(DEFAULT));-- 哈希分区CREATETABLEscott.inventory(item_id NUMBER(8),item_name VARCHAR2(30),quantity NUMBER(5))PARTITIONBYHASH(item_id)PARTITIONS4STOREIN(users_data,users_index);2.3 修改表-- 添加列ALTERTABLEscott.empADD(email VARCHAR2(50));-- 修改列数据类型ALTERTABLEscott.empMODIFY(ename VARCHAR2(20));-- 修改列默认值ALTERTABLEscott.empMODIFY(hiredateDEFAULTSYSDATE);-- 删除列ALTERTABLEscott.empDROPCOLUMNemail;-- 重命名列Oracle 9iALTERTABLEscott.empRENAMECOLUMNenameTOemployee_name;-- 重命名表ALTERTABLEscott.empRENAMETOemployees;-- 将表移动到其他表空间ALTERTABLEscott.emp MOVETABLESPACEusers_data2;-- 启用/禁用约束ALTERTABLEscott.empENABLECONSTRAINTsys_c001234;ALTERTABLEscott.empDISABLECONSTRAINTsys_c001234;2.4 删除表-- 删除表放入回收站DROPTABLEscott.emp;-- 删除表直接删除不放入回收站DROPTABLEscott.empPURGE;-- 删除表并级联删除约束DROPTABLEscott.empCASCADECONSTRAINTS;三、索引Index管理3.1 创建索引-- 创建B树索引默认CREATEINDEXscott.idx_emp_enameONscott.emp(ename)TABLESPACEusers_index;-- 创建唯一索引CREATEUNIQUEINDEXscott.idx_emp_emailONscott.emp(email)TABLESPACEusers_index;-- 创建复合索引CREATEINDEXscott.idx_emp_dept_jobONscott.emp(deptno,job)TABLESPACEusers_index;-- 创建函数索引CREATEINDEXscott.idx_emp_upper_enameONscott.emp(UPPER(ename))TABLESPACEusers_index;-- 创建位图索引适用于低基数数据CREATEBITMAPINDEXscott.idx_emp_jobONscott.emp(job)TABLESPACEusers_index;3.2 创建分区索引-- 本地分区索引与表分区一一对应CREATEINDEXscott.idx_orders_customerONscott.orders(customer_id)LOCALTABLESPACEusers_index;-- 全局分区索引CREATEINDEXscott.idx_orders_dateONscott.orders(order_date)GLOBALPARTITIONBYRANGE(order_date)(PARTITIONp_before_2024VALUESLESS THAN(TO_DATE(2024-01-01,YYYY-MM-DD)),PARTITIONp_in_2024VALUESLESS THAN(TO_DATE(2025-01-01,YYYY-MM-DD)),PARTITIONp_futureVALUESLESS THAN(MAXVALUE))TABLESPACEusers_index;3.3 修改和删除索引-- 重建索引修复碎片、释放空间ALTERINDEXscott.idx_emp_ename REBUILDTABLESPACEusers_index;-- 在线重建索引不阻塞DMLALTERINDEXscott.idx_emp_ename REBUILD ONLINE;-- 合并索引减少碎片ALTERINDEXscott.idx_emp_enameCOALESCE;-- 重命名索引ALTERINDEXscott.idx_emp_enameRENAMETOidx_emp_name;-- 删除索引DROPINDEXscott.idx_emp_ename;3.4 索引监控-- 启用索引监控ALTERINDEXscott.idx_emp_ename MONITORINGUSAGE;-- 查看索引使用情况SELECTindex_name,table_name,used,start_monitoring,end_monitoringFROMv$object_usageWHEREindex_nameIDX_EMP_ENAME;-- 禁用索引监控ALTERINDEXscott.idx_emp_ename NOMONITORINGUSAGE;四、视图View管理4.1 创建视图-- 创建简单视图CREATEVIEWscott.vw_emp_deptASSELECTe.empno,e.ename,e.job,e.sal,d.dname,d.locFROMscott.emp eJOINscott.dept dONe.deptnod.deptno;-- 创建只读视图CREATEVIEWscott.vw_emp_salaryASSELECTempno,ename,sal,comm,salcommAStotal_incomeFROMscott.empWITHREADONLY;-- 创建检查约束视图CREATEVIEWscott.vw_emp_salesASSELECTempno,ename,sal,deptnoFROMscott.empWHEREdeptno30WITHCHECKOPTION;4.2 修改和删除视图-- 重新编译视图ALTERVIEWscott.vw_emp_dept COMPILE;-- 删除视图DROPVIEWscott.vw_emp_dept;五、序列Sequence管理5.1 创建序列-- 基本语法CREATESEQUENCE[schema.]sequence_name[INCREMENTBYn][STARTWITHn][MAXVALUE n|NOMAXVALUE][MINVALUE n|NOMINVALUE][CYCLE|NOCYCLE][CACHE n|NOCACHE][ORDER|NOORDER];-- 示例创建员工ID序列CREATESEQUENCE scott.seq_empnoSTARTWITH8000INCREMENTBY1MAXVALUE9999NOCACHE NOCYCLE;5.2 使用序列-- 获取下一个值SELECTscott.seq_empno.NEXTVALFROMDUAL;-- 获取当前值SELECTscott.seq_empno.CURRVALFROMDUAL;-- 在INSERT中使用INSERTINTOscott.emp(empno,ename,job,sal)VALUES(scott.seq_empno.NEXTVAL,SMITH,CLERK,800);-- 修改序列ALTERSEQUENCE scott.seq_empno INCREMENTBY10;-- 删除序列DROPSEQUENCE scott.seq_empno;六、同义词Synonym管理6.1 创建同义词-- 创建私有同义词CREATESYNONYM empFORscott.emp;-- 创建公有同义词所有用户可访问CREATEPUBLICSYNONYM deptFORscott.dept;-- 创建远程同义词指向远程数据库对象CREATESYNONYM remote_empFORempremote_db;6.2 删除同义词-- 删除私有同义词DROPSYNONYM emp;-- 删除公有同义词DROPPUBLICSYNONYM dept;七、表分区管理7.1 添加和删除分区-- 添加分区ALTERTABLEscott.ordersADDPARTITIONp2025_q1VALUESLESS THAN(TO_DATE(2025-04-01,YYYY-MM-DD))TABLESPACEusers_data;-- 删除分区数据也一并删除ALTERTABLEscott.ordersDROPPARTITIONp2024_q1;-- 删除分区但保留数据ALTERTABLEscott.ordersDROPPARTITIONp2024_q1UPDATEGLOBALINDEXES;7.2 分区数据操作-- 截断分区ALTERTABLEscott.ordersTRUNCATEPARTITIONp2024_q1;-- 移动分区到另一个表空间ALTERTABLEscott.orders MOVEPARTITIONp2024_q1TABLESPACEusers_data2;-- 拆分分区ALTERTABLEscott.orders SPLITPARTITIONp2025_q1 AT(TO_DATE(2025-02-01,YYYY-MM-DD))INTO(PARTITIONp2025_jan,PARTITIONp2025_feb_q1);-- 合并分区ALTERTABLEscott.ordersMERGEPARTITIONS p2025_jan,p2025_feb_q1INTOPARTITIONp2025_q1;八、对象依赖与状态管理8.1 查看对象依赖-- 查看对象依赖关系SELECTname,type,referenced_name,referenced_typeFROMdba_dependenciesWHEREownerSCOTTORDERBYname,referenced_name;-- 查看无效对象SELECTowner,object_name,object_type,statusFROMdba_objectsWHEREstatusINVALIDORDERBYowner,object_type,object_name;8.2 重新编译无效对象-- 重新编译单个对象ALTERPROCEDUREscott.calculate_bonus COMPILE;-- 批量重新编译无效对象BEGINFORrecIN(SELECTowner,object_name,object_typeFROMdba_objectsWHEREstatusINVALID)LOOPBEGINIFrec.object_typePROCEDURETHENEXECUTEIMMEDIATEALTER PROCEDURE ||rec.owner||.||rec.object_name|| COMPILE;ELSIF rec.object_typeFUNCTIONTHENEXECUTEIMMEDIATEALTER FUNCTION ||rec.owner||.||rec.object_name|| COMPILE;ELSIF rec.object_typePACKAGETHENEXECUTEIMMEDIATEALTER PACKAGE ||rec.owner||.||rec.object_name|| COMPILE;ELSIF rec.object_typeVIEWTHENEXECUTEIMMEDIATEALTER VIEW ||rec.owner||.||rec.object_name|| COMPILE;ENDIF;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(Failed to compile ||rec.owner||.||rec.object_name||: ||SQLERRM);END;ENDLOOP;END;/九、最佳实践9.1 表设计原则选择合适的数据类型使用VARCHAR2而非CHAR使用NUMBER而非FLOAT使用约束主键、外键、NOT NULL、CHECK约束保证数据完整性合理分区大表1000万行考虑分区使用ASSM表空间自动段空间管理9.2 索引优化原则选择性高的列建索引唯一值多的列适合建索引避免过多索引每个DML操作都会更新索引使用复合索引将经常一起查询的列放在一个索引中定期重建碎片化的索引9.3 对象命名规范表 T_table_name 或 table_name_t 视图 VW_view_name 或 view_name_v 索引 IDX_table_name_column_name 序列 SEQ_table_name 存储过程 SP_procedure_name 或 procedure_name_p 函数 FN_function_name 或 function_name_f 包 PKG_package_name 触发器 TRG_table_name_event十、总结模式对象管理的核心要点表管理创建、修改、分区表、约束管理索引管理B树索引、位图索引、分区索引、索引重建视图管理简单视图、只读视图、WITH CHECK OPTION序列管理创建、使用、修改、删除同义词管理私有同义词、公有同义词分区管理添加、删除、拆分、合并分区对象依赖查看依赖关系、重新编译无效对象最佳实践表设计、索引优化、命名规范上一篇【第22篇】Oracle用户与权限管理详解下一篇【第24篇】Oracle数据字典与动态性能视图详解参考资料《Oracle 11g数据库管理员指南》— 刘宪军著Oracle官方文档Database Administrator’s Guide - Managing Schema ObjectsOracle官方文档Database Concepts - Schema Objects