Thursday, January 31, 2008

Customer success story at ODTUG Kaleidoscope 2008


I will be presenting two customer success stories at the ODTUG Kaleidoscope 2008 in New Orleans this year. This is about the work I have done for the German Telecom Shops (T-Punkt Vertriebsgesellschaft mbH). This presentation is not so much about the technical details but more about the business related stuff.

Why ist this application important for the customer? What were the greatest benefits? Why was APEX chosen as a technology. How did this technology perform compared to the usual suspects ;) ?

Thanks to Scott Spendolini, he invited me to give this presentation. Hope to finally meet him there.

And I do look forward to meet up again with all the APEX developers and enthusiasts around the world, like Joel Kallman, Patrick Wolf, Carl Backstrom, Dimitri Gielis, John Scott (this time spelled correctly ;), David Peake, ... and meet some new ones, too.

It will be real fun to come back to New Orleans. I have been there in 1996 for mardi gras, it was a pure blast !!! I wonder how things have changed since Katrina.

Hope to see you there,
~Dietmar.

Tuesday, January 22, 2008

Gathering Apex Installation Requirements

Did you ever wonder how to determine the installation requirements for Apex?

Which steps are really neccessary? Is the JavaVM installed? What about XML DB? What about Oracle Text? How can you find out?

And even more, what if you don't have access to a DBA account on the target instance? What information needs to be gathered by the DBA?

I have created a simple script to gather all relevant information.

Comments are welcome!

In another post I might explore a way to programmatically analyze the outcome and determine the exact steps to be taken.

Here is the script:

/*
* Check installation requirements for Oracle Application Express 3.0.1
*
* http://download-uk.oracle.com/docs/cd/B32472_01/doc/install.300/b32468/overview.htm
*
* Version Date Author Comment
* #####################################################################
* 0.5.0 22.01.2008 D. Aust initial creation
*
*/

SET serveroutput on
set linesize 200
set pagesize 10000
set trimspool on
SPOOL oracle_info.lst

PROMPT ***
PROMPT *** database version must be >= 9.2.0.3
PROMPT ***

DECLARE
l_version VARCHAR2 (100);
l_compatibility VARCHAR2 (100);
BEGIN
DBMS_UTILITY.db_version (VERSION => l_version,
COMPATIBILITY => l_compatibility
);
DBMS_OUTPUT.put_line ( 'Version: '
|| l_version
|| ' Compatibility: '
|| l_compatibility
);
END;
/

PROMPT ***
PROMPT *** v$version
PROMPT ***
column banner format A100;
SELECT banner
FROM v$version;

PROMPT ***
PROMPT *** installed components
PROMPT ***
column comp_id format A15;
column comp_name format A40;
column version format A15;
column status format A10;
column schema format A15;
SELECT comp_id, comp_name, VERSION, status, SCHEMA
FROM dba_registry;

PROMPT ***
PROMPT *** installed options
PROMPT ***
column parameter format A50;
column value format A10;
SELECT parameter, value
FROM v$option;

PROMPT ***
PROMPT *** parameters
PROMPT ***
COLUMN name format A30;
COLUMN value format A40;
COLUMN isdefault format A10;
COLUMN ismodified format A10;
COLUMN isadjusted format A10;
COLUMN description format A80;
SELECT NAME, VALUE, isdefault, ismodified, isadjusted, description
FROM v$parameter
ORDER BY NAME;


PROMPT ***
PROMPT *** tablespaces
PROMPT ***
COLUMN name format A30;
COLUMN value format A40;
select TABLESPACE_NAME,
round(sum(BYTES) / 1024 / 1024, 2) Total_free_space_MB,
round(max(BYTES) / 1024 / 1024, 2) largest_free_extent_MB
from dba_free_space
group by TABLESPACE_NAME
order by tablespace_name;

PROMPT ***
PROMPT *** version of the owa_toolkit, must be >= 10.1.2.0.6
PROMPT ***
select owa_util.get_version OWA_VERSION from dual;

PROMPT ***
PROMPT *** NLS database parameters
PROMPT ***
select * from nls_database_parameters;

PROMPT ***
PROMPT *** NLS session parameters;
PROMPT ***
select * from nls_session_parameters;

PROMPT ***
PROMPT ***
PROMPT ***
COLUMN param format A20;
COLUMN value format A60;
SELECT 'SESSION_USER' param, SYS_CONTEXT ('USERENV', 'SESSION_USER') value FROM DUAL
union all
SELECT 'CURRENT_SCHEMA' param, SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') value FROM DUAL
union all
SELECT 'DB_DOMAIN' param, SYS_CONTEXT ('USERENV', 'DB_DOMAIN') value FROM DUAL
union all
SELECT 'DB_NAME' param, SYS_CONTEXT ('USERENV', 'DB_NAME') value FROM DUAL
union all
SELECT 'DB_UNIQUE_NAME' param, SYS_CONTEXT ('USERENV', 'DB_UNIQUE_NAME') value FROM DUAL
union all
--SELECT 'IP_ADDRESS' param, SYS_CONTEXT ('USERENV', 'IP_ADDRESS') value FROM DUAL
--union all
--SELECT 'HOST' param, SYS_CONTEXT ('USERENV', 'HOST') value FROM DUAL
--union all
SELECT 'SERVICE_NAME' param, SYS_CONTEXT ('USERENV', 'SERVICE_NAME') value FROM DUAL
union all
SELECT 'SERVER_HOST' param, SYS_CONTEXT ('USERENV', 'SERVER_HOST') value FROM DUAL
union all
SELECT 'SERVER_HOST_NAME' param, UTL_INADDR.get_host_name value FROM dual
union all
SELECT 'SERVER_ADDRESS' param, UTL_INADDR.get_host_address value from dual;
--union all
--SELECT 'TERMINAL' param, SYS_CONTEXT ('USERENV', 'TERMINAL') value FROM DUAL;


spool off;

--host start oracle_info.lst

exit


Run the script as SYS, SYSTEM or any other DBA or a normal user having SELECT_CATALOG_ROLE privileges.

It will generate a file called oracle_info.lst.

Here is a sample output:

***
*** database version must be >= 9.2.0.3
***
Version: 10.2.0.1.0 Compatibility: 10.2.0.1.0

PL/SQL-Prozedur erfolgreich abgeschlossen.

***
*** v$version
***

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

***
*** installed components
***

COMP_ID COMP_NAME VERSION STATUS SCHEMA
--------------- ---------------------------------------- --------------- ---------- ---------------
APEX Oracle Application Express 2.2.0.00.32 VALID FLOWS_020200
EM Oracle Enterprise Manager 10.2.0.1.0 VALID SYSMAN
SDO Spatial 10.2.0.1.0 VALID MDSYS
ORDIM Oracle interMedia 10.2.0.1.0 VALID ORDSYS
AMD OLAP Catalog 10.2.0.1.0 VALID OLAPSYS
XDB Oracle XML Database 10.2.0.1.0 VALID XDB
CONTEXT Oracle Text 10.2.0.1.0 VALID CTXSYS
EXF Oracle Expression Filter 10.2.0.1.0 VALID EXFSYS
RUL Oracle Rules Manager 10.2.0.1.0 VALID EXFSYS
OWM Oracle Workspace Manager 10.2.0.1.0 VALID WMSYS
ODM Oracle Data Mining 10.2.0.1.0 VALID DMSYS
CATALOG Oracle Database Catalog Views 10.2.0.1.0 VALID SYS
CATPROC Oracle Database Packages and Types 10.2.0.1.0 VALID SYS
JAVAVM JServer JAVA Virtual Machine 10.2.0.1.0 VALID SYS
XML Oracle XDK 10.2.0.1.0 VALID SYS
CATJAVA Oracle Database Java Packages 10.2.0.1.0 VALID SYS
APS OLAP Analytic Workspace 10.2.0.1.0 VALID SYS
XOQ Oracle OLAP API 10.2.0.1.0 VALID SYS

18 Zeilen ausgewählt.

***
*** installed options
***

PARAMETER VALUE
-------------------------------------------------- ----------
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
Proxy authentication/authorization TRUE
Change Data Capture TRUE
Plan Stability TRUE
Online Index Build TRUE
Coalesce Index TRUE
Managed Standby TRUE
Materialized view rewrite TRUE
Materialized view warehouse refresh TRUE
Database resource manager TRUE
Spatial TRUE
Visual Information Retrieval TRUE
Export transportable tablespaces TRUE
Transparent Application Failover TRUE
Fast-Start Fault Recovery TRUE
Sample Scan TRUE
Duplexed backups TRUE
Java TRUE
OLAP Window Functions TRUE
Block Media Recovery TRUE
Fine-grained Auditing TRUE
Application Role TRUE
Enterprise User Security TRUE
Oracle Data Guard TRUE
Oracle Label Security FALSE
OLAP TRUE
Table compression TRUE
Join index TRUE
Trial Recovery TRUE
Data Mining TRUE
Online Redefinition TRUE
Streams Capture TRUE
File Mapping TRUE
Block Change Tracking TRUE
Flashback Table TRUE
Flashback Database TRUE
Data Mining Scoring Engine FALSE
Transparent Data Encryption TRUE
Backup Encryption TRUE
Unused Block Compression TRUE

54 Zeilen ausgewählt.

***
*** parameters
***

NAME VALUE ISDEFAULT ISMODIFIED ISADJUSTED DESCRIPTION
------------------------------ ---------------------------------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
active_instance_count TRUE FALSE FALSE number of active instances in the cluster database
aq_tm_processes 0 TRUE FALSE FALSE number of AQ Time Managers to start
archive_lag_target 0 TRUE FALSE FALSE Maximum number of seconds of redos the standby could lose
asm_diskgroups TRUE FALSE FALSE disk groups to mount automatically
asm_diskstring TRUE FALSE FALSE disk set locations for discovery
asm_power_limit 1 TRUE FALSE FALSE number of processes for disk rebalancing
audit_file_dest D:\ORACLE\PRODUCT\ADMIN\ODS\ADUMP FALSE FALSE FALSE Directory in which auditing files are to reside
audit_sys_operations FALSE TRUE FALSE FALSE enable sys auditing
audit_trail NONE TRUE FALSE FALSE enable system auditing
background_core_dump partial TRUE FALSE FALSE Core Size for Background Processes
background_dump_dest D:\ORACLE\PRODUCT\ADMIN\ODS\BDUMP FALSE FALSE FALSE Detached process dump directory
backup_tape_io_slaves FALSE TRUE FALSE FALSE BACKUP Tape I/O slaves
bitmap_merge_area_size 1048576 TRUE FALSE FALSE maximum memory allow for BITMAP MERGE
blank_trimming FALSE TRUE FALSE FALSE blank trimming semantics parameter
buffer_pool_keep TRUE FALSE FALSE Number of database blocks/latches in keep buffer pool
buffer_pool_recycle TRUE FALSE FALSE Number of database blocks/latches in recycle buffer pool
circuits TRUE FALSE FALSE max number of circuits
cluster_database FALSE TRUE FALSE FALSE if TRUE startup in cluster database mode
cluster_database_instances 1 TRUE FALSE FALSE number of instances to use for sizing cluster db SGA structures
cluster_interconnects TRUE FALSE FALSE interconnects for RAC use
commit_point_strength 1 TRUE FALSE FALSE Bias this node has toward not preparing in a two-phase commit
commit_write TRUE FALSE FALSE transaction commit log write behaviour
compatible 10.2.0.1.0 FALSE FALSE FALSE Database will be completely compatible with this software version
control_file_record_keep_time 7 TRUE FALSE FALSE control file record keep time in days
control_files D:\ORACLE\ORADATA\ODS\CONTROL01.CTL, D:\ FALSE FALSE FALSE control file names list
ORACLE\ORADATA\ODS\CONTROL02.CTL, D:\ORA
CLE\ORADATA\ODS\CONTROL03.CTL

core_dump_dest D:\ORACLE\PRODUCT\ADMIN\ODS\CDUMP FALSE FALSE FALSE Core dump directory
cpu_count 2 TRUE FALSE FALSE number of CPUs for this instance
create_bitmap_area_size 8388608 TRUE FALSE FALSE size of create bitmap buffer for bitmap index
create_stored_outlines TRUE FALSE FALSE create stored outlines for DML statements
cursor_sharing EXACT TRUE FALSE FALSE cursor sharing mode
cursor_space_for_time FALSE TRUE FALSE FALSE use more memory in order to get faster execution
db_block_buffers 0 TRUE FALSE FALSE Number of database blocks cached in memory
db_block_checking FALSE TRUE FALSE FALSE header checking and data and index block checking
db_block_checksum TRUE TRUE FALSE FALSE store checksum in db blocks and check during reads
db_block_size 8192 FALSE FALSE FALSE Size of database block in bytes
db_cache_advice ON TRUE FALSE FALSE Buffer cache sizing advisory
db_cache_size 0 TRUE FALSE FALSE Size of DEFAULT buffer pool for standard block size buffers
db_create_file_dest TRUE FALSE FALSE default database location
db_create_online_log_dest_1 TRUE FALSE FALSE online log/controlfile destination #1
db_create_online_log_dest_2 TRUE FALSE FALSE online log/controlfile destination #2
db_create_online_log_dest_3 TRUE FALSE FALSE online log/controlfile destination #3
db_create_online_log_dest_4 TRUE FALSE FALSE online log/controlfile destination #4
db_create_online_log_dest_5 TRUE FALSE FALSE online log/controlfile destination #5
db_domain FALSE FALSE FALSE directory part of global database name stored with CREATE DATABASE
db_file_multiblock_read_count 16 FALSE FALSE FALSE db block to be read each IO
db_file_name_convert TRUE FALSE FALSE datafile name convert patterns and strings for standby/clone db
db_files 200 TRUE FALSE FALSE max allowable # db files
db_flashback_retention_target 1440 TRUE FALSE FALSE Maximum Flashback Database log retention time in minutes.
db_keep_cache_size 0 TRUE FALSE FALSE Size of KEEP buffer pool for standard block size buffers
db_name ODS FALSE FALSE FALSE database name specified in CREATE DATABASE
db_recovery_file_dest D:\oracle\product/flash_recovery_area FALSE FALSE FALSE default database recovery file location
db_recovery_file_dest_size 2147483648 FALSE FALSE FALSE database recovery files size limit
db_recycle_cache_size 0 TRUE FALSE FALSE Size of RECYCLE buffer pool for standard block size buffers
db_unique_name ODS TRUE FALSE FALSE Database Unique Name
dbwr_io_slaves 0 TRUE FALSE FALSE DBWR I/O slaves
db_writer_processes 1 TRUE FALSE FALSE number of background database writer processes to start
db_16k_cache_size 0 TRUE FALSE FALSE Size of cache for 16K buffers
db_2k_cache_size 0 TRUE FALSE FALSE Size of cache for 2K buffers
db_32k_cache_size 0 TRUE FALSE FALSE Size of cache for 32K buffers
db_4k_cache_size 0 TRUE FALSE FALSE Size of cache for 4K buffers
db_8k_cache_size 0 TRUE FALSE FALSE Size of cache for 8K buffers
ddl_wait_for_locks FALSE TRUE FALSE FALSE Disable NOWAIT DML lock acquisitions
dg_broker_config_file1 D:\ORACLE\PRODUCT\10.2.0\DATABASE\DR1ODS TRUE FALSE FALSE data guard broker configuration file #1
.DAT

dg_broker_config_file2 D:\ORACLE\PRODUCT\10.2.0\DATABASE\DR2ODS TRUE FALSE FALSE data guard broker configuration file #2
.DAT

dg_broker_start FALSE TRUE FALSE FALSE start Data Guard broker framework (DMON process)
disk_asynch_io TRUE TRUE FALSE FALSE Use asynch I/O for random access devices
dispatchers (PROTOCOL=TCP) (SERVICE=ODSXDB) FALSE FALSE FALSE specifications of dispatchers
distributed_lock_timeout 60 TRUE FALSE FALSE number of seconds a distributed transaction waits for a lock
dml_locks 748 TRUE FALSE FALSE dml locks - one for each table modified in a transaction
drs_start FALSE TRUE FALSE FALSE start DG Broker monitor (DMON process)
event TRUE FALSE FALSE debug event control - default null string
fal_client TRUE FALSE FALSE FAL client
fal_server TRUE FALSE FALSE FAL server list
fast_start_io_target 0 TRUE FALSE FALSE Upper bound on recovery reads
fast_start_mttr_target 0 TRUE FALSE FALSE MTTR target of forward crash recovery in seconds
fast_start_parallel_rollback LOW TRUE FALSE FALSE max number of parallel recovery slaves that may be used
fileio_network_adapters TRUE FALSE FALSE Network Adapters for File I/O
file_mapping FALSE TRUE FALSE FALSE enable file mapping
filesystemio_options TRUE FALSE FALSE IO operations on filesystem files
fixed_date NONE FALSE FALSE FALSE fixed SYSDATE value
gc_files_to_locks TRUE FALSE FALSE mapping between file numbers and global cache locks
gcs_server_processes 0 TRUE FALSE FALSE number of background gcs server processes to start
global_context_pool_size TRUE FALSE FALSE Global Application Context Pool Size in Bytes
global_names FALSE TRUE FALSE FALSE enforce that database links have same name as remote database
hash_area_size 131072 TRUE FALSE FALSE size of in-memory hash work area
hi_shared_memory_address 0 TRUE FALSE FALSE SGA starting address (high order 32-bits on 64-bit platforms)
hs_autoregister TRUE TRUE FALSE FALSE enable automatic server DD updates in HS agent self-registration
ifile TRUE FALSE FALSE include file in init.ora
instance_groups TRUE FALSE FALSE list of instance group names
instance_name ods TRUE FALSE FALSE instance name supported by the instance
instance_number 0 TRUE FALSE FALSE instance number
instance_type RDBMS TRUE FALSE FALSE type of instance to be executed
java_max_sessionspace_size 0 TRUE FALSE FALSE max allowed size in bytes of a Java sessionspace
java_pool_size 0 TRUE FALSE FALSE size in bytes of java pool
java_soft_sessionspace_limit 0 TRUE FALSE FALSE warning limit on size in bytes of a Java sessionspace
job_queue_processes 10 FALSE FALSE FALSE number of job queue slave processes
large_pool_size 0 TRUE FALSE FALSE size in bytes of large pool
ldap_directory_access NONE TRUE FALSE FALSE RDBMS's LDAP access option
license_max_sessions 0 TRUE FALSE FALSE maximum number of non-system user sessions allowed
license_max_users 0 TRUE FALSE FALSE maximum number of named users that can be created in the database
license_sessions_warning 0 TRUE FALSE FALSE warning level for number of non-system user sessions
local_listener LISTENER_ODS FALSE FALSE FALSE local listener
lock_name_space TRUE FALSE FALSE lock name space used for generating lock names for standby/clone database
lock_sga FALSE TRUE FALSE FALSE Lock entire SGA in physical memory
log_archive_config TRUE FALSE FALSE log archive config parameter
log_archive_dest TRUE FALSE FALSE archival destination text string
log_archive_dest_state_1 enable TRUE FALSE FALSE archival destination #1 state text string
log_archive_dest_state_10 enable TRUE FALSE FALSE archival destination #10 state text string
log_archive_dest_state_2 enable TRUE FALSE FALSE archival destination #2 state text string
log_archive_dest_state_3 enable TRUE FALSE FALSE archival destination #3 state text string
log_archive_dest_state_4 enable TRUE FALSE FALSE archival destination #4 state text string
log_archive_dest_state_5 enable TRUE FALSE FALSE archival destination #5 state text string
log_archive_dest_state_6 enable TRUE FALSE FALSE archival destination #6 state text string
log_archive_dest_state_7 enable TRUE FALSE FALSE archival destination #7 state text string
log_archive_dest_state_8 enable TRUE FALSE FALSE archival destination #8 state text string
log_archive_dest_state_9 enable TRUE FALSE FALSE archival destination #9 state text string
log_archive_dest_1 TRUE FALSE FALSE archival destination #1 text string
log_archive_dest_10 TRUE FALSE FALSE archival destination #10 text string
log_archive_dest_2 TRUE FALSE FALSE archival destination #2 text string
log_archive_dest_3 TRUE FALSE FALSE archival destination #3 text string
log_archive_dest_4 TRUE FALSE FALSE archival destination #4 text string
log_archive_dest_5 TRUE FALSE FALSE archival destination #5 text string
log_archive_dest_6 TRUE FALSE FALSE archival destination #6 text string
log_archive_dest_7 TRUE FALSE FALSE archival destination #7 text string
log_archive_dest_8 TRUE FALSE FALSE archival destination #8 text string
log_archive_dest_9 TRUE FALSE FALSE archival destination #9 text string
log_archive_duplex_dest TRUE FALSE FALSE duplex archival destination text string
log_archive_format ARC%S_%R.%T TRUE FALSE FALSE archival destination format
log_archive_local_first TRUE TRUE FALSE FALSE Establish EXPEDITE attribute default value
log_archive_max_processes 2 TRUE FALSE FALSE maximum number of active ARCH processes
log_archive_min_succeed_dest 1 TRUE FALSE FALSE minimum number of archive destinations that must succeed
log_archive_start FALSE TRUE FALSE FALSE start archival process on SGA initialization
log_archive_trace 0 TRUE FALSE FALSE Establish archivelog operation tracing level
log_buffer 7024640 TRUE FALSE FALSE redo circular buffer size
log_checkpoint_interval 0 TRUE FALSE FALSE # redo blocks checkpoint threshold
log_checkpoints_to_alert FALSE TRUE FALSE FALSE log checkpoint begin/end to alert file
log_checkpoint_timeout 1800 TRUE FALSE FALSE Maximum time interval between checkpoints in seconds
log_file_name_convert TRUE FALSE FALSE logfile name convert patterns and strings for standby/clone db
logmnr_max_persistent_sessions 1 TRUE FALSE FALSE maximum number of threads to mine
max_commit_propagation_delay 0 TRUE FALSE FALSE Max age of new snapshot in .01 seconds
max_dispatchers TRUE FALSE FALSE max number of dispatchers
max_dump_file_size UNLIMITED TRUE FALSE FALSE Maximum size (blocks) of dump file
max_enabled_roles 150 TRUE FALSE FALSE max number of roles a user can have enabled
max_shared_servers TRUE FALSE FALSE max number of shared servers
nls_calendar TRUE FALSE FALSE NLS calendar system name
nls_comp TRUE FALSE FALSE NLS comparison
nls_currency TRUE FALSE FALSE NLS local currency symbol
nls_date_format TRUE FALSE FALSE NLS Oracle date format
nls_date_language TRUE FALSE FALSE NLS date language name
nls_dual_currency TRUE FALSE FALSE Dual currency symbol
nls_iso_currency TRUE FALSE FALSE NLS ISO currency territory name
nls_language AMERICAN TRUE FALSE FALSE NLS language name
nls_length_semantics BYTE TRUE FALSE FALSE create columns using byte or char semantics by default
nls_nchar_conv_excp FALSE TRUE FALSE FALSE NLS raise an exception instead of allowing implicit conversion
nls_numeric_characters TRUE FALSE FALSE NLS numeric characters
nls_sort TRUE FALSE FALSE NLS linguistic definition name
nls_territory AMERICA TRUE FALSE FALSE NLS territory name
nls_time_format TRUE FALSE FALSE time format
nls_timestamp_format TRUE FALSE FALSE time stamp format
nls_timestamp_tz_format TRUE FALSE FALSE timestampe with timezone format
nls_time_tz_format TRUE FALSE FALSE time with timezone format
object_cache_max_size_percent 10 TRUE FALSE FALSE percentage of maximum size over optimal of the user session's object cache
object_cache_optimal_size 102400 TRUE FALSE FALSE optimal size of the user session's object cache in bytes
olap_page_pool_size 0 TRUE FALSE FALSE size of the olap page pool in bytes
open_cursors 300 FALSE FALSE FALSE max # cursors per session
open_links 4 TRUE FALSE FALSE max # open links per session
open_links_per_instance 4 TRUE FALSE FALSE max # open links per instance
optimizer_dynamic_sampling 2 TRUE FALSE FALSE optimizer dynamic sampling
optimizer_features_enable 10.2.0.1 TRUE FALSE FALSE optimizer plan compatibility parameter
optimizer_index_caching 0 TRUE FALSE FALSE optimizer percent index caching
optimizer_index_cost_adj 100 TRUE FALSE FALSE optimizer index cost adjustment
optimizer_mode ALL_ROWS TRUE FALSE FALSE optimizer mode
optimizer_secure_view_merging TRUE TRUE FALSE FALSE optimizer secure view merging and predicate pushdown/movearound
os_authent_prefix OPS$ TRUE FALSE FALSE prefix for auto-logon accounts
os_roles FALSE TRUE FALSE FALSE retrieve roles from the operating system
O7_DICTIONARY_ACCESSIBILITY FALSE TRUE FALSE FALSE Version 7 Dictionary Accessibility Support
parallel_adaptive_multi_user TRUE TRUE FALSE FALSE enable adaptive setting of degree for multiple user streams
parallel_automatic_tuning FALSE TRUE FALSE FALSE enable intelligent defaults for parallel execution parameters
parallel_execution_message_siz 2148 TRUE FALSE FALSE message buffer size for parallel execution
e

parallel_instance_group TRUE FALSE FALSE instance group to use for all parallel operations
parallel_max_servers 40 TRUE FALSE FALSE maximum parallel query servers per instance
parallel_min_percent 0 TRUE FALSE FALSE minimum percent of threads required for parallel query
parallel_min_servers 0 TRUE FALSE FALSE minimum parallel query servers per instance
parallel_server FALSE TRUE FALSE FALSE if TRUE startup in parallel server mode
parallel_server_instances 1 TRUE FALSE FALSE number of instances to use for sizing OPS SGA structures
parallel_threads_per_cpu 2 TRUE FALSE FALSE number of parallel execution threads per CPU
pga_aggregate_target 243269632 FALSE FALSE FALSE Target size for the aggregate PGA memory consumed by the instance
plsql_ccflags TRUE FALSE FALSE PL/SQL ccflags
plsql_code_type INTERPRETED TRUE FALSE FALSE PL/SQL code-type
plsql_compiler_flags INTERPRETED, NON_DEBUG TRUE FALSE FALSE PL/SQL compiler flags
plsql_debug FALSE TRUE FALSE FALSE PL/SQL debug
plsql_native_library_dir TRUE FALSE FALSE plsql native library dir
plsql_native_library_subdir_co 0 TRUE FALSE FALSE plsql native library number of subdirectories
unt

plsql_optimize_level 2 TRUE FALSE FALSE PL/SQL optimize level
plsql_v2_compatibility FALSE TRUE FALSE FALSE PL/SQL version 2.x compatibility flag
plsql_warnings DISABLE:ALL TRUE FALSE FALSE PL/SQL compiler warnings settings
pre_page_sga FALSE TRUE FALSE FALSE pre-page sga for process
processes 150 FALSE FALSE FALSE user processes
query_rewrite_enabled TRUE TRUE FALSE FALSE allow rewrite of queries using materialized views if enabled
query_rewrite_integrity enforced TRUE FALSE FALSE perform rewrite using materialized views with desired integrity
rdbms_server_dn TRUE FALSE FALSE RDBMS's Distinguished Name
read_only_open_delayed FALSE TRUE FALSE FALSE if TRUE delay opening of read only files until first access
recovery_parallelism 0 TRUE FALSE FALSE number of server processes to use for parallel recovery
recyclebin on TRUE FALSE FALSE recyclebin processing
remote_archive_enable true TRUE FALSE FALSE remote archival enable setting
remote_dependencies_mode TIMESTAMP TRUE FALSE FALSE remote-procedure-call dependencies mode parameter
remote_listener TRUE FALSE FALSE remote listener
remote_login_passwordfile EXCLUSIVE FALSE FALSE FALSE password file usage parameter
remote_os_authent FALSE TRUE FALSE FALSE allow non-secure remote clients to use auto-logon accounts
remote_os_roles FALSE TRUE FALSE FALSE allow non-secure remote clients to use os roles
replication_dependency_trackin TRUE TRUE FALSE FALSE tracking dependency for Replication parallel propagation
g

resource_limit FALSE TRUE FALSE FALSE master switch for resource limit
resource_manager_plan TRUE FALSE FALSE resource mgr top plan
resumable_timeout 0 TRUE FALSE FALSE set resumable_timeout
rollback_segments TRUE FALSE FALSE undo segment list
serial_reuse disable TRUE FALSE FALSE reuse the frame segments
service_names ODS TRUE FALSE FALSE service names supported by the instance
session_cached_cursors 20 TRUE FALSE FALSE Number of cursors to cache in a session.
session_max_open_files 10 TRUE FALSE FALSE maximum number of open files allowed per session
sessions 170 TRUE FALSE FALSE user and system sessions
sga_max_size 603979776 FALSE FALSE FALSE max total SGA size
sga_target 603979776 FALSE FALSE FALSE Target size of SGA
shadow_core_dump partial TRUE FALSE FALSE Core Size for Shadow Processes
shared_memory_address 0 TRUE FALSE FALSE SGA starting address (low order 32-bits on 64-bit platforms)
shared_pool_reserved_size 4823449 TRUE FALSE FALSE size in bytes of reserved area of shared pool
shared_pool_size 0 TRUE FALSE FALSE size in bytes of shared pool
shared_servers 1 TRUE FALSE FALSE number of shared servers to start up
shared_server_sessions TRUE FALSE FALSE max number of shared server sessions
skip_unusable_indexes TRUE TRUE FALSE FALSE skip unusable indexes if set to TRUE
smtp_out_server TRUE FALSE FALSE utl_smtp server and port configuration parameter
sort_area_retained_size 0 TRUE FALSE FALSE size of in-memory sort work area retained between fetch calls
sort_area_size 65536 TRUE FALSE FALSE size of in-memory sort work area
spfile D:\ORACLE\PRODUCT\10.2.0\DBS\SPFILEODS.O FALSE FALSE FALSE server parameter file
RA

sql_trace FALSE TRUE FALSE FALSE enable SQL trace
sqltune_category DEFAULT TRUE FALSE FALSE Category qualifier for applying hintsets
sql_version NATIVE TRUE FALSE FALSE sql language version parameter for compatibility issues
sql92_security FALSE TRUE FALSE FALSE require select privilege for searched update/delete
standby_archive_dest %ORACLE_HOME%\RDBMS TRUE FALSE FALSE standby database archivelog destination text string
standby_file_management MANUAL TRUE FALSE FALSE if auto then files are created/dropped automatically on standby
star_transformation_enabled FALSE TRUE FALSE FALSE enable the use of star transformation
statistics_level TYPICAL TRUE FALSE FALSE statistics level
streams_pool_size 0 TRUE FALSE FALSE size in bytes of the streams pool
tape_asynch_io TRUE TRUE FALSE FALSE Use asynch I/O requests for tape devices
thread 0 TRUE FALSE FALSE Redo thread to mount
timed_os_statistics 0 TRUE FALSE FALSE internal os statistic gathering interval in seconds
timed_statistics TRUE TRUE FALSE FALSE maintain internal timing statistics
trace_enabled TRUE TRUE FALSE FALSE enable KST tracing
tracefile_identifier TRUE FALSE FALSE trace file custom identifier
transactions 187 TRUE FALSE FALSE max. number of concurrent active transactions
transactions_per_rollback_segm 5 TRUE FALSE FALSE number of active transactions per rollback segment
ent

undo_management AUTO FALSE FALSE FALSE instance runs in SMU mode if TRUE, else in RBU mode
undo_retention 900 TRUE FALSE FALSE undo retention in seconds
undo_tablespace UNDOTBS1 FALSE FALSE FALSE use/switch undo tablespace
use_indirect_data_buffers FALSE TRUE FALSE FALSE Enable indirect data buffers (very large SGA on 32-bit platforms)
user_dump_dest D:\ORACLE\PRODUCT\ADMIN\ODS\UDUMP FALSE FALSE FALSE User process dump directory
utl_file_dir TRUE FALSE FALSE utl_file accessible directories list
workarea_size_policy AUTO TRUE FALSE FALSE policy used to size SQL working areas (MANUAL/AUTO)

257 Zeilen ausgewählt.

***
*** tablespaces
***

TABLESPACE_NAME TOTAL_FREE_SPACE_MB LARGEST_FREE_EXTENT_MB
------------------------------ ------------------- ----------------------
ABIT 515,81 510,94
EXAMPLE 22,63 19,81
SHDB 490,44 487,94
SYSAUX 19 2,94
SYSTEM 2,88 1,94
UNDOTBS1 2589,5 2571,94
USERS 656,19 56,5

7 Zeilen ausgewählt.

***
*** version of the owa_toolkit, must be >= 10.1.2.0.6
***

OWA_VERSION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10.1.2.0.0

***
*** NLS database parameters
***

PARAMETER VALUE
-------------------------------------------------- ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0

20 Zeilen ausgewählt.

***
*** NLS session parameters
***

PARAMETER VALUE
-------------------------------------------------- ----------------------------------------
NLS_LANGUAGE GERMAN
NLS_TERRITORY GERMANY
NLS_CURRENCY €
NLS_ISO_CURRENCY GERMANY
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE GERMAN
NLS_SORT GERMAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 Zeilen ausgewählt.

***
***
***

PARAM VALUE
-------------------- ------------------------------------------------------------
SESSION_USER SYSTEM
CURRENT_SCHEMA SYSTEM
DB_DOMAIN
DB_NAME ODS
DB_UNIQUE_NAME ODS
SERVICE_NAME SYS$USERS
SERVER_HOST daust3
SERVER_HOST_NAME DAUST3
SERVER_ADDRESS 192.168.0.2

9 Zeilen ausgewählt.


Hope that helps,
~Dietmar.

Mod_plsql for Apache2

Thanks to Patrick for finding this really interesting post by Joel Kallman.

Finally, mod_plsql has been ported to Apache2. Up until now it was only available and supported for the Oracle Http Server based on Apache (1.3.x).

Using this configuration on a *nix (Linux / Unix) based machine would start a new database session even if the request would only serve a static image. Thus the connection pool of mod_plsql wasn't used at all. This is the first time where I have seen an advantage of the Windows architecture over Linux, bummer.

Thus on Linux you had to be careful planning for your environment if you expected a heavy load, here are some of the typical things to do optimize the performance.

Using Apache 2.0 and the new mod_plsql this should be improved now.

Greetinx,
~Dietmar.

Sunday, January 20, 2008

Apex Meetup last Tuesday

Last week, we had the first Apex meetup in the Cologne area in Germany. We started off slow with a small group of five people :) . From left to right you see Uwe Mahlmann, Denes Kubicek, Jens Gauger and Erich Heinemann.


We hope to establish this exchange of ideas on a monthly basis. Let's see, how it works out. On the next picture you can see me (Dietmar Aust) on the right, too.

We were talking about the different projects and the different ways, in which we use Apex in our projects.

Jens also mentioned that there definitely is a need to show off more customer references on how Apex is used in the *real world*. If somebody researches different tools for their company, customer references and actual samples / screenshots / screencasts build credibility. David Peake recently blogged about large applications built with Apex. This will be definitely a valuable addition to the already existing resources on OTN, especially the community and partners page.

Another bigger topic was the generation of Excel and PDF reports, this area is still not perfectly solved using Apex out of the box. BI-publisher is very expensive and the other solutions are just too rudimentary.

It was a really nice place in Siegburg. I guess I will going there more frequently. Nice atmosphere and good food and drinks.












Hope to see you on the next Apex meetup. I will post the anncouncement here and on my website.

Regards,
~Dietmar.

Thursday, January 10, 2008

Apex Stammtisch am 15.1.2008

Am kommenden Dienstag, dem 15.01.2008, findet in Siegburg (in der Nähe von Köln) der erste Apex Stammtisch statt!

Denes Kubicek und Dietmar Aust sind auf jeden Fall dabei. Wir freuen uns auf ein lockeres Kennenlernen bei einem Bier und evtl. leckerem Flammkuchen.

Vielleicht bis nächste Woche!
~Dietmar.