Skip to content

Category Archives: Oracle

Selecting random rows

Wednesday, August 1, 2007

If you want to select random rows from a table, you can order the rows using the dbms_random.value function : SELECT COLUMN FROM TABLE ORDER BY dbms_random.VALUE To get 20 random rows from the table, we can add a condition on rownum. This selects the first 20 rows : SELECT COLUMN FROM ( SELECT COLUMN [...]

Also filed in | | Comments (0)

Recompiling database objects

Thursday, July 26, 2007

The DBMS_UTILITY package has a function called compile_schema that compiles all procedures, functions, packages, and triggers. EXEC DBMS_UTILITY.compile_schema(schema => ‘SCOTT’); You can also use the UTL_RECOMP package, but I haven’t tried that one yet. With thanks to this ORACLE-BASE article.

Also filed in | | Comments (0)

Statspack summary

Wednesday, July 4, 2007

This post is a summary of the Oracle Statspack Survival Guide. To install statspack : Create tablespace for it @?/rdbms/admin/spcreate (run as sysdba) To take snaps : exec statspack.snap; To create the report : @?/rdbms/admin/spreport.sql

Also filed in | | Comments (0)

How much space does an Oracle table take up ?

Tuesday, June 19, 2007

The following queries can be used to find out how much space a table takes up. The first query gets the block size used in this database. The second one shows how many blocks are used for a table. SELECT VALUE FROM v$parameter WHERE name = ‘db_block_size’;   SELECT blocks, last_analyzed FROM all_tables WHERE owner [...]

Also filed in | | Comments (0)