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 PerformanceNote 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 syntaxAttempting 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 valueIf 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 LinksHow 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 schemas1) 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 gzipThe Value Added Tax rate we invoice at drops from 17.5% to 15% on 1st December 2008. If, like me, you are on the small business flat rate VAT scheme for IT consultants, then the rate we pay VAT out to HM Revenue and Customs drops from 13% to 11.5% at the same time. Full […]
Read More VAT flat rate scheme 11.5% rather than 13% from 1st December 2008Adding more memory to PCs is a cheap and easy upgrade method. But there are different types of PC memory modules available – SDRAM, DDR, DDR2 – and computers are limited to being compatible with one type only. So you have to know which type you want before buying, especially if buying online. 1) To […]
Read More Which PC Memory Module type (SDRAM, DDR, or DDR2) do I need?I used the following to install dbms_profiler, using a central set of system owned tables rather than each user having its own tables: conn / as sysdba @?/rdbms/admin/profload.sql conn system/password alter user system default tablespace users ; — [or any other reasonable tablespace] @?/rdbms/admin/proftab.sql GRANT all ON plsql_profiler_runnumber TO PUBLIC; GRANT all ON plsql_profiler_data TO […]
Read More Installing dbms_profiler