Chet Justice

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.

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.

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.

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?

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.

Details to follow.

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
  
  


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


• HTTPS
  
  


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


• Session Timeout
  
  


1. Maximum Session Length in Seconds
2. Maximum Session Idle Time in Seconds


• Excluded Domains
  
  


1. 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
  
  


1. 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."
2. 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."
3. Account Password Lifetime (days) - Self Explanatory


• Workspace Password Policy
  
  


1. Minimum Password Length - Self Explanatory
2. 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"
3. Must Contain At Least One Alphabetic Character - Self Explanatory
4. Must Contain At Least One Numeric Character - Self Explanatory
5. Must Contain At Least One Punctuation Character - Self Explanatory
6. Must Contain At Least One Upper Case Character - Self Explanatory
7. Must Contain At Least One Lower Case Character - Self Explanatory
8. Must Not Contain Username - Self Explanatory
9. Must Not Contain Workspace Name - Self Explanatory
10. Must Not Contain - colon seperated list of words that cannot be used in the password, default words are: "oracle:hello:welcome:guest:user:database"
11. Alphabetic Characters - default: "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
12. 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
  
  


1. 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.
2. Development Service URL - Used in conjunction with Request or Email Verification above.



• Email Provisioning
  
  



1. 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
  
  



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


• Email
  
  



1. SMTP Host Address - This is where you set up your internal mail server. All APEX mail calls will use this server by default.
2. SMTP Host Port - Self Explanatory
3. Administration Email Address - Self Explanatory
4. 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.

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.

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:

1. Downloading from OTN and configuring the embedded PL/SQL gateway (11g)
2. Downloading from OTN and configuring Oracle HTTP Server (9.2.0.3 -- 11gR2)
3. Installing from the database and configuring the embedded PL/SQL gateway (11g)
4. 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).

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.

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.

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.

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. ;)

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.

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 ], saysGoodbye 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 FAQsAccording 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.htmlToday 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.

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.

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.

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

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.