The logic with 'A' and 'B' might be "hidden" behind a virtual column on which you could do the partitioning:
alter session set nls_date_format = 'yyyy-mm-dd';
drop table tq84_partitioned_table;
create table tq84_partitioned_table (
status varchar2(1) not null check (status in ('A', 'B')),
date_a date not null,
date_b date not null,
date_too_old date as
( case status
when 'A' then add_months(date_a, -7*12)
when 'B' then date_b
end
) virtual,
data varchar2(100)
)
partition by range (date_too_old)
(
partition p_before_2000_10 values less than (date '2000-10-01'),
partition p_before_2000_11 values less than (date '2000-11-01'),
partition p_before_2000_12 values less than (date '2000-12-01'),
--
partition p_before_2001_01 values less than (date '2001-01-01'),
partition p_before_2001_02 values less than (date '2001-02-01'),
partition p_before_2001_03 values less than (date '2001-03-01'),
partition p_before_2001_04 values less than (date '2001-04-01'),
partition p_before_2001_05 values less than (date '2001-05-01'),
partition p_before_2001_06 values less than (date '2001-06-01'),
-- and so on and so forth..
partition p_ values less than (maxvalue)
);
insert into tq84_partitioned_table (status, date_a, date_b, data) values
('B', date '2008-04-14', date '2000-05-17',
'B and 2000-05-17 is older than 10 yrs, must be deleted');
insert into tq84_partitioned_table (status, date_a, date_b, data) values
('B', date '1999-09-19', date '2004-02-12',
'B and 2004-02-12 is younger than 10 yrs, must be kept');
insert into tq84_partitioned_table (status, date_a, date_b, data) values
('A', date '2000-06-16', date '2010-01-01',
'A and 2000-06-16 is older than 3 yrs, must be deleted');
insert into tq84_partitioned_table (status, date_a, date_b, data) values
('A', date '2009-06-09', date '1999-08-28',
'A and 2009-06-09 is younger than 3 yrs, must be kept');
select * from tq84_partitioned_table order by date_too_old;
-- drop partitions older than 10 or 3 years, respectively:
alter table tq84_partitioned_table drop partition p_before_2000_10;
alter table tq84_partitioned_table drop partition p_before_2000_11;
alter table tq84_partitioned_table drop partition p2000_12;
select * from tq84_partitioned_table order by date_too_old;
You will have to examine how the question is worded. By default 11g will create all three tablespaces. You can however create a database with only SYSTEM and SYSAUX. Technically speaking you can upgrade pre-10g database and not add SYSAUX, or remove the SYSAUX tablespace after the database is created and therefore have a database with only a SYSTEM tablespace, but you would be ill advised to do so and I really don't think they would look for that in an answer.
From the 11.2 Administration Guide:
The SYSAUX tablespace is always created at database creation.
From another section of the Administration Guide:
An auto-extending undo tablespace named UNDOTBS1 is automatically
created when you create the database with Database Configuration
Assistant (DBCA)...
If no undo tablespace is available, then the
instance starts without an undo tablespace, and undo data is written
to the SYSTEM tablespace. You should avoid running in this mode.
From the 10.2 Concepts Guide:
...the SYSAUX tablespace is always created during database creation or
database upgrade...
During normal database operation, the Oracle database server does not
allow the SYSAUX tablespace to be dropped or renamed. Transportable
tablespaces for SYSAUX is not supported.
Note: If the SYSAUX tablespace is unavailable, such as due to a media
failure, then some database features might fail.
From the 10.1 SQL Reference:
You cannot drop the SYSTEM tablespace. You can drop the SYSAUX
tablespace only if you have the SYSDBA system privilege and you have
started the database in MIGRATE mode.
From the 10.1 Administrators Guide:
If the SYSAUX tablespace becomes unavailable, core database
functionality will remain operational. The database features that use
the SYSAUX tablespace could fail, or function with limited capability.
http://oradbpedia.com/wiki/The_SYSAUX_Tablespace has this take:
The SYSAUX tablespace is a new tablespace that is required in Oracle
Database 10g...
When you migrate to Oracle Database 10g, the SYSAUX tablespace needs
to be created as a part of that migration. This is done after mounting
the database under the new Oracle Database 10g database software. Once
you have mounted it, open the database in migrate mode with the
startup migrate command. After the database is open, you can create
the SYSAUX tablespace.
Note: The loss of the SYSAUX tablespace is not fatal to your database.
In our testing it appears that the only real impact is that certain
functionality related to the occupants of the SYSAUX tablespace is
lost.
Earlier versions did not have a SYSAUX tablespace:
SYSAUX is the name of the compulsory tablespace, introduced in Oracle
10g.
Best Answer
You could use Oracle Data Pump Export and unload data compressing the resulting dump file(s). Below is an example of unloading the tables
EMPLOYEES
andDEPARTMENTS
owned by the userHR
:The parameter file
hrexp.par
contains the following parameters:The dump file is created in the directory specified by the
directory
parameter. You will probably want this directory to be located on the NFS so that it's available both to source database host and the remote host. The Oracle database should have appropriate privileges to be able to read from/write to this directory, and the user which performs the export operation should have read/write access to the directory object:In the example, the value of parameter
content
instructs Data Pump Export to unload data only. Thecompression
parameter here specifies that the exported data should be compressed.And now the most important part concerning performance. Oracle Data Pump can use different methods during load and unload operations, and in your case Direct Path method may be preferable because of your CPU resource usage requirement. In order for Data Pump to be able to use this method, you should satisfy some requirements listed in the section "Situations in Which Direct Path Unload Is Not Used" of the Database Utilities Guide: