Monthly Archives: March 2009

Autoextend syntax

Posted on 6 March 2009 by Andrew Fraser

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 … Continue reading →

Posted in Space | Leave a comment

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

Posted on 5 March 2009 by Andrew Fraser

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 … Continue reading →

Posted in Scripts | Leave a comment

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

Posted on 5 March 2009 by Andrew Fraser

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 … Continue reading →

Posted in Bugs | 1 Comment

Application Express APEX datafile location for new schemas

Posted on 5 March 2009 by Andrew Fraser

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 … Continue reading →

Posted in Apex | Leave a comment

Using tar with compress or gzip

Posted on 5 March 2009 by Andrew Fraser

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 > … Continue reading →

Posted in Scripts | Leave a comment