ISBN 10:  0-9844282-2-4 ISBN 13: 978-0-9844282-2-9   Library of Congress Number: 2011937947 656 pages Perfect Bind- 9x7 Publication Date: 911 Shelving:  Databases/Oracle Oracle In-Focus Series #41

Advanced Oracle DBMS Packages The Definitive Reference Paulo Portugal Retail Price $39.95 /  Canadian $43.95   Order now at 30% off and get access to the code depot! Only $27.95 (30% off)         Key Features About the Author Reader Comments Table of Contents Index Topics Errata

Oracle continues to implement amazing programs to aid the Oracle DBA.  This book shows how powerful Oracle utilities are encapsulated into special DBMS packages.  Properly used, these DBMS packages can aid the DBA in every aspect of their job.  Specifically, DBMS packages provide the underlying foundation for many of the Oracle Enterprise Manager (OEM) screens; therefore, a skilled DBA with in-depth knowledge of DBMS packages can learn to write and apply sophisticated scripts to replicate the online functionality in a batch mode. Knowing when and how to use DBMS packages is key to a DBA’s success in administering and monitoring their database.  This book contains insider information on how to determine which Oracle packages are best to use in real world situations.  The examples supplied in this book will be a powerful tool for any experienced DBA or developer interested in escaping from a reliance on queries or a GUI interface.  The detail given in this book is unlike any others on the market today. This book is not for beginners.  These are powerful utilities to be used only by experienced DBAs and developers who need to perform powerful system-wide operating system internal operations on their Oracle databases.

Expert secrets               & hidden             tips & tricks     • Understand how to automate database management with Oracle DBMS packages. • Learn how to write scripts that replicate the functionality of OEM screens. • See working examples of all important Oracle DBMS package utilities. • Discover the secrets used by professional DBA's to perform advanced diagnostics. • Learn how to use DBMS packages when a GUI interface is not available. • Learn how using DBMS packages can be a more powerful choice than queries or GUI interfaces. •  Get insider information on real world DBMS packages used by the best DBAs in the industry.

About the Author:

Paulo Portugal

Paulo Ferreira Portugal is a DBA with a decade of experience in IT and has worked as a DBA for 8 years. He is an Oracle Certified Professional (9i and 10g); IBM DB2 Certified (8 and 9 “Viper”); and an Oracle 11i Applications Database Administrator Certified Professional. Currently, Paulo works as Senior Applications DBA for F2C Consultoria in Rio de Janeiro/Brazil. F2C serves one of Oracle E-Business’ largest clients in Brazil. He has participated in the Oracle Beta Test 11i project using Data Guard, and is a specialist in High Availability tools like Oracle Data Guard, Oracle Streams and Oracle RAC. His clients in Brazil demand the most sophisticated services using tools like Oracle E-Business Suite 11i, Oracle OTM, Oracle Retail and Oracle BPEL with Oracle RAC 10g. Paulo greatly enjoys what he does and is always improving his technical knowledge by attending events like Oracle Open World - San Francisco (2005 and 2006) and IBM Information on Demand – Los Angeles (2006).

Technical Editor:

Gabriel Rosales

Gabriel Rosales, M.Sc.  has over 10 years of IT and more than 6 of extensive experience with Oracle. Mr. Rosales has been an instructor at Oracle University for the Administration, Database Security and Oracle Tuning courses.  He was awarded the best IT Project Manager Profile by the Costa Rican Chamber of ICTs in 2006. Mr. Rosales holds a Masters Degree in IT with mention on Project Management from ULACIT and a Bachelor Degree in Computing Sciences from UCR.  Currently, he is the DBA of Mercado de Valores, Costa Rica’s most important private brokerage firm and is also an IT Masters Professor in ULACIT, one of the most recognized private universities of CR. Passionate about IT, Mr. Rosales also possesses important achievements and awards in areas such as educational software and C/C++ development.  He has participated as a speaker in many respected forums, including Oracle OTN Tour Latin America 2010.

Table of Contents:

Chapter 1:  Introduction to Oracle DBMS Packages              Oracle Packages               History of Packages with PL/SQL Language           Advantages and Benefits in the Use of Packages               Packages Internals          Chapter 2:  Table Index and Tablespace       Package dbms_errlog    Package dbms_iot           Procedure  build_chain_rows_table        Procedure build_exceptions_table          Package dbms_lob          Package dbms_pclxutil  Package dbms_redefinition        Package dbms_rowid     Package dbms_space     Function asa_recommendations               Procedure unused_space            Package dbms_space_admin     Procedure           Procedure tablespace_migrate_to_local               Procedure assm_segment_synchwm     Procedure           Procedures tablespace_rebuild_quotas tablespace_rebuild_bitmaps and tablespace_verify       Package utl_compress   Procedure lz_compress and lz_uncompress                     Chapter 3:  Oracle Security             Package dbms_crypto   Checking for Modifications on Stored PL/SQL      Storing Encrypted Data into a varchar Column     Package dbms_change_notification        Package dbms_distributed_trust_admin              Package dbms_fga          Procedure add_policy    Procedure drop_policy  Procedure enable_policy             Procedure disable_policy             Package dbms_obfuscation_toolkit         Package dbms_rls            Tools Used to Prevent Access to the Database   Package dbms_wms       Features of Oracle Workspace Manager                             Chapter 4:  Oracle File & Network Mgt Package utl_file                Using utl_mail in Oracleg              How to Send Emails with Attachments   Package utl_raw               Package utl_ref                             Chapter 5:  Oracle Tuning                Package dbms_advisor  Package dbms_application_info                Package dbms_aw_stats              Package dbms_connection_pool              Package dbms_libcache                Package dbms_monitor                Easy SQL Analysis Using session_trace_enable Procedure             How to Trace a Specific Client Session on a Multi-tier Environment           How to Enable Trace for All SQL*Plus Sessions    Package dbms_odci        Package dbms_outln      Package dbms_profiler Oracle:  Starting a Profiling Session           Analyzing dbms_profiler Data    Package dbms_result_cache      Package dbms_xplan     Package dbms_spm        Package dbms_sql           The bind_variable() Procedures                The close_cursor() Procedure    The column_value() Procedures               Package dbms_sqltune Create a Tuning Task Using a Trace File   Package dbms_stat_funcs           Package dbms_stats       Package dbms_stats.create_extended_stats     Package dbms_storage_map     Package dbms_trace      Package dbms_workload_repository                  Chapter 6:  Oracle Backup and Recovery   Package dbms_datapump           How to Export a Full Database    How to Create a Dump Procedure to Automate Dump Operations            Monitoring Dump Jobs Using Queries     Package dbms_file_transfer       Package dbms_transaction          Package dbms_flashback             How to Recover Deleted Rows From a Table       How to Backout a Transaction and Its Dependents           Programs dbms_logmnr and dbms_logmnr_d

How to Recover Deleted Historical Data Using Log Miner               Programs dbms_logmnr_cdc_publish and dbms_logmnr_cdc_subscribe               How to Configure Change Data Capture                Package dbms_repair    How to Repair Corrupt Data Using dbms_repair Package               Package dbms_streams_tablespace_adm            Transport Tablespaces Between Databases Using RMAN and dbms_streams_tablespace_adm Package                Package dbms_tdb         Package dbms_tts           How to Check if a Tablespace is Transportable or Not    Chapter 7:  Oracle Management and Monitoring Package dbms_alert       Package dbms_auto_task_admin             Package dbms_comparison         Package dbms_db_version         Package dbms_ddl          Package dbms_debug   Package dbms_describe               Package dbms_hm          Package dbms_java        Package dbms_job          Package dbms_ldap        Package dbms_metadata             Package dbms_output  Package dbms_pipe       Package dbms_preprocessor     Package dbms_resconfig              Package dbms_resource_manager and dbms_resource manager_privs Package dbms_resumable           Package dbms_scheduler            Package dbms_server_alert       Package dbms_session Package dbms_shared_pool       Package dbms_utility     Package dbms_warning                Package debug_extproc               Package dbms_random                           Chapter 8:  Oracle Data Warehouse           Package dbms_advanced_rewrite           Package dbms_cube      How to Create a Cube Using an Existing Materialized View           Package dbms_cube_advise       Package dbms_dimension           Package dbms_mview   Package dbms_olap        Package dbms_refresh             Chapter 9:  RAC  and Distributed Transactions        Package dbms_service  Package dbms_xa                         Chapter 10:  Oracle Data Guard      Package dbms_dg           Package dbms_logstdby                     Chapter 11:  Oracle Streams             Package dbms_apply_adm          Packages dbms_aq and dbms_aqadm    Package dbms_capture_adm     Package dbms_hs_passthrough                Packages dbms_mgwadm and dbms_mgwmsg Package               Packages dbms_streams and dbms_streams_admin       How to Easily Configure Streams Replication       How to Find and Fix Problems in Streams Configuration Package dbms_streams_auth    How to Grant and Revoke Streams Administrative Privileges       Package dbms_streams_advisor_adm   How to Get Streams Performance Information  Package dbms_streams_messaging        How to Enqueue and Dequeue Messages                    Chapter 12:  Oracle HTML DB and XML DB         Package htmldb_custom_auth  Package htmldb_item    Package htmldb_util       Package dbms_xdb         How to Check Current XDB Tablespace and Change It     Package dbms_xdbt       How to Create Indexes for XML DB          Package dbms_xdbz       Packages             Packages dbms_xmlgen and dbms_xmlquery    Tips for Using dbms_xmlgen       Packages dbms_xmlsave and dbms_xmlstore    Package dbms_xmlschema         Summary             Book Summary   About the Author            Technical Editor