I'm about to upgrade an OracleDB and server.
Current:
DB - Oracle 11.2.0.3
OS - redhat 6
Total Datafile size ~8.5TB
used space ~ 2.5TB
I have only 1 schema to export so I'm going to use schema export/import method. Problem is my recource limitation. In new server I can allocate only 3.5TB disk space for OracleDB. Question is does schema export/imort shrink unused space in order to fit data into my limited space. If not is there any other approch.
Thanks.
Yes, you can rearrange your files and reclaim unused space by using export/import.
For further more details about Reclaim Unused Space, visit the link mentioned below:
https://www.support.dbagenesis.com/post/reclaim-unused-space-in-oracle
Thank you @Piyush Ranjan It works.
For those who have the same task don't repeat my mistake and never go through this method without reading the following MOSC note Doc ID 2308198.1 if you have referencing constrants on big tables. It says:
To decrease the import time, take into account the following alternatives:
1/ Change the constraint type to "ENABLE NOVALIDATE" in source database before running the export. - or - 2/ Disable the constraints in source database and perform the export and the import afterwards. The constraints can be re-enabled later in source and target database with validate or novalidate option.
- or - 3/ Split the import job in three phases as follows: phase 1: > impdp EXCLUDE=CONSTRAINT full=yes phase 2: > impdp INCLUDE=CONSTRAINT SQLFILE=constraints.sql phase 3: > Get the definitions from constraints.sql and modify ENABLE with DISABLE and create the constraints this way from SQL*Plus and next execute: - Enable novalidate state of constraints - Enable state in SQL*Plus. Note: If any performance issue is seen in target database after phase 3, VALIDATE all constraints then gathered database statistics. z
Forgot one more important point:
Don't export as sysdba user, it also might cause some issues during import.