Configure and Install Oracle Application express for 11g database

Installing oracle application express

Usually oracle express will come pre installed in the oracle database

To check if it is installed you need to query dba_registry

select * from dba_registry where comp_id='APEX';

If it is installed note the version and all you need to do is configure it.

For configuring follow these steps

Go to the $ORACLE_HOME/apex directory.

sqlplus / as sysdba

@apxconf.sql

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.

Enter a password for the ADMIN user              []admin_password
Enter a port for the XDB HTTP listener [8080]
...changing HTTP Port

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

Check port usage using the following command.

select dbms_xdb.gethttpport from dual;

Usually the above gives 0. If thats the case run the below to change port to 8080

Enable Oracle XML DB HTTP server

EXEC DBMS_XDB.SETHTTPPORT(8080);
COMMIT;

This completes setup. You can login to admin console from following location.

http://host:port/apex
http://host:port/apex/apex_admin — Admin console


To upgrade Apex to latest version download from oracle support. In my case i am upgrading from version 3 to version 5.

apex_5.0.4_en.zip

unzip apex_5.0.4_en.zip

I unzipped the file to /opt/app/oracle


Sqlplus / as sysdba

Make sure anonymos, apex_public_user and APEX_030000 users are unlocked

Check default tablespace and temporary tablespace of the users which are usually sysaux and temporary


Once this is done, Navigate to unzipped folder and run the following

cd /opt/app/oracle/apex

sqlplus / as sysdba

@apexins.sql SYSAUX SYSAUX TEMP /i/


This takes some time to complete

After installation is complete you need to setup admin password. You need to do this again even if you did it earlier because user needs to be configured based on new version

@apxchpwd.sql

================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN]
User "ADMIN" exists.
Enter ADMIN's email [ADMIN]
Enter ADMIN's password []
Changed password of instance administrator ADMIN.


After this is done there is one more final step to load images

To load images give the directory without apex as below.

sqlplus / as sysdba

@apex_epg_config.sql /opt/app/oracle

This takes around 2 minutes.

This completes the upgrade. You may login using the web address. Sometimes you might need to restart db for the changes to take effect

[Off topic] How to calculate your lease payment and negotiate lease deal

First visit all the dealerships and test drive the vehicles you like. Just take basic quotes from the dealers and do not buy at that point.

Once you have driven all the cars narrow down based on what you like. After you know what car you liked the most start research on the car.

Hit to edmunds forums and go to the vehicle specific forums and see what others are paying. Also request money factor and residual value of the model you would like to buy and one of the moderator will post the values for you.

Now visit specific car related forums. In my case i am planning to get a 2016 Honda civic touring.

So i went to civicx forums to see what are the possible issues people are facing and see if there are any TSB's to fix the issue. Make sure you check these issues before you finalize your purchase.

In addition to that see what prices people are paying around your area as they have state specific forums in civicx for example.

Once you get an idea just go to truecar and get the price for your model. Just use this as reference.

Also in edmunds you can check the invoice price of the vehicle.

The sticker price for civic touring $27335 ( Including destination fee)

Invoice price is around $24600 + $835 destination fee

Keeping these in mind i want to target price less than invoice including destination for $24500

From here i started requesting quotes online from all dealerships around me in 30 mile radius.

After negotiations over the phone and emails i am left with prices ranging from 24100 to 24900

I send this 24100 quote i got from the dealer who is quite far from my place to nearest dealer to see if he can beat it and i said i will be coming in today for purchase.

Instantly i got a call saying to bring the printed quote and they will beat it by $50 bucks.

So i headed to the dealership and when i told them this is for a lease they said for lease they make calculations differently and some crap. Dont hesitate to walkout. They will call you back in most of the cases. They will try to comeup with some numbers and force you but be firm and ask them the money factor (multiply this value with 2400 to get interest rate) and residual value( this is the value finance company decides what the value of car at the end of 3 yr lease)

So For honda civic touring i was told residual is 57% and MF is 0.00070(only if credit score above 760)


Do the math dont panic and let dealer match the price. This is how you calculate

Residual value is always calculated on MSRP or sticker price

Since dealer said 57%

27335*(57/100) = 15580.95

So your car is worth 15580.95 at the end of the lease. If you want you can buy your car if you want for that price at the ened of lease.

Money factor i was told is 0.00070

multiply money factor by 2400 to get interest rate. This is just for your reference,

In this case it is 0.00070*2400=1.68%



For lease you pay the depreciation of the car and the interest

MSRP=27335
Negotiated price= 24050
Residual Value= 15580.95
Lease acquisition fee =$595

So Total price you negotiated= 24050+595=$24645


Lease acquisition fee is mandatory when you are leasing


So first calculate this if its for 36 month lease

Depreciation = (24645-15581)/36 = $251.77

Interest = (24645+15581)*0.00070 = $28.15

Add these two

$251.77 + $28.15 = $279.92 + tax

These are the monthly payments you need to pay for 36 months

Our tax rate is 8% so it is $22.39


So total including tax per month is $302.31 for 36 months including tax.

Dont panic and be clear and dont overpay.

In addition to this when you drive of you need to pay dealer, title and documentation fee. In California it is less than $400

Happy Leasing. Leave comments if you have any questions.

Basic procedures to give db level grants to users

Some times we need to grant a particular db user to kill sessions and for that we need to  grant alter system access which is not a best practice. So to over come we create a simple procedure to do the required actions and grant execute permissions to the user on that procedure.

Here are few examples..

Killing session procedure

CREATE OR REPLACE procedure kill_db_session
 ( v_sid number, v_serial number )  as
 v_varchar2 varchar2(100);
begin
execute immediate 'ALTER SYSTEM KILL SESSION '''   || v_sid || ',' || v_serial || ''' IMMEDIATE';
end;
/

Refresh materialized view under a different user

CREATE OR REPLACE PROCEDURE SSS.REFRESH_MV AS

BEGIN

    DBMS_MVIEW.REFRESH('xxx','C');

END REFRESH_MV;

/

Here xxx is tablename and C means complete refresh

Make sure you give grants on tables explicitly for mviews or else they will throw table not found error