Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

  

 

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)
buy it now

 

     
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

 

 

Key Features

• 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            

  Index:

A

Access Control Entry (ACE)         
Access Control Lists (ACLs)          
action   
add_logfile procedure  
add_policy         
admin_tables procedure             
administrative privilege
Advanced Queuing (AQ)              
alert.log file       
allow_all             
alter_param      
ApEx        
Application Context       
apply process       
assm_segment_synchwm          
assm_tablespace_verify              
attach procedure            
attach_simple_tablespace          
attachment_text            
audit_trail          
Automated Maintenance Tasks
Automatic Workload Repository (AWR)
Autotask             
AWR     
AWR Snapshots               
BFILE    
binary files         
bind_variable procedure             
bind_variable()
BLOB    
book_access_policy       
build procedure               
build_part_index            

C
cancel_tuning_task        
capture process               
change data capture feature     
check_db           
check_external
check_object procedure             
checksum results            
client_id_trace_enable
CLOB    
clone_simple_tablespace           
close_cursor()  
Cluster Ready Services (CRS)     
column_value()               
comma_to_table            
compare function           
Conditional Compilation               
configure            
configure_pool
copy     
copy_file            
cost-based optimizer    
CQN (Change Query Notification)    
create index      
Create Table As (CTAS) 
create_change_table procedure             
create_comparison procedure 
create_extended_stats               
create_job procedure  
create_queue_table procedure               
create_table_cost          
create_wrapped procedure       
createtemporary            
Cursor Cache    

D

Data block integrity check           
Data Guard Broker         
Database Change Notification   
Database Configuration Assistant (DBCA)            
Database Resident Connection Pooling 
database_trace_disable              
database_trace_enable               
date_popup function    
DB structure integrity check       
dba_capture_parameters view
dba_extents     
dba_hist             
dba_jobs            
dba_registered_mviews view   
dba_resumable               
dba_segments 
dba_services view          
dba_streams_administrator view            
dbms_advanced_rewrite            
dbms_advisor  
dbms_alert        
dbms_application_info
dbms_apply_adm            
dbms_aq            
dbms_aqadm   
dbms_auto_task_admin             
dbms_aw_stats               
dbms_capture_adm      
dbms_cdc_publish         
dbms_comparison         
dbms_crypto    
dbms_cube       
dbms_cube_advise       
dbms_cube_advise_trace.sql   
dbms_data_mining        
dbms_data_mining_transform 
dbms_datapump            
dbms_db_version          
dbms_ddl           

 

 

dbms_describe
dbms_dg            
dbms_dimension            
dbms_distributed_trust_admin               
dbms_errlog     
dbms_fga           
dbms_file_transfer        
dbms_flashback              
dbms_hm          
dbms_hs_passthrough
dbms_iot            
dbms_java         
dbms_job          
dbms_ldap        
dbms_libcache 
dbms_lob           
dbms_logmnr_cdc_subscribe   
dbms_logmnr_d             
dbms_logstdby
dbms_metadata             
dbms_mgwadm              
dbms_mgwmsg               
dbms_monitor 
dbms_monitor package               
dbms_mview   
dbms_mview.refresh procedure             
dbms_obfuscation_toolkit         
dbms_odci         
dbms_olap package       
dbms_outlln     
dbms_output   
dbms_pclxutil   
dbms_pipe        
dbms_preprocessor      
dbms_profiler  
dbms_propagation_adm             
dbms_random 
dbms_redefinition         
dbms_refresh  
dbms_repair     
dbms_resconfig              
dbms_resource_manager          
dbms_resource_manager_privs              
dbms_result_cache       
dbms_rls            
dbms_rowid     
dbms_scheduler             
dbms_scheduler.create_file_watcher  
dbms_server_alert        
dbms_service   
dbms_session  
dbms_session package
dbms_shared_pool       
dbms_space       
dbms_space.create_index_cost              
dbms_space_admin      
dbms_spm        
dbms_sql           
dbms_sqltune    
dbms_stat_funcs            
dbms_stats       
dbms_stats.create_extended_stats      
dbms_storage_map      
dbms_streams 
dbms_streams_adm     
dbms_streams_admin 
dbms_streams_advisor_adm    
dbms_streams_auth     
dbms_streams_messaging         
dbms_streams_tablespace_adm             dbms_tdb          
dbms_trace       
dbms_transaction          
dbms_tts            
dbms_utility      
dbms_warning 
dbms_wm         
dbms_workload_repository      
dbms_xa            
dbms_xdb           
dbms_xdbt        
dbms_xdbz       
dbms_xmldom
dbms_xmlgen    
dbms_xmlgen.getxml function 
dbms_xmlparser               
dbms_xmlquery              
dbms_xmlsave
dbms_xmlschema          
dbms_xmlstore               
dbms_xplan      
DBUA (Database Upgrade Assistant) utility         
dd command    
DDL command       
DDL operation  
debug parameter           
debug_extproc
declare_rewrite_equivalence procedure             
decrypt               
delete_object  
deny_all              
deny_server     
dequeue procedure      
dequeue process            
describe_dimension procedure               
describe_procedure      
destination database    
detach_simple_tablespace        
Dictionary integrity check            
dictionary managed tablespaces              
disable_policy  
Disaster Recovery           
disconnect_session procedure 
DML      
DML command      
DML operation    
DML statements             
Document Object Model (DOM)         
Document Type Definition (DTD)        
drop_change_table procedure 
drop_policy       
drop_tuning_task           
DWH (Data Warehouse) database           

 

E

enable procedure           
enable_at_time              
enable_policy   
encapsulation   
encrypt               
enqueue procedure      
ETL process (Extraction Transformation and Load)         
execute command         
execute privilege            
execute_catalog_role   
explain_mview procedure          
export (exp) utility         
expression statistics      
external LOBS   
external procedure 
      
F

failover process               
fast-start failover            
fetch_rows()    
FGA configurations        
file watcher       
file_mapping parameter              
Fine Grained Access Control      
Fine-Grained Auditing (FGA)     
fire once trigger               
flashback functionalities              
flashback query               
flashback_transaction_query    
flush command
flush operation
free_blocks       
full table scan   
full-table scan   
func_card_num_encrypt            
func_card_num_encrypt_des  
G
Geo-Cluster      
get_file               
get_threshold procedure            
getlength           
global variables
grep command
GUI tools            
H
hash     
hashmd               
High Water Mark             
historical deleted rows 
HTML DB             
htmldb_custom_auth   
htmldb_item    
htmldb_util       
I
initialization parameter
insert command              
instr      
internal LOBs    
internal Oracle alerts     
interrupt_tuning_task  
IOT (Index Organized Tables)    
isopen 

J

Java      
jobs package     

L

library cache      
lightweight jobs               
LOBs     
locally managed tablespaces      
lock_object       
Log Miner             
login procedure               
lz_compress      
M
materialized view                
Materialized View Advisor          
Messaging Gateway      
multi-column statistics  
mv_cube_advice            
mv_cube_advice function
          
N

NLOB    
num parameter   
           
O

object_dependent_segments  
object_growth_trend   
OLAP databases              
OLAP Option     
olap_table function       
optimizer statistics         
Oracle Advanced Queue (AQ) 
Oracle Calibration           
Oracle Data Guard            
Oracle Data Mining         
Oracle Database Workspace Manager   
Oracle Streams   
Oracle Streams Replications       
Oracle XA           
Oracle XML SQL Utility (XSU)      
ORADB database             
ORDERED hint   
orphan tables   

P
p_mail_host      
parallelism         
performance statistics  
persistent area
pkg_cust_policy              
PL/SQL 
plsql_warnings 
predicate            
proc_alert          
proc_test           
profiling utility  
profload.sql       
proftab.sql         
propagation process      
pull_simple_tablespace               
purge procedure             
purge_lost_db_entry  procedure            
put_file               

 

 

Q

query equivalence         

R

randombytes    
RAW     
Real Application Clusters (RAC) 
Redo integrity check      
redo log files     
repair tables      
reset_tuning_task          
restore_defaults             
Result Cache     
result_cache_mode      
resumable space allocation        
resumable_timeout      
RMAN  
rowid      
rowid_block_number   
rowid_object    
runtime area     

S

savepoint           
segment_drop_currupt               
select_catalog_role       
select_object   
select_sql_trace              
serv_mod_act_trace_enable    
Server Control (SRVCTL)              
server-side alerts            
set_action          
set_client_info
set_cns_exception_log
set_context      
set_context procedure
set_identifier procedure             
set_module      
set_sqlwkld_parameter              
set_task_parameter     
set_threshold procedure            
shrink command             
signal procedure             
skip procedure 
skip_none          
skip_offline       
skip_readonly  
smtp_out_server           
source code      
space_usage     
SQL optimizer   
SQL plan baselines          
SQL Plan Management 
SQL profile         
SQL profile attributes    
SQL statement 
SQL Tuning Advisor          
SQL Tuning Set 
SQL tuning task
sql_id   
start_pool          
start_profiler    
statistics management tools      
stop_pool          
summary procedure      
synchronize function     

T

table_name      
table_type         
tablespace         
tablespace_fix_segment_extblks           
tablespace_migrate_to_local    
tablespace_verify           
tkprof utility      
topnsql
trace file             
Transaction integrity check         
transaction_backout     
transaction_backout procedure               
trcsess utility     
trim       
Triple DES algorithm       
trusted all    
      
U

Undo segment integrity check  
unusable option              
unused_space 
update command           
update_object 
User Global Area (UGA)               
user_dump_dest            
user_stored_outlines   
utl_compress   
utl_file 
utl_file_dir         
utl_mail               
utl_raw               
utl_ref 
utl_smtp  
          
V

v$db_transportable_platform view        
v$dml_locks      
v$lock  
v$locked_object             
v$logmnr_contents view             
v$open_cursor view      
v$session           
v$sql_plan view               
v$vpd_policy    
validate_dimension procedure 
view_wo_overwrite      
Virtual Private Database (VPD) 
VPD security     
vrfy_changes    

W

workspaces       
wrap utility        
wrapping code 
writeappend
     
X

XML DB                   
XML developers              
XML schemas   
XMLType data  
XSD (XML Schema Definition)    

   

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks