Thursday, November 5, 2009

(APEX) & the Embedded PL/SQL Gateway (EPG) in an 11G


After installing Oracle 11g, run the following to configure APEX
Run apxconf.sql from $ORACLE_HOME/apex
When prompted, enter the port for the Oracle XML DB HTTP server. The default port number is 8080.
Unlock the anonymous user
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

You should be able to log into apex as the admin user from a browser using -> http://machine.domain:port/apex

The machine is the DB host and the port is the one input during configure step.

If you get an error and can't log in, verify the EPG is up by running the following in your browser ->

http://machine.domain:port

If it's up, you should be prompted for a username and password for XDB.

If the EPG is not up, accomplish the following to start it:

1. Log in as SYS as SYSDBA
2. Run the following statement:
3. EXEC DBMS_XDB.SETHTTPPORT(port); ==>> Where port is the plsql gatway port.
4. COMMIT;

For example:
EXEC DBMS_XDB.SETHTTPPORT(8080);
COMMIT;

Monday, November 2, 2009

How to find size of LOB


Select b.table_name,b.Column_name,c.data_type,a.Segment_name,a."size"
from
(Select Segment_name , (bytes/(1024*1024*1024)) "size"
from User_Segments
where (bytes/(1024*1024*1024))>0.5 )a,
(Select Table_name,Column_name,Segment_name
from User_Lobs)b,
(Select table_name,Column_Name,Data_type from User_Tab_Columns
Where Data_Type in ('CLOB','BLOB','LONG','LONG RAW') ) c
Where a.segment_name=b.segment_name
and b.table_name=c.table_name
and b.column_name=c.column_name
Order by c.data_type
/