Oracle PL/SQL: Time Difference
The following script show how to calculate the time difference between 2 execution points:declare thediff number; TIME1 timestamp; TIME2 timestamp; i number;begin TIME1 := SYSDATE; for i in 1..1000000000 loop …
Oracle PL/SQL: Pagination methods
The official way to achieve pagination in Oracle DB is not very elegant and very very slow. The reasons why it is slow are 2:The order by command must be…
Oracle DB: Shrink space taken by table
You can execute the following statements in the given order in order to shrink the data taken by your tables:alter table APPS.XXACCOUNT_BALANCES enable row movement;alter table APPS.XXACCOUNT_BALANCES shrink space COMPACT; …
Oracle DB: Gather statistics – Show statistics
Gather schema statsistics:exec dbms_stats.gather_schema_stats( - ownname => 'SCOTT', - options => 'GATHER AUTO' );Show schema stats:select * from DBA_IND_STATISTICS WHERE OWNER='SCOTT'select * from DBA_TAB_STATISTICS WHERE OWNER='SCOTT'select * from DBA_TAB_COL_STATISTICS WHERE…
Oracle DB: Install APEX 4.2
Here is how to install APEX 4.2 on Oracle 11g:Download Apex 4.0.2:http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.htmlin C:\apps\ApexDownload Apex Listener:http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.htmlin C:\apps\Apex_ListenerMake a shortcut to SQL Plus 11:C:\Oracle\product\11.2.0\dbhome_1\BIN\sqlplus.exe /nolognamed: Apex SQL Plus 11 wirh working directory…
Oracle DB: Create new undo tablespace
If you have the following message:ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'Then the undo tablespace is possibly corrupted.Try the following:create undo tablespace UNDOTBS2 datafile 'c:\app\vag\oradata\ora11g\UNDOTBS02.DBF' size 100000;alter…
Oracle PL/SQ-Java: Read from SYS_REFCURSOR
Please see:Java-PL/SQL: Read from SYS_REFCURSOR
Java-PL/SQL: Read from Oracle SYS_REFCURSOR
Here is a piece of java code, which fetches data from SYS_REFCURSOR, using the OracleTypes.CURSOR argument type:import java.sql.*;import java.util.Date;import oracle.jdbc.OracleTypes;public class ListCountries { static Driver driver; static Connection conn =…
Oracle PL/SQL: Pipeline function example
With pipeline functions we can "select" from a function as if it was a table, by fetching results as soon as they are prepared by the function, without waiting all…