In the beginning of data migration, we usually recreate some tablespaces to accommodate data from the source. The best way to do this is to generate tablespace DDL from the source database, then execute them in the target database.
There's somewhat different in this post, I intend to recreate all tablespaces with data files in target database as usual. But, I want data files to meet the following uniform requirements.
- Uniform Size: in initial, incremental, extendable and max size.
- Uniform Name: with sequential ordered.
Rationale
Why do we need uniform and small data files initially? What are advantages of doing this?
This is because most of data files in the source database are oversized and superfluous. Furthermore, some never been used data files reaches 32GB, the maximum size of a data file with 8k block. Apparently, the disk space is abused in the source database. This is totally unacceptable in the target database.
What I need is to recreate tablespaces in the target database with initially small and extendable data files which also have the enough capacity to accommodate the source data.
Solutions
1. Uniform Size
Therefore, I use the following query to generate DDL of tablespaces with uniform specifications in the SOURCE database.
SELECT 'CREATE TABLESPACE ' || tablespace_name || ' DATAFILE ' || LISTAGG('''' || file_name || ''' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED', ', ') WITHIN GROUP (ORDER BY file_name) || ';' "STMT" FROM DBA_DATA_FILES WHERE TABLESPACE_NAME NOT IN ('SYSTEM', 'SYSAUX', 'UNDO', 'UNDO1', 'UNDO2', 'UNDOTBS1', 'UNDOTBS2') GROUP BY tablespace_name;
In which, I use a group function called LISTAGG() to concatenate all necessary data files into a line within each tablespace.
The sample output looks like this:
CREATE TABLESPACE ERPAPPTBS1 DATAFILE '+DATA/ORCL/ERPAPPTBS1a.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, '+DATA/ORCL/ERPAPPTBS1a02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, '+DATA/ORCL/ERPAPPTBS1a3.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, '+DATA/ORCL/ERPAPPTBS1b.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, '+DATA/ORCL/ERPAPPTBS1b01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED, '+DATA/ORCL/ERPAPPTBS1b02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
A better format of the above DDL would be:
CREATE TABLESPACE ERPAPPTBS1 DATAFILE
'+DATA/ORCL/ERPAPPTBS1a.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATA/ORCL/ERPAPPTBS1a02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATA/ORCL/ERPAPPTBS1a3.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATA/ORCL/ERPAPPTBS1b.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATA/ORCL/ERPAPPTBS1b01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'+DATA/ORCL/ERPAPPTBS1b02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
That is to say, all data files in each tablespaces are the same initially. The only fly in the ointment is the file name, they were named in a casual way, which is also unacceptable to me.
So next, let's see how to rename all data files in an ordered format.
2. Uniform Name
Not only uniformed size, additionally, I want to rename all data files in a sequential form. So I use the following query to compose executable statements in the SOURCE database.
select case row_number() over (partition by tablespace_name order by file_id) when 1 then ';' || chr(10) || 'CREATE TABLESPACE ' || tablespace_name || ' DATAFILE' || chr(10) || ' ''+DATA/ORCLCDB/' || tablespace_name || '_01.dbf'' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED' else ',''+DATA/ORCLCDB/' || tablespace_name || '_' || to_char(row_number() over (partition by tablespace_name order by file_id), 'FM00') || '.dbf'' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED' end stmt from dba_data_files where tablespace_name not in ('SYSTEM', 'SYSAUX', 'UNDO', 'UNDO1', 'UNDO2', 'UNDOTBS1', 'UNDOTBS2') union all select ';' stmt from dual;
With the analytic function ROW_NUMBER(), we can order each data file of every tablespace.
The sample output looks like this:
;
CREATE TABLESPACE ERPAPPTBS1 DATAFILE
'+DATA/ORCL/ERPAPPTBS1_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
,'+DATA/ORCL/ERPAPPTBS1_02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
,'+DATA/ORCL/ERPAPPTBS1_03.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
,'+DATA/ORCL/ERPAPPTBS1_04.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
,'+DATA/ORCL/ERPAPPTBS1_05.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
,'+DATA/ORCL/ERPAPPTBS1_06.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
;
As you can see, we don't use the original data file name, instead, we renamed them with the following format.
In some situations, this kind of naming will prevent data files from potential name collisions, because their filename are guaranteed unique in our way.
Please note that, we don't change the number of data files of any tablespace. Because the target database should provide sufficient space to accommodate all segments migrated from the source database.