Tuesday 27 March 2007

Oracle Apex Install

Cool and straightforward tutotrial to use when installing apex installation.

There is a better version of this technology nowadays and will appear in Oracle 11g I hear. It is called PL/SQL Gateway. It is like everything you need to build and host a web application will be in Oracle! Very nice, I like the idea, so is it bye bye application servers , bye bye web servers? Hello mighty Oracle! Well it seems like the future where we will be able to write that valuable piece of SQL code under an umbrella on a beach somewhere in Barcelona is near.

Oracle 11g comes with Apex preinstalled when you install it, so you want have to do this.

http://www.oracle.com/technology/obe/obe10gdb/install/htmldbinst/htmldbinst.htm



Home made (DIY) quick installation:

1. Download apex.

2. Unzip apex in your chosen directory

3. Switch to apex directory after unzip.

4. From the apex directory, start SQL*Plus and connect the database as SYSDBA.

$ \apex\sqlplus sys/SYS_password as sysdba

5. Run

SQL>@apexins password tablespace_apex tablespace_files tablespace_temp images

Where:

password: is the password you choose for the apex admin account / owner
tablespace_apex: the name of the tablespace for the Apex user.
tablespace_files: the name of the tablespace for the Apex Files user.
tablespace_temp: the name of the temporary tablespace
images: virtual directory for the images on the webserver kind of thing, define it to be /i/
This is how I run it, after creating a tablespace APEX.

SQL>@apexins kubilay APEX APEX TEMP /i/

If for some reason you fail you can remove it like this and try again

$ \apex\sqlplus sys/SYS_password as sysdba

Execute the following commands
SQL> drop user FLOWS_030000 CASCADE;

SQL> drop user FLOWS_FILES cascade;

Visit my Oracle Apex mock applications


Saturday 24 March 2007

Get DDL with dbms_metadata

Did you ever wanted to get the Data Definition Language of an object in Oracle from SQL*Plus.


There is package called DBMS_METADATA which you can use and it gives you exactly that. Use the package to get the DDL in SQL*Plus by passing as first parameter the object type (i.e. TABLE, INDEX…) and second parameter the object name.

SQL> set long 50000

SQL> select dbms_metadata.get_ddl( 'TABLE', 'MY_TABLE' ) from dual;


Wednesday 21 March 2007

Monitor Dynamic SGA components

Use this SQL script when logged in as SYSDBA to see what happens to the SGA components over time, how they grow and shrink

I found it very useful monitoring the SHARED_POOL usage over time.


SELECT COMPONENT ' - ' OPER_TYPE COMPONENT_STATUS,INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE,STATUS,TO_CHAR(START_TIME, ‘dd-mon-yyyy hh24:mi’) START_TIME,END_TIMEFROM V$SGA_RESIZE_OPSORDER BY END_TIME;

Wednesday 7 March 2007

SPFILE or PFILE startup

I have found this very useful SQL to identify what type of initialization parameter file an oracle instance has started with.



Found this on Thomas Kyte’s site at http://asktom.oracle.com most useful information as ever!

Run the following as SYSDBA.

SELECT DECODE(COUNT(*), 1, 'spfile', 'pfile')
FROM V$SPPARAMETER
WHERE ROWNUM = 1
AND ISSPECIFIED = ‘TRUE’



Install Oracle 10g R2 on Ubuntu (Breezy Badger and Dapper Drake)

I have found http://www.dizwel.com website very helpful when I was trying to install Oracle 10g R2 on Ubuntu.

The instructions are clear end well tested.I have used it couple of time to install Oracle on Ubuntu.


I strongly recommend it.Step by step installation guide of Oracle 10g R2 on Ubuntu (Breezy Badger and Dapper Drake)


Great website :http://www.dizwell.com/prod/node/52?page=0%2C0