Use Foreign Key constraints with “on delete cascade” option for fast automated delete of child records

Here is a demo example. 1) Set up example tables: CREATE TABLE parent ( parent_id number(10) not null, CONSTRAINT parent_pk PRIMARY KEY (parent_id) ); CREATE TABLE child ( child_id        numeric(10)     not null, parent_id       numeric(10)     not null, CONSTRAINT fk_child FOREIGN KEY (parent_id) REFERENCES parent(parent_id) ); insert into parent values (1) ; insert into child values (1,1) […]

Read More Use Foreign Key constraints with “on delete cascade” option for fast automated delete of child records
March 13, 2009
Posted in Performance tuning

extent management local autoallocate sizes

Here is the algorithim for calculating next extent sizes with a system managed/autoallocate exten management local tablespace. The dependency is with the current segment size. Segment Size Next Extent Size less than 1m 64k 1m to 63m 1m 64m to 1023m 8m 1g or more 64m That is for 10gR2. Note that the above table […]

Read More extent management local autoallocate sizes
March 13, 2009
Posted in Space

Cache LOBs for Better Performance

LOBs generally(*) perform better if cached – that is, stored in the database buffer cache. However, that is not switched on by default. To change a lob to be cached: alter table mytable modify lob (mycolumn) (cache) ; To set it back to being uncached: alter table mytable modify lob (mycolumn) (nocache) ; And to […]

Read More Cache LOBs for Better Performance
March 6, 2009
Posted in Performance tuning

Autoextend syntax

Note the order of maxsize and next, has to be specified in that order, a little annoyingly: alter database datafile ‘FILENAME’ autoextend on next 1024m maxsize 3072m ; alter tablespace TSNAME add datafile ‘FILENAME’ size 1024m autoextend on next 1024m maxsize 3072m ; From 10gR1 and above you can specify sizes in g. With 9i […]

Read More Autoextend syntax
March 6, 2009
Posted in Space

Move script for ORA-03297: file contains used data beyond requested RESIZE value

Attempting to shrink files which have a lot of empty space can fail with this error: SQL> alter database datafile ‘/ora1data/TEST/TEST_ts_data1_f1.dbf’ resize 2g ; ORA-03297: file contains used data beyond requested RESIZE value Provided you can get an outage, one fix for this is to temporarily move segments to another tablespace using a scripts like […]

Read More Move script for ORA-03297: file contains used data beyond requested RESIZE value
March 5, 2009
Posted in Scripts

Bug (ora-2019 or ora-2069) with User Defined Functions Insert/Update over Database Links

If you try to insert or update across a database link some values called by a user defined function: insert into mytable@remote (mycol) values ( myfunction (’01-JAN-2009′) ) ; That will fail with either: ORA-02019: connection description for remote database not found or ORA-02069: global_names parameter must be set to TRUE for this operation There […]

Read More Bug (ora-2019 or ora-2069) with User Defined Functions Insert/Update over Database Links
March 5, 2009
Posted in Bugs
3 Comments

Application Express APEX datafile location for new schemas

How to change apex datafile location for new schemas? Just change the datafile location for the flow tablespace – because the new ones all create in that same directory. For example, in this database that is /ora1data/TEST/dbs/ : SQL> select file_name from dba_data_files where tablespace_name like ‘FLOW%’; FILE_NAME ——————————————————————————– /ora1data/TEST/dbs/FLOW_1.dbf You can change the datafile […]

Read More Application Express APEX datafile location for new schemas
March 5, 2009
Posted in Apex

Using tar with compress or gzip

1) To tar files up into a tarball: tar cvfp – file1 file2 file3 > tarfile.tar And to untar: tar xvfp tarfile.tar 2) Now, same thing but with compress added in: tar cvfp – file1 file2 file3 | compress > tarfile.tar.Z zcat tarfile.tar.Z | tar xvfp – 3) And, the same thing using gzip rather […]

Read More Using tar with compress or gzip
March 5, 2009
Posted in Scripts