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 and below, you are stuck with sizes in m, like the above.
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 this. (Note that this script includes the commands to move LOBs).
set pages 9999 lines 132
spool go.tmp
select 'alter table '||owner||'.'||table_name||' move tablespace ts_data2 ;'
from dba_tables
where tablespace_name = 'TS_DATA1'
order by 1
/
select 'alter index '||owner||'.'||index_name||' rebuild tablespace ts_data2 ;'
from dba_indexes
where tablespace_name = 'TS_DATA1'
and index_type != 'LOB'
order by 1
/
select 'alter table '||owner||'.'||table_name||' move tablespace ts_data2 lob ('||column_name||') store as (tablespace ts_data2) ;'
from dba_lobs
where tablespace_name = 'TS_DATA1'
order by 1
/
spool off
ed go.tmp
They can be moved back afterwards, once the original tablespace has been shrunk, if desired.
One thing to watch out for is user tablespace quotas during the moves. If you get errors like this:
ORA-01950: no privileges on tablespace 'TS_DATA2'
You either need to temporarily grant unlimited tablespace privilege to the user affected, or grant them a quota on the new tablespace.
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 are three workarounds to the problem:
1) create database links in both directions, local->remote and remote->local.
2) Specify “@database link name” when calling your user defined function, like this:
insert into mytable@remote (mycol) values ( myfunction@remote ('01-JAN-2009') ) ;
That requires that the function exist in the remote database.
3) Insert the data first into a local table, then copy that data over to the remote database, like this:
insert into mytable_local values ( myfunction ('01-JAN-2009') ) ;
insert into mytable@remote select * from mytable_local ;
delete mytable_local ;
And a fourth option, of pursuing a complete fix:
4) Raise this as a bug with Oracle Support and have them provide a complete fix, rather than resort to workarounds. This issue was actually logged as bug number 671775 way back in 1998, but was closed off on the grounds that there were easy workaround available.
Reference – Metalink Note 342320.1
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 location with the alter database command, e.g.:
SQL> alter tablespace FLOW_1 offline ;
SQL> host mv /ora1data/TEST/dbs/FLOW_1.dbf /apexdbfiles/FLOW_1.dbf
SQL> alter database datafile ‘/ora1data/TEST/dbs/FLOW_1.dbf’ rename ‘/apexdbfiles/FLOW_1.dbf’ ;
SQL> alter tablespace FLOW_1 online ;
Alternatively, for oracle managed files, set parameter db_create_file_dest.
Easier still – reuse schemas and even workspaces in APEX, don’t create new ones continually.
By default, workspace data files created in same directory as tablespace for APEX (FLOW_xxx.DBF)
Oracle Managed Files
Gives DBA complete control over file location
DB_CREATE_FILE_DEST init parameter
o1_mf_%t_%u_.dbf
/u03/oradata/marvlprd/MARVLPRD/datafile/o1_mf_flow_25_339bpw79_.dbf
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 than compress:
tar cvfp - file1 file2 file3 | gzip > tarfile.tar.gz gunzip -c tarfile.tar.gz | tar xvfp -
(Or, if you prefer, “gzip -cd” is the same as “gunzip -c”)
Gzip compresses down in size slightly better than compress, but tends to take slightly longer to do so.