ORACLENERD

UNDO Brain Damage

2009-11-29T20:41:00.004-05:00

I "met" Mrs. Pedersen on the oracle-l mailing list. I can't remember what the topic was, but she supplied this detailed message full of stats and opinion. I immediately went out and looked for her blog, but couldn't find it. She didn't have one.

So I dropped her a line and offered her some space here.

I won't take (all) the credit, but she has since started her own blog, DBA Kevlar.

Automatic Undo can be one of those areas that make you go "hmmmm..." I am fully aware that the documentation that came with Oracle 9i new features for DBA's when Automatic Undo was introduced clearly claimed, "You won't receive another ORA-01555 again!" yet Oracle hasn't quite lived up to that promise in either 9i or 10g and I haven't had the chance to prove otherwise in 11g.

I had the opportunity to revisit my old friend, "ORA-01555" when one of our environments began to page a fellow DBA with the issue the other day. I became intrigued, as I often am by any database oddity and when I queried the amount of undo the process was consuming, it didn't add up with the amount of undo that was being consumed. In our environment, it is not uncommon for 800GB or more of undo to be utilized, but it became quickly apparent something was amiss when looking at the query below vs. the amount of undo allocation of 1.2TB that had been recently allocated by the DBA on call:

select distinct
  vs.sid, vs.osuser,
  vs.process,
  vs.sql_id,
  round((vu.undoblks*32768) / (1024*1024*1024)) GB,
  vu.tuned_undoretention
from v$undostat vu, v$session vs
where vu.maxqueryid in vs.sql_id
group by
  vs.sid,
  vs.osuser,
  vs.process,
  vs.sql_id,
  vu.undoblks,
  vu.tuned_undoretention
order by vs.sid

TOTAL GB used from the query above: 45GB

The first aspect I chose to take into consideration is the "tuned undo retention", as many folks forget that the Oracle parameter UNDO_RETENTION can be over-ridden by the SMON if a process requires blocks from the undo to stay unexpired over the set retention period. I have found in my experience, that if there is a rollback issue with an existing process, inspecting the TUNED_UNDO_RETENTION is an important area to start as a DBA.

After verifying that there were over 7000 different maxquerid's in v$undostat, but that only 20% of them were returning when we were looking at active sessions, I queried to find out what queries have undo blocks set with expirations greater than the set time, (currently 43200 seconds) that were also not in active sessions.

select distinct 
  vu.maxqueryid, 
  vu.maxquerylen,
  vu.tuned_undoretention 
from 
  v$undostat vu, 
  v$session vs
where vu.tuned_undoretention > 43200
  and vu.maxqueryid not in  vs.sql_id
group by 
  vu.maxqueryid,
  vu.maxquerylen, 
  vu.tuned_undoretention;

One SQL_ID came up as the offender with the majority of undo blocks retained over the retention period and not in active sessions: 1zuumhmqc93zj

The maxqueryid we are looking for isn't active, but if we searched historical SQL, then we found our statement:

select sql_text
from DBA_HIST_SQLTEXT
where sql_id = '1zuumhmqc93aj';

select col1 
from prod.Tbl1 
where cnt = 1

Why would a select statement create undo blocks and how could it be using undo if it isn't a current running process and yet showing active undo blocks???

If the query is not active - what could it be?

Answer: PREVIOUS

select vs.sid
from v$undostat vu, v$session vs
where  vu.max_queryid=’1zuumhmqc93aj’
  and vu.maxqueryid = vs.prev_sql_id;

SID
---
817

We now have the SID currently running that is the cause, why worry about the SQL anymore? It's not the current SQL, but a previous sql_id in an active undo process in v$undostat, so at this point, we need to focus on the SID.

So what transpired here?

Upon inspecting SID 817, it was found to be a background session for Oracle two weeks back where someone had issued a kill session command on a poorly coded process! Oracle decided that the select, which was a full scan on a 59GB table, used a cursor into a for loop, was going to take just over two weeks to rollback the killed session! The process to rollback was so sublime, that it hadn't come up as a red flag on any monitoring script or tool, including OEM!

Since no actual data block changes had been performed, it was perfectly safe to stop the rollback and execute a session kill. To free the undo vs. allowing the kill command to continue, we selected the OS Session from V$SESSION for SID 817, proceeded to kill the process at the OS level and issued a reset on the undo tablespace to clear the remaining undo blocks that were active and/or unexpired:

begin
  reset_undo_stat();
end;

By resetting the undo, we were able to free up the undo that had been tuned to be retained and were able to disregard the rollback on a select statement that had been killed.

Once we reset the undo, we promptly informed the developer that he/she was never to attempt to use a 59GB non-partitioned table without indexes for a cursor ever, ever again!! :)

Moral of this story
If you have significant undo retained by non-active, non-transactional processes, you should inspect them carefully to verify that they are valid utilization of your undo tablespace. If not, you could receive ORA-01555, snapshot too old errors on transactions that would normally complete successfully, causing incomplete transactions, more rollback occurrence and users asking you why their processes didn't finish.

BIO
Kellyn Pedersen is a Performance Tuning and Operations Database Administration Specialist with 10 years of DBA experience in Oracle, SQL Server and MySQL. She is a Database Administrator and Developer at I-Behavior in Louisville, CO, is busy writing articles, sharing tips on her own blog, DBA Kevlar, and will be presenting at RMOUG 2010 in Denver, CO.

She lives in Westminster, CO with her husband and children - who are wonderfully willing to put up with her strange fascination with Oracle's Cost Based Optimizer.

[ kellyn dot pedersen at gmail | Twitter | LinkedIn ]

Teaching APEX

2009-11-29T20:14:00.005-05:00

Tomorrow I'll be leaving for Dallas, TX for a 4 day teaching assignment on Application Express.

I'm a bit nervous for multiple reasons.
1. I've never had a problem (well, not in my 30's anyway) getting up and speaking in front of people. My style is light on slides and heavy on winging it. I can do that because I don't often give talks on things I don't know intimately. Don't get me wrong, I have a basic outline to follow, but I have never liked lectures and tried to avoid them at all costs.
2. Leaving the family. The last time I was away from my family for more than a day was 3 years ago, when I moved down to Tampa. Before that, it was 2002 when I went to OOW. I was homesick both times. Homesickness...is a strange thing for me. I grew up as an only child and moved 8 times...so I was always the new kid with no friends. I went to college 2000 miles away from home (CO --> FL). It wasn't until I had a family did I first experience the phenomenon.
3. I have to get on a plane. Yes, it's irrational...I know this. You are far more likely to die on the roads (in the US anyway). Again, this has something to do with the family thing. A feeling of mortality? I don't know. I've probably flown 100 times in my life, I used to love flying...the people watching in the terminal, the feeling of takeoff, landing...

In fact, when I was at UF, I rode this plane countless times...without fear. (And I swear there was duct tape on the propellors...)

I've just been out of the habit of flying. No more ~~Peter Pan~~ Invincibility syndrome for me I guess.

I know lots of you travel quite frequently and are non-plussed by it. I will be thinking of all the people that I know that travel on a regular basis. It might require a beer or 3 to get on board this "first" time.

4. Almost forgot, I get to meet OracleNude. That could be scary too. :)

I guess this post should have been titled, A Fear of Flying, would have been more appropriate.

APEX: Manage Applications

2009-11-27T00:23:00.004-05:00

Home --> Administrative Services

This is the section where you manage your instance wide application attributes. The only functionality that exists in this section, other than mere reporting, is the ability to change an application from Run and Build Application to Run Application Only.

Application Attributes
In the section you can view the following application attributes:

Workspace
Application - the number assigned which will be part of the URL
Parsing Schema
Application Name
Last Updated - The user (workspace admin or developer) who last updated this application.
Date - Time since the last update
Pages - Number of pages in the application
Language

Build Status
From here you can view the Build Status of your applications. It will either be Run and Build Application, which means it can be edited by developers and workspace administrators AND viewed by end users. This is typically the status in a non-production environment; or, it will be Run Application Only which is what the status should be in a production environment. It can not be modified in any way but it can be viewed by the end users.

Edit Build Status
The page where you change the Build Status.

Parsing Schemas
Finally, this tells you what the parsing schema is for the application.

Playing With PARALLEL

2009-11-24T00:56:00.004-05:00

I'm trying to learn about PARALLEL and how it affects SELECT statements in a DW/DSS (non OLTP) environment. I can't really say I know what I'm doing or what I'm looking at, but I'm trying.

I had a DBA strongly discourage me from using PARALLELism on reporting tables (i.e. facts or dimensions). I think he even tried to explain it to me...but it was even more over my head at that time. I think it had something to do with all the processes kicked off then multiplied by the number of users running queries on those tables. "It will bring the database to its knees" he use to say. I never fought much on that because 1, he explained it to me 2, I trusted him and 3, he showed me once all the processes spawned during a typical data load operation. (I thought that was really cool by the way, all those neat little processes doing little chunks of work).

He did however let me (us) use them on staging tables. This is typically where our transformations took place. INSERTs and/or partition swapping.

Anyway, I was reading the 10gR2 docs, the Data Warehousing Guide to be exact on Using Parallel Execution. Lots of neat pictures...which is good for me.

Then I decided to see if I could see what the run times on a SELECT COUNT(*) would be given different levels of parallelism. If you want to know if a table or index has PARALLEL enabled, you need to look at the DEGREE column in %_TABLES and %_INDEXES. I spent about 20 minutes looking for PARALLEL in those tables before I remembered that.

CREATE TABLE big_table 
AS
SELECT *
FROM dba_objects;

I know have 74,431 records to play with. It's not a lot, I know, I just want to see what happens. I then created an anonymous block to:
1. loop through and change the PARALLELism starting with 1 up to 8
2. get the start time from DBMS_UTILITY.GET_TIME
3. loop doing 20 SELECT COUNT(*)s on table table
4. get the end time
5. get the total time
6. print it out
7. put it in a spreadsheet to compare the results side by side

DECLARE
  l_start NUMBER;
  l_end NUMBER;
  l_total NUMBER;
  l_dummy INTEGER;
BEGIN
  FOR j IN 1..8 LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE big_table PARALLEL ' || j;
    dbms_output.put_line( 'PARALLEL (DEGREE):  ' || j );
    FOR i IN 1..20 LOOP
      l_start := dbms_utility.get_time;

      SELECT COUNT(*)
      INTO l_dummy
      FROM big_table;

      l_end := dbms_utility.get_time;

      l_total := l_end - l_start;

      dbms_output.put_line( 'Run ' || LPAD( i, 2, '0' ) || ':  ' || LPAD( l_total, 3, '0' ) );
    END LOOP;
  END LOOP;
END;
/

And here are my results:

Sorry that it's an image...I can't seem to get decently formatted text into Blogger lately.

For this silly, unscientific test, PARALLEL set to 1 (i.e. NOPARALLEL) was the fastest all the way down. I don't know what it means or if it's even valid, but hey, I'm trying. I figured that if I posted something way above my head all you nice folks out there would happily correct me if I mistated something. I am avoiding any statements, if you couldn't tell.

ORACLENERD, Inc.

2009-11-22T23:38:00.002-05:00

Today I made it official...I filed my paperwork with the State of Florida to incorporate. It seemed fairly obvious what the name should be.

If you read the previous post, you probably gathered that little fact.

All of this is related to the fact that I have a new job. I'm not quite ready to talk about the details yet but I believe there is quite a bit of opportunity here. For one, I'll be working with OBIEE again. It's been about 9 months since I last worked with and I was just getting started. There may also be some work with APEX, which would be great. Finally, there's a very outside shot that I may get to work with Exadata v2 in the somewhat near future.

Maybe you're wondering why I incorporated? I don't know, just seemed like fun. Actually, I'll be working as a consultant. This is my first real trek down that road. I have to learn about billable hours, successful travel planning...and probably most importantly, budgeting.

I'm very excited about this opportunity. I will have to travel a bit, but I'll also get to work from home quite a bit so I believe in the long run, family time will probably be greater than when I was going to that "9 to 5" type job where I was actually away from home more than 60 hours a week. We'll see.

How To Apply For an EIN

2009-11-22T19:36:00.003-05:00

EIN being an Employer Identification Number.

You can find the start page here at irs.gov.

This is the second time I have done this, well, second time I have had an EIN. I'm pretty sure the lawyer did it the first time around. If I remember correctly, that cost me a whopping $500, 5 years ago. Of course that included incorporation as well.

I remember how daunting it was back then, starting your own business. I felt like I had to hire a lawyer to do it. The truth is, it's very easy to do. Especially if you consider the sheer volume of documentation that exists on the Oracle database...this is a piece of cake.

It took 10 minutes to complete, but most of that time was spent doing screen captures. Twenty steps in all.

Landing page from the link above:

Begin Application:

Choose the type of business:

"Sub" Corporations:

Confirm your selection:

Why do you need an EIN?

Principal Officer?

Individual details:

Location of corporation:

Verify physical location:

"Accept Database Version" is funny. Do most people understand what that would mean? I guessed that's the USPS version of my address...but I tend to work with these types of scenarios.

Corporation details:

More about the corporation:

What does your corporation do?

I selected other because there was no IT type category on there.

What kind of Other?

What kind of Consulting?

How do you want this delivered?

Summary:

EIN assigned!

Additional information:

Thank you!

Pretty easy all in all. I bet is was a bit harder 5 years ago (i.e. not automated).

Amusingly, this form is only available at certain times of the day. I tried this morning and was told the online assistant didn't work until 7 PM on Sundays. WTF?

APEX: Create Workspace

2009-11-21T19:00:00.005-05:00

Since no where I go seems to have heard of Application Express, I have to demo it (locally) and then convince the DBAs to install it. As such, I do tell them that the APEX Administrator (account) has very powerful privileges, including CREATE TABLESPACE.

I don't think it ever truly registered what this means. In talking to my friend and (current) colleague Daniel McGhan (APEX nerd extraordinaire), he reminded me that when you create a new workspace in APEX that it creates a new tablespace as well (if you don't set "Re-user existing schema?" to "Yes") which means a datafile is also created.

Of course I had to see if for myself:

CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /u01/app/oracle/oradata/testing/system01.dbf
         2 /u01/app/oracle/oradata/testing/sysaux01.dbf
         3 /u01/app/oracle/oradata/testing/undotbs01.dbf
         4 /u01/app/oracle/oradata/testing/users01.dbf
         5 /u01/app/oracle/oradata/testing/example01.dbf

5 rows selected.

That's what I currently have. I'll create a new workspace now to demonstrate. Starting from the Manage Workspace page I select Create Workspace:

I give it a name, APEX_TEST:

On Step 2, I set "Re-user existing schema?" to "No"

Step 3:

Step 4, confirming details:

Confirmation page:

CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /u01/app/oracle/oradata/testing/system01.dbf
         2 /u01/app/oracle/oradata/testing/sysaux01.dbf
         3 /u01/app/oracle/oradata/testing/undotbs01.dbf
         4 /u01/app/oracle/oradata/testing/users01.dbf
         5 /u01/app/oracle/oradata/testing/example01.dbf
         6 /u01/app/oracle/oradata/testing/FLOW_1172420773490155.dbf

6 rows selected.

As you can see, the datafile FLOW_1172420773490155.dbf was created which is mapped to the like named FLOW_1172420773490155 tablespace.

This can obviously be controlled by selecting "Yes" for "Re-user existing schema?", but I just thought it important enough to point out.

I've Got a Job!

2009-11-19T14:40:00.000-05:00

Details to follow.

APEX: Manage Service > Manage Environment Settings

2009-11-18T00:08:00.001-05:00

Home --> Administrative Services --> Manage Service

As I mentioned previously, this is probably the most important area for administering your APEX instance.

Messages

There are 2 types of messages you can create, one is the Login message and one is the System Message

The Login message appears at the top of the Workspace Login screen:

The System messages appears after you have logged in.

Feature Configuration

Application Development
1. Allow PL/SQL Program Unit Editing - This pertains to the SQL Workshop. Will you allow end-users (Developers/Administrators), who have access to SQL Workshop, to edit PL/SQL.
2. Create demonstration objects in new workspaces - By default, when you create a new workspace, the sample application is automatically installed in the schema that it is mapped to. In my case, CJUSTICE. There are database objects included with this object as well. If you would like to see those database objects and need a script to remove them, read here.
  
  I don't like that it gets created automatically so I typically turn it off.

SQL Workshop
1. SQL Commands Maximum Inactivity in minutes - "Identify the maximum amount of time a transactional command in the SQL Command Processor waits before timing out."
2. SQL Scripts Maximum Script Output Size in bytes - "Identify the maximum amount of output a single SQL Script can generate. SQL scripts are run from the SQL Workshop."
3. SQL Scripts Maximum Workspace Output Size in bytes - "Identify the maximum amount of space all scripts within a workspace may consume. SQL Script results are the output generated when running SQL Scripts from the SQL command processor."
4. SQL Scripts Maximum Script Size in bytes - "Identify the maximum size of a SQL script used within the SQL Workshop."
5. Enable Transactional SQL Commands - "Enable Transactional SQL Commands for the entire instance. Enabling this feature permits users of SQL Commands to issue multiple SQL commands within the same physical database transaction."

Monitoring
1. Enable Database Monitoring - "Enable or disable monitoring within the SQL Workshop."
Honestly, I have no idea what kind of monitoring is done here. I haven't been able to find any related reports...which doesn't mean they don't exist, just that I can't find them.

Security

Security

Set Workspace Cookie
Disable Administrator Login - Defaults to No (otherwise, how would you log in?)
Disable Workspace Login - Defaults to No
Allow Public File Upload
Restrict Access by IP Address

HTTPS

Require HTTPS - Use a comma delimited list of IP Addresses to restrict, can be either 1 or up to 4 values

Session Timeout

Maximum Session Length in Seconds
Maximum Session Idle Time in Seconds

Excluded Domains

Domain Must Not Contain - "Enter domains (not including the port), separated by colons, that should not be allowed in regions of type URL or accessed as a Web service. "

Account Login Control

Require User Account Expiration and Locking - "Select Yes to enable Application Express user account expiration and locking features across all workspaces. This selection prevents the same feature from being disabled at the workspace level.

Select No to allow individual workspaces to enable or disable this feature independently.

Application Express user account expiration and locking features apply to end-user accounts created using the Application Express end-user account management interface."
Maximum Login Failures Allowed - "This setting applies to accounts used to access the Application Express administration and development environment only. It does not apply to end-user accounts used by applications developed in Application Express.

However, this setting is used as the default workspace-level "Maximum Login Failures Allowed" preference, which workspace administrators can change. The workspace-level preference applies to the end-user accounts within that workspace."
Account Password Lifetime (days) - Self Explanatory

Workspace Password Policy

Minimum Password Length - Self Explanatory
Minimum Password Differences - "Enter the number of differences required between old and new passwords. The passwords are compared character by character, and each difference that occurs in any position counts toward the required minimum difference.

This setting applies to accounts for workspace administrators, developers, and end use"
Must Contain At Least One Alphabetic Character - Self Explanatory
Must Contain At Least One Numeric Character - Self Explanatory
Must Contain At Least One Punctuation Character - Self Explanatory
Must Contain At Least One Upper Case Character - Self Explanatory
Must Contain At Least One Lower Case Character - Self Explanatory
Must Not Contain Username - Self Explanatory
Must Not Contain Workspace Name - Self Explanatory
Must Not Contain - colon seperated list of words that cannot be used in the password, default words are: "oracle:hello:welcome:guest:user:database"
Alphabetic Characters - default: "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
Punctuation Characters - default: "!"#$%&()``*+,-/:;<=>?_"

Service Administrator Password Policy
1. Policy - You can either define the Workspace Password Policy (above) or just use the default strong password policy. For my sandbox versions, I tend to make it as lax as possible so I do not have to remember complex passwords when putting things like this together.

Instance Settings

For most organizations, the only 3 areas you need to worry about here are Email, Report Printing and Wallet.

Self Service

Provisioning Status - 3 options here: Manual, Request and Email Verification. For most organizations Manual will do just fine. That will allow your end-users to follow the normal protocols set forth in your organization. For hosted sites, like apex.oracle.com, Email Verification is the way to go.
Development Service URL - Used in conjunction with Request or Email Verification above.

Email Provisioning

Email Provisioning - By default, is is enabled, but without the other settings it is useless. I turn this off...but it doesn't really matter for me since I use a sandbox.

Schema

Require New Schema - Do you want to require the user to obtain a new schema or reuse an existing one?

Email

SMTP Host Address - This is where you set up your internal mail server. All APEX mail calls will use this server by default.
SMTP Host Port - Self Explanatory
Administration Email Address - Self Explanatory
Notification Email Address - Self Explanatory

Wallet
1. Wallet Path - Path to the wallet on the database server.
2. Wallet Password - Self Explanatory

Report Printing
1. Print Server - Standard is the Default. To enable pdf printing you need BI Publisher and this is where you enter the server information.
2. Print Server Protocol
3. Print Server Host Address
4. Print Server Port
5. Print Server Script
For a How To on setting up APEX and BI Publisher, check out the RittmanMead blog entry Oracle ApEx and BI Publisher.

New Workspace Request Size

If using the Self Service module, this is where you'll set the sizes (and the default) of the workspaces to choose from. You can enable them all or just a few.

Workspace Change Request Size

Just like the New Workspace Request Size section, you can modify the size of workspaces you are offering.

APEX: Drop Sample Application Database Objects

2009-11-17T15:42:00.005-05:00

Here's a simple script to remove the database objects from the default Sample Application from Application Express (APEX). Deleting the application from the interface does not remove the database objects.

There are 30 database objects created by default along with the Sample Application:

OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------
FUNCTION            CUSTOM_AUTH
                    CUSTOM_HASH
INDEX               DEMO_CUSTOMERS_PK
                    DEMO_CUST_NAME_IX
                    DEMO_ORDER_ITEMS_PK
                    DEMO_ORDER_PK
                    DEMO_ORD_CUSTOMER_IX
                    DEMO_PAGE_HIERARCHY_PK
                    DEMO_PRODUCT_INFO_PK
                    DEMO_USERS_PK
LOB                 SYS_LOB0000077614C00007$$
SEQUENCE            DEMO_CUST_SEQ
                    DEMO_ORDER_ITEMS_SEQ
                    DEMO_ORD_SEQ
                    DEMO_PROD_SEQ
                    DEMO_USERS_SEQ
TABLE               DEMO_CUSTOMERS
                    DEMO_ORDERS
                    DEMO_ORDER_ITEMS
                    DEMO_PAGE_HIERARCHY
                    DEMO_PRODUCT_INFO
                    DEMO_STATES
                    DEMO_USERS
TRIGGER             BI_DEMO_USERS
                    DEMO_ORDER_ITEMS_GET_PRICE
                    DEMO_USERS_T1
                    INSERT_DEMO_CUST
                    INSERT_DEMO_ORDER_ITEMS
                    INSERT_DEMO_PROD
                    UPDATE_ORDER_TOTAL

30 rows selected.

If you have nothing else in your schema, you can run this:

DECLARE
  l_sql VARCHAR2(32767);
BEGIN
  FOR i IN ( SELECT object_name, object_type 
             FROM user_objects
             WHERE object_type NOT IN ( 'LOB', 'INDEX', 'TRIGGER' ) 
             ORDER BY object_type )
  LOOP
    CASE 
      WHEN i.object_type = 'TABLE' THEN
        l_sql := 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS PURGE';
      ELSE
        l_sql := 'DROP ' || i.object_type || ' ' || i.object_name;
    END CASE;
    EXECUTE IMMEDIATE l_sql;
  END LOOP;
END;
/

If however, you do have other items in there, use this:

DROP FUNCTION CUSTOM_AUTH;
DROP FUNCTION CUSTOM_HASH;
DROP SEQUENCE DEMO_ORDER_ITEMS_SEQ;
DROP SEQUENCE DEMO_PROD_SEQ;
DROP SEQUENCE DEMO_ORD_SEQ;
DROP SEQUENCE DEMO_USERS_SEQ;
DROP SEQUENCE DEMO_CUST_SEQ;
DROP TABLE DEMO_STATES CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_PAGE_HIERARCHY CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_USERS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_ORDER_ITEMS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_ORDERS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_PRODUCT_INFO CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_CUSTOMERS CASCADE CONSTRAINTS PURGE;

Triggers, LOBs and Indexes will be dropped along with their associated objects.

Dilbert: You're Fired!

2009-11-16T11:40:00.001-05:00

APEX: Install 3.2.1 on 11gR2

2009-11-16T00:01:00.003-05:00

Yes, I know it comes pre-installed with 11g. No, I didn't bother to check what the default version was either. I needed practice so I removed it and re-installed it. This follows my recent install of 11gR2 on OEL v5.4.

First up, get the latest version from apex.oracle.com. I unzipped the file in a shared folder (which was then mounted to OEL) in the following directory (from OEL):

/mnt/software/oracle/apex/apex

In another window, I open up the installation doc.

From the database server, I moved to that directory and logged in as SYS. Here's the list of files in that directory:

[oracle@oracledb apex]$ ls -l
apex_epg_config_core.sql
apex_epg_config.sql
apexins.sql
apexvalidate.sql
apxchpwd.sql
apxconf.sql
apxdbmig.sql
apxdevrm.sql
apxdvins.sql
apxe101.sql
apxe102.sql
apxe111.sql
apxldimg.sql
apxpatch.sql
apxrelod.sql
apxremov.sql
apxrtins.sql
apxsqler.sql
apxxemig.sql
apxxepwd.sql
catapx.sql
coreins.sql
devins.sql
endins.sql
load_trans.sql

Since it's already installed, I want to remove it. To do so, I need to run apxremov.sql. I run it and a few minutes later its gone. I confirm this by looking in the DBA_REGISTRY view:

SELECT comp_name, version
FROM dba_registry
ORDER BY comp_name;

There are 4 basic installation scenarios:

Downloading from OTN and configuring the embedded PL/SQL gateway (11g)
Downloading from OTN and configuring Oracle HTTP Server (9.2.0.3 -- 11gR2)
Installing from the database and configuring the embedded PL/SQL gateway (11g)
Installing from the database and configuring the Oracle HTTP server (11g)

I'm going with #1.

I've removed the default installation of APEX, now I'm going to install it. I'm still logged in to SYS so I just need to run the apexins.sql script.

SQL>@apexins.sql SYSAUX SYSAUX temp /i/

The first parameter SYSAUX is the tablespace where APEX user will ive. The second SYSAUX is where the APEX files will live. TEMP is the name of the temporary tablespace and /i/ is the image (virtual) directory. Installation on my standalone machine took about 30 minutes.

The installation script logs you out at the end, so for the next step, you have to login as SYS account. Now we'll be setting the ADMIN account password. This password will be used here:

or here if you use INTERNAL as the workspace name (thanks John!):

Script to change the ADMIN password: apxchpwd.sql

Since this is technically an upgrade, I need to run apxldimg.sql

SQL> @apxldimg /mnt/software/oracle/apex

PL/SQL procedure successfully completed.

Directory created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.
timing for: Load Images
Elapsed: 00:02:33.48

Directory dropped.

Verify that the ANONYMOUS account is unlocked, if it isn't unlock it.

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

We need to verify the EPG port now:

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

If a 0 (zero) is returned, EPG is not set-up yet. I'm going to use port 8080 because I'm too lazy to figure out, for now, how to set it up on port 80.

EXEC dbms_epg.sethttpport( 8080 );

Finally we have to enable network services. Since this is my own sandbox, security is not a concern. I'm going with the default as provided by the documentation:

DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  SELECT ACL 
  INTO ACL_PATH 
  FROM DBA_NETWORK_ACLS
  WHERE HOST = '*' 
    AND LOWER_PORT IS NULL 
    AND UPPER_PORT IS NULL;

  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) 
  INTO ACL_ID
  FROM 
    XDB.XDB$ACL A, 
    PATH_VIEW P
  WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) 
    AND EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200', 
     'connect') IS NULL 
  THEN 
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
    'APEX_030200', TRUE, 'connect'); 
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
      ( 'power_users.xml',
        'ACL that lets power users to connect to everywhere',
        'APEX_030200', 
        TRUE, 
        'connect' );
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

You should be ready to go. The url will be http://localhost:8080/apex/apex_admin

If you are prompted with a XDB login window like this:

You may have forgotten to unlock the ANONYMOUS account. (I did, naturally).

APEX: Manage Service

2009-11-15T23:30:00.001-05:00

Home --> Administrative Services

Within the Administrative Services space, you have 4 options:

Manage Service
Manage Workspace
Manage Applications
Monitor Activity

Of those 4, Manage Service is probably the most important area. This is the location where you will administer your entire APEX instance.

Within the Manage Service space, you have 3 categories:

Manage Service
Manage Environment Settings
Manage Shared Components

Manage Shared Components

This is where you manage your public themes. What are public themes?

Public themes allow you to add to the theme repository. They are available when you create an application or when you create a theme for an existing application.

You create a public theme by selecting a theme from within a workspace and application. Once created, it cannot be edited directly. To edit, create an application based off the theme, edit the theme within that application and then you can create a new public theme with the updated application theme (and delete the old, if you no longer want it).

Manage Service

In the Manager Service section (of Manager Service, naturally), there are 5 sections:

Site-Specific Tasks
Logs
Session State
Mail Queue
Installed Translations

I just now learned what Site-Specific Tasks are (isn't that the whole point of this?). You can either create a task that will display on the Workspace Home or the Workspace Login. I've created 2 tasks, one for each area:

Where do those display?

Workspace Login

Workspace Home

Site-Specific Tasks is helpful to relay messages to your development staff, perhaps a planned outage or a reminder to use constraints. ;)

Next up are Logs. From here you can manually purge SQL Workshop, Page View Acitivity, Developer Activity , External Click, Mail and Login Access logs. The reports on these logs can be viewed from the Workspace Home. Not really a whole lot to see here.

Session State
Not a whole lot to see here either. There is a report to view recent sessions with drill-down capabilities, but that's about it. The other 2 items in this section are "Purge Sessions, by age" and "Session State Statistics."

Mail Queue
Basically just a log of all the pending emails to be sent. You have the option to send them or delete individual emails.

Installed Translations
Finally, not much to see here either. Just a list of, you guessed it, installed translations.

I'm going to take on the final piece of the Manage Service section in a separate post. This is where the guts of your APEX instance are managed. Lots of fun stuff here.

DBMS_UTILITY.WAIT_ON_PENDING_DML

2009-11-15T02:53:00.004-05:00

I've been reading up on Edition-Based Redefinition (which I can't link to because the docs seem to be missing right now).

In my readings I ran across DBMS_UTILITY.WAIT_ON_PENDING_DML which appears to be new to 11gR2 (there is no entry for it in the 11gR1 docs, which again, I can't link up currently). Based on my reading (I have the docs locally), this function appears to be used specifically for the new editioning feature.

It's listed as a procedure in the docs (don't ask for the link):

and a function if you do a describe on dbms_utility.

This procedure waits until all transactions (other than the caller's own) that have locks on the listed tables and began prior to the specified SCN have either committed or been rolled back.

So I wanted to see how it works.

First, I create the table:

CREATE TABLE s
(
  x NUMBER
);

I then open up a second session. In that session, I will run the WAIT_ON_PENDING_DML function which should monitor session 1.

Session 1:

BEGIN
  FOR i IN 1..100 LOOP
    INSERT INTO s ( x ) 
    VALUES ( DBMS_RANDOM.VALUE );
    dbms_lock.sleep( .5 );
  END LOOP;
  COMMIT;
END;
/

I'm inserting 100 records with a wait of half a second between inserts. This should take a little over 50 seconds.

Session 2

Note I've set the timeout to 30 seconds so that it will finish prior to Session 1

DECLARE
  l_bool BOOLEAN;
  l_scn NUMBER;
BEGIN
  l_bool := dbms_utility.wait_on_pending_dml
              ( tables => 'cjustice.s',
                timeout => 30,
                scn => l_scn );

  dbms_output.put_line( 'scn:  ' || l_scn );
  IF l_bool THEN
    dbms_output.put_line( 'true' );
  ELSE
    dbms_output.put_line( 'false' );
  END IF;
END;
/

scn:  924643
false

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.03

The return value of "false" means that Session 1 is still inserting, which I confirmed visually (I should really create a video for this). Once Session 1 completed, I reran Session 2's anonymous block and received these results:

CJUSTICE@TESTING>/
scn:  924773
true

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

In regards to editioning, you would poll the database using this utility to see if there is, well, any pending DML. One aspect of Editioning allows you to create temporary triggers that will help you to migrate your application to the latest version without worrying about locking or timeouts. Pretty cool stuff.

I'll have more Editioning soon.

11gR2: Installfest

2009-11-13T02:22:00.003-05:00

I installed Oracle Enterprise Linux 5.4 a few weeks ago in a VirtualBox virtual machine...I just didn't have the inclination to install the database.

Way back in June, I installed VirtualBox, OEL (5.3), 11gR1 and ApEx. To do that, I followed this guide on OTN by John Smiley (thanks John!). This time, I was going to try to do it from the docs. I can't say it was easy to read, so I was back at John's article before to long.

First up was preparing OEL for the database install. This included setting up kernel parameters and loading some extra libraries. There were slight differences between OEL 5.3 and 5.4, but I had the docs handy...and the installer complained loudly if something was amiss.

Next up, mount a shared folder in the virtual machine so that I could keep it to a relatively small size, 15 GB to be exact. Plus, I wanted to use the shared folders functionality. So I created a directory in the /mnt directory called software (as root of course), then mounted the shared folder.

root@oracledb#mkdir software
root@oracledb#mount -t vboxsf software /mnt/software

"software" was the name I gave the shared folder through the VirtualBox interface, that mapped to /opt/software on my host machine (ubuntu 9.04).

Then I downloaded the 11gR2 software, 2 files, linux_11gR2_database_1of2.zip and linux_11gR2_database_2of2.zip. I then used Archive Manager to extract the files. Strangely, it created 2 folders, "database" and "database(2)." That can't be right.

Back to OEL and I cd'd to the directory, /mnt/software/11gR2/database and ran the installer.

I did not provide my email address and I unchecked the box that would send me updates from Oracle Support.

Selected Yes to continue.

I'm just accepting the defaults here.

Not really sure what the difference is between a Desktop Class and Server Class install, so I again accepted the default (Desktop).

Now I have to fill in 4 fields:
* OSDBA Group (drop down)
* Global database name
* Administrative password
* Confirm password

* OSDBA Group = oracle (for me)
* Global database name = testing
* Administrative password = testing, whoops, strong password rules, not testing
* Confirm password = same as above

I chose "oracle" as the oraInventory Group Name.

Some prerequisite checks, to make sure I have the right libraries and the kernel parameters are OK.

This is when I realized the values were different between 11gR1 and 11gR2 and went back to the docs to get the correct ones. I realized afterwards though, that if you select the particular failure, it will tell you what the values should be. Nice.

After I went back and changed the values, I only had a failure on the memory check, but I checked the "Ignore All" checkbox and proceeded forward.

(I missed a screen shot the second go-around, so ignore the OS Kernel Parameters and Package errors.

Confirmation screen.

And were off!

I like to take a lot of pictures.

Creating the database...

Screen behind the screen.

Almost done.

Post-installation tasks.

Running /u01/app/oraInventory/orainstRoot.sh

Running /u01/app/oracle/product/11.2.0/db_home_1/root.sh

Done.

And done.

I can login from the virtual machine...

And I can login from the host machine.

Enterprise Manager from the host machine.

And my new account from the host machine.

Easy right?

Special thanks to Tim Hall as well, he's got a great primer for installing 11gR2, Oracle Database 11g Release 2 (11.2) Installation On Enterprise Linux 5.0.

Almost forgot, my problem with Archive Manager creating "database" and "database(2)"? Solved by twitter naturally:

So I unzipped the 2 files from a terminal window and it (apparently) put everything in the same folder. Win.

Secretary Justice

2009-11-12T22:23:00.003-05:00

I started out as a secretary and it's come full circle now.

Tonight I was elected Secretary of the Suncoast Oracle Users Group (SOUG).

What does that mean? Well, pretty simple I think. Blog. Sum up the meetings in a fun and easy to read way to convince them that they are really missing out.

As a member of the board, I will also (eventually) push for twice monthly meetings. One more formal with the presentation format and one more casual.

We spent an hour tonight talking after the presentation...it would be nice to do that in a more relaxed environment.

Fortunately, I've been writing about the group for some time, so this is just a natural extension of that. I look forward to serving.

Plus, it's another job I have that doesn't pay anything. ;)

Security Tools Malware

2009-11-11T23:52:00.004-05:00

My parent's neighbor called me this afternoon...his computer had a virus.

There was no virus software installed.

I told him to try AVG (which makes me want to put parenthesis after it with a GROUP BY clause), scan the computer and then call me back.

He did call back, but he was unable to download the software. He kept getting messages that his computer was infected and that his credit card details were being delivered to the 'tubes. Just bring it over and I'll take a look.

I tried opening up Task Manager, no deal, Security Tools identified it as a virus. WTF?

I tried opening up a shell window. Ditto.

Here's what "Security Tool" looks like:

Hmmm...I could use the Ubuntu Live CD and try to remove it...you know, since I'm an expert and all that.

So I opened up a terminal and started poking around the file system (/media/disk). Nothing jumped out at me. So I googled and found this entry. It suggested installing AVG for Linux and then scan the Windows disk. So I did. Took about 2 hours to complete and it came back with nothing.

I gave up on trying to solve the problem with Linux. I really thought I would be cool. Oh well.

So I did some more research and found this entry on "Security Tool." It suggested downloading MalwareBytes’ Anti-Malware, but it was impossible to do anything. I then found this post on the Symantec forum which suggested opening up Task Manager as soon as you login. That seemed to work. Found the process and killed it immediately.

I then installed MalwareBytes', scanned the computer and removed the infected files. Voila! It only took about 6 hours from start to finish. I will get a free lunch out of it though...I'll take all the free I can get these days. I also updated Windows, installed Firefox and avast! so we can hopefully avoid these same issues in the future.

On a side-but-related note, I ran across an infected site last week and it looked like this:

I found it quite amusing...but probably authentic enough to fool a number of people.

Hopefully some poor soul out there will get good use out of this.

My Oracle Support Fiasco

2009-11-11T14:10:00.009-05:00

I haven't had access to metalink for about a year now. I certainly don't envy those who do though.

Last week, Oracle deployed My Oracle Support, a new flash based system.

In the post, Classic MetaLink:Na,na,na,Nah,na,na. Hey hey Goodbye, by Chris Warticki, Sr. Customer Support Manager [ Twitter ], says

Goodbye old friend. Thanks for the memories. As old blue eyes crooned..."And now, the end is near". We did it our way with My Oracle Support.

If you've been off the grid, or totally out of the loop and completely clueless (poor word choice, but this in the context of getting customers to the training), Classic MetaLink retired on November 6th. My Oracle Support training sessions can be found here. Please register. Change is here. Get in front of this one...seriously.

Some of you have been very vocal with your therapists, on OTN, Twitter, Linked-In Groups, OracleMix, UserGroups and other channels, including this very blog.

In case you didn't read in the FAQ - there will be an HTML version for anyone unable to use flash. Hint: If your browser allows flash, there isn't an HTML option. You'll have to figure out the rest.

Visit the Information Page for the Latest Details

Oracle announced that Classic MetaLink will retire and My Oracle Support will be upgraded and become the single support interface. Review the Transition Information Page for significant changes, benefits, training, communications and FAQs

According to all reports, this transition has been an unmitigated disaster.

oracle-l

Metalink Fiasco - so far there are about 30 replies. Let's not forget the off-shoot of this one, Metalink Fiasco -- Home many names can we come up with for MOS?

Issues with My Oracle Support - Only about 20 responses to this one.

There there's one advising patience, helping Oracle help us.

And the funny, Quotes from Oracle Support.

Comments that weren't posted (or I didn't see them): Oracle Support Blog

Blogs

Quite frankly warticki, you should apologize! by Nuno Souto

#Fail: My Oracle Support by rnm1978 [ blog | twitter ]

MetaLink, we barely knew ye from Jared Still.

It's a mess...but it's no a surprise, by Daniel Fink.

My Oracle Support by Doug Burns. Then another from Mr. Burns (eggcellent!).

Fanning the Flames of Support Discontent from ManagingAutomation

Hans Forbrich has come out in support, sort of, with In support of My Oracle Support? - Part 1

Tech Magazines

From COMPUTERWORLD Users call new Oracle support portal a 'fiasco'

PCWorld, Oracle Support Portal Woes Could Erode Users' Trust

CIO, Users: New Oracle Support Portal a 'fiasco'

Mr. Warticki did provide an update yesterday:

This weekend we migrated Oracle & BEA customers over to My Oracle Support from Metalink. Yesterday (Nov 9th), some customers experienced problems expressed in the comments that I wrote about in this blog. The issues were mostly around performance and registration, but to our knowledge, we have resolved these issues. If there are problems that we don't know about, please contact us!

I just want to highlight an important step in the migration -- one that we tried to communicate through repeated postings and email blasts. Customers need to validate their MetaLink email address against Oracle's corporate single sign-on prior to conversion (the migration uses Oracle's corporate single sign-on to improve security). For users who may have missed this notice, there is a link to an FAQ on the landing page that will guide you through the registration process.

My Oracle Support is really a terrific platform and this change is going to bring value to customers. But please, if you experience any issue migrating, don't hesitate to contact us -- we want to hear from you so call your local support phone number - hit this link http://www.oracle.com/support/contact.html

Today he apologized for his perceived misinterpretation:

To all Oracle Customers, I do apologize for any misinterpretation I may have caused in that blog entry.

As you have just read, I do accept responsibility and accountability for my actions.

Please understand that my intent was not to insult anyone. The purpose was to draw attention, call to action for the training and to add some humor to what most view as bland blogging.

I'm happy that everyone is getting their frustrations out, even if I need to wear kevlar. I have thick skin. I can take it. I know that you're upset at the portal not the person, the UI and not the individual, and the portal performance not the personal performance. I know that Oracle's leadership is reading, listening and responding.

I have always been FOR the customer. Many of you know that.

Sincerely, Respectfully and Apologetically,

I certainly don't envy him right now.

I do hope this gets resolved either by rolling back to the Classic Metalink or all the problems solved in the new system. I'm certainly glad that I am not dependent on MOS at this point.

Update
Chris has linked up to a Migration of Classic MetaLink Users to My Oracle Support FAQ.

Update 11/18/2009
Mr. Fink published the results of his survey, you can find them here, My Oracle Support Survey Results!.

Update 11/23/2009
My Oracle Support is starting to perform from Hans Forbrich.

There’s not a lot of love for My Oracle Support from Duncan Davies, aka the PeopleSoft Tipster.

Oracle Press Release on Sun Merger

2009-11-09T18:28:00.004-05:00

Original can be found here:

Redwood Shores, Calif. - November 9, 2009

Oracle's acquisition of Sun is essential for competition in the high end server market, for revitalizing Sparc and Solaris and for strengthening the Java development platform. The transaction does not threaten to reduce competition in the slightest, including in the database market. The Commission's Statement of Objections reveals a profound misunderstanding of both database competition and open source dynamics. It is well understood by those knowledgeable about open source software that because MySQL is open source, it cannot be controlled by anyone. That is the whole point of open source.

The database market is intensely competitive with at least eight strong players, including IBM, Microsoft, Sybase and three distinct open source vendors. Oracle and MySQL are very different database products. There is no basis in European law for objecting to a merger of two among eight firms selling differentiated products. Mergers like this occur regularly and have not been prohibited by United States or European regulators in decades.

The U.S. Department of Justice carefully reviewed the proposed acquisition during the normal Hart-Scott-Rodino review and considered it again when the European Commission initiated a second phase review. On both occasions the Justice Department came to the conclusion that there is nothing anticompetitive about the deal, including specifically Oracle’s acquisition of the MySQL database product. The U.S. Department of Justice approved the acquisition without conditions and terminated the waiting period under the Hart-Scott-Rodino Act on August 20, 2009.

Sun's customers universally support this merger and do not benefit from the continued uncertainty and delay. Oracle plans to vigorously oppose the Commission’s Statement of Objections as the evidence against the Commission’s position is overwhelming. Given the lack of any credible theory or evidence of competitive harm, we are confident we will ultimately obtain unconditional clearance of the transaction.

Apparently the European Commission, the European Union's executive arm, issued a statement of objections on Monday. Sun issued a statement as well:

On November 9, 2009, the European Commission issued a statement of objections relating to the acquisition of Sun by Oracle Corporation. The Statement of Objections sets out the Commission's preliminary assessment regarding, and is limited to, the combination of Sun's open source MySQL database product with Oracle's enterprise database products and its potential negative effects on competition in the market for database products. The issuing of a Statement of Objections allows addressees to present arguments in response to the Commission's preliminary assessment of the competitive effects of a notified transaction. A Statement of Objections is a preparatory document that does not prejudge the European Commission's final decision. Any final decision by the European Commission is subject to appeal to the European Court of First Instance.

I'm sorry, but the EU just doesn't get it. First, it's open source. Do they really lack the technical acumen to determine what this means?

Second, there is plenty of competition out there. DB2, SQL Server, Sybase, Intercache, BTree...not to mention Postgres and other open source alternatives. This is just embarassing.

Update
Looks like the Department of Justice got into the act as well:

WASHINGTON - Deputy Assistant Attorney General Molly Boast of the Department of Justice's Antitrust Division issued the following statement today after the European Commission (EC) issued a statement of objections regarding Oracle Corporation's proposed acquisition of Sun Microsystems Inc.:

"After conducting a careful investigation of the proposed transaction between Oracle and Sun, the Department's Antitrust Division concluded that the merger is unlikely to be anticompetitive. This conclusion was based on the particular facts of the transaction and the Division's prior investigations in the relevant industries. The investigation included gathering statements from a variety of industry participants and a review of the parties' internal business documents. At this point in its process, it appears that the EC holds a different view. We remain hopeful that the parties and the EC will reach a speedy resolution that benefits consumers in the Commission's jurisdiction.

"Several factors led the Division to conclude that the proposed transaction is unlikely to be anticompetitive. There are many open-source and proprietary database competitors. The Division concluded, based on the specific facts at issue in the transaction, that consumer harm is unlikely because customers would continue to have choices from a variety of well established and widely accepted database products. The Department also concluded that there is a large community of developers and users of Sun's open source database with significant expertise in maintaining and improving the software, and who could support a derivative version of it.

"The Department and the European Commission have a strong and positive relationship on competition policy matters. The two competition authorities have enjoyed close and cooperative relations. The Antitrust Division will continue to work constructively with the EC and competition authorities in other jurisdictions to preserve sound antitrust enforcement policies that benefit consumers around the world."

For the life of me I can't find the "official" statement of objection by the European Commission.

It's Official

2009-11-09T16:02:00.003-05:00

I got my ACE profile posted on Oracle:

I tried to get them to use the entire photo, but alas it wouldn't happen:

Can't blame me for trying.

Application Express: A Guide

2009-11-06T17:00:00.009-05:00

Since I'm going through and relearning everything, I'm creating this page as an index page for all future posts. Yes, I could use a label and have blogger do it automatically...I'm just trying to make more work for myself.

What's the abbreviation for Application Express? Joel Kallman answers that here and it's "APEX."

Installation Guide: 11gR2

Administrative Services
- Manage Service
  - Manage Environment Settings
- Manage Workspaces
- Manage Applications
- Monitor Activity
Workspace
- Application Builder
- SQL Workshop
  - Object Browser
  - SQL Commands
  - SQL Scripts
  - Query Builder
- Utilities
  - Data Load/Unload
  - Generate DDL
  - Object Reports
  - Recycle Bin
  - Database Monitor
  - APEX Views
  - Schema Comparison
  - About Database

Other
Drop Sample Application Database Objects

ApEx: Administrative Services

2009-11-06T00:28:00.007-05:00

Home

Getting back into the Application Express (ApEx) world again.

Trying to refresh my rusty memory on some key aspects. I figured I'd start with Administrative Services, in other words, controlling/configuring your ApEx installation.

Login
I found this confusing when I first started using ApEx. There are 2 login screens, one for the Administrative Services and one for Workspace Administration.

Administrative Services is in the (URL) form of:

http://<domain>:<port>/apex/apex_admin

For me it looks like this:

http://192.168.1.199:8080/apex/apex_admin

Which quickly resolves to:

http://192.168.1.199:8080/apex/f?p=4550:10

A helpful picture:

For Workspace Administration, the URL looks like this:

http://<domain>:<port>/apex/apex

For me it looks like this:

http://192.168.1.199:8080/apex/apex

Which quickly resolves to:

http://192.168.1.199:8080/apex/f?p=4550:1

A helpful picture:

Services
After you login, you are presented with the following screen:

Your choices are:

Manage Service
Manage Workspace
Manage Applications
Monitor Activity

Actually, I'll leave it at that for the time being. Each of those sections incorporates a bit of functionality which probably deserve their own post.

Losing A Job: The Aftermath

2009-11-05T23:38:00.005-05:00

So I've got a little experience in "losing" my job. Laid off, furloughed, rehired, resigned (and walked out), new job, laid off.

Of course I didn't lose one of those jobs...I know right where they are. I just can't or won't go back to them.

Fortunately for me, I've made a lot of good friends online. Very supportive friends. It makes "losing" a job that much easier.

Of course there's more to it...the effect it has on the family.

Kate...she doesn't understand yet. She just smiles and laughs and makes everything go away.

LC, however, is starting to grasp it. He doesn't say anything to me, but he does say stuff to his mother.

"Are we going to have to move back to Gainesville?"

"Is poppa going to pay our bills for us?"

You know...fun stuff like that.

Strangely, not to long ago he was always asking me to fire myself so I could be home all the time (it's the only way I get a vacation these days).

And then there's the wife, the lovely woman who agreed to marry me (yikes!) and has supported me through all of this. Without her, I could not have made it as far as I have.

When we met, we were polar opposites. I was a bit on the timid side and she...well, she doesn't have much of a filter. A perfect match!

Over time I have found my voice and she has muted hers (only a little). We're meeting somewhere in the middle (at least that's what I tell myself).

Kris does not work. She held LC in her hands and told me she didn't want to go back to work. I couldn't argue. Although she did work briefly but was let go because LC had an ear infection and needed medical attention. We didn't fight it. She's a stay-at-home mom, and we like it that way. She's been able to help Kate out with doctor visits, physical therapy, speech therapy, HGH injections...the list goes on and on.

Since she doesn't work, she feels, at times, out of control. When I "lose" my job, that feeling is heightened.

Not really sure what the point of this was...it's not to feel sorry for me, that's for sure. Just a warning to those out there in a similar situation. It doesn't effect just you...it effects your entire family (I haven't even mentioned my parents, or Kris' parents).

The only advice I can give to help alleviate this is:
1. Have enough money in the bank to weather the storm
2. ...OK, just 1.

Method R + C.J. Date = Fun!

2009-11-05T18:05:00.004-05:00

I just got an email blast from Mr. Cary Millsap.

It reads as follows:

This brief note is to update you on some of the things we've been doing at Method R Corporation.

C. J. Date seminar - There's a new course on our schedule: we've invited Chris Date to teach "How to Write Accurate SQL and Know It: A Relational Approach to SQL" January 26-28, 2010 in the Dallas/Fort Worth area. Our make-or-break date for the course is December 4, 2009, so if you're interested please enroll soon. I'm personally excited about the opportunity to sit in a classroom for three days with one of the inventors of our whole industry. http://method-r.com/education/107-cj-date-course.

MR Tools - A few weeks ago, we released a new set of software tools that let you do some pretty unbelievable things with Oracle trace data. Take a look at our Guided Tour pages behind the individual tool links at http://method-r.com/software/mrtools.

Method R Profiler - Recently, we released Method R Profiler 5.2.0. The Profiler now runs on Mac and Linux, in addition to Windows. And new with 5.2 is a graphical user interface that's a genuine pleasure to use. I was always a command line guy when I used the Profiler, but with the new 5.2 GUI, I'm sold. http://method-r.com/software/profiler.

The tools are currently a bit out of my league, but the opportunity to listen to C.J. Date would be cool (cool as in geek chic or nerd chic of course). I have no idea what my schedule will be like at this point, but I would definitely like to attend. Hopefully I'll have some things tied up so I can plan for it.

OpenOffice Writer: AutoComplete?

2009-11-03T20:56:00.002-05:00

Now that I'm using Ubuntu, I'm also using OpenOffice.

However, I like Google Docs quite a bit so I've used OpenOffice sparingly.

I've had trouble with Google Docs though; the Download as Word bastardizes the document. Looks great as a pdf, not so much as a Word document. The reason for creating a Word document is for recruiters, they all seem to have some sort of application that parses the Word document and fills in your employment history and such. This would be a great place for XML and some sort of standard wouldn't it?

Anyway, in OpenOffice Writer, I type in:

November 2009

which Writer immediately turns into

11/01/09

No! Stop it!
I tried turning AutoComplete off:

Tools --> AutoCorrect

and disabling everything I can find...try again. Same result.

Reading this thread, I begin to wonder if it's like Excel...you know, you enter a date and it automatically puts it into the default date format.

Sure enough, right click on the column, select "Character" and voila! No more transformation.