AboutElliot Mak Expertise I can answer questions with regarding to Oracle DB (8i, 9i, 10g) installation, configuration, administration, Data Guard, and SQL.
Experience I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.
Expert: Elliot Mak Date: 6/3/2008 Subject: some dba questions
Question How does one use the import/export utilities?
Can one resize tablespaces and data files?
Can one monitor how fast a table is imported? Can one modify data as it loads into the database?
Answer Good day Shanthi
I assume that you are using Oracle 10g R2 or above.
Question #1:
How does one use the import/export utilities?
Answer:
Oracle introduces Data Pump (impdp/expdp) in Oracle 10g, it is a faster and more efficient way to perform an import/export. The old way (imp/exp) still works but rumor has that it will be deprecated very soon.
For this reason, I will provide you the Oracle Data Pump version of the import and export.
Question #2:
Can one resize tablespaces and data files?
Answer:
Here is an example
1. Create a temporary tablespace with 1 datafile of 100MB in size
CREATE SMALLFILE TABLESPACE "ALLEXPERT_TMP_TS" DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\allexpert_ts1' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
2. Add a new datafile with 50MB in size
ALTER TABLESPACE "ALLEXPERT_TMP_TS" ADD DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\allexpert_ts2' SIZE 50M
3. Drop a datafile
ALTER TABLESPACE "ALLEXPERT_TMP_TS" DROP DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ALLEXPERT_TS2'
4. Resize the first datafile from 100MB to 75MB
ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ALLEXPERT_TS1' RESIZE 75M
You cannot resize the tablespace to a smaller size if the block for those 25 MB are in use. You will need to do a reorg before you do a resize to a smaller datafile.
By controlling the size of the datafiles within the tablespace, you are effectively resizing the tablespaces.
Question #3:
Can one monitor how fast a table is imported? Can one modify data as it loads into the database?
Let's answer the first question "Can one monitor how fast a table is imported?"
For each import using imp/impdp, you can specify to output a log file. The logfile contains all the information about the import including start time and end time of the import, but it does not provide timing for each individual table.
The second question "Can one modify data as it loads into the database?"
I assume that you are using Oracle utilities.
If you are using imp/impdp, you cannot modify the data as it loads into the database.
The other way to get around it is to use SQLLOADER. SQLLOADER will allow you to modify the data content as it loads into the database. But SQLLOADER will only read flat files.
Hope it helps. If you have additional questions, please don't hesitate to contact me.
