Bloat Buster 1.2 and patch to crack

  • Questions
  • How To FULL DB EXPORT/IMPORT

You Asked

Hello Tom,
How to do a Full DB exp and Import.

I do say
exp system/manager@xyz FULL=Y FILE=FULL.DMP

Then If I want to do a full Import to a new freshly created DB which only has the default schemas sys , system , etc.

1) Please correct me :-
I would need to pre-create all schemas /tablespaces for those schemas needed to import the user and then do a FULL import.

imp system/manager@abc FULL=Y FILE=FULL.DMP

2)Cant i just

imp system/manager@abc FULL=Y FILE=FULL.DMP and the users and tablespaces will be automatically created due to FULL option ?

3)Is the use of FULL exp/imp just to gather all schema files into one big file.

4) Suppose I need to exp import say only 10 schemas. What is better peroformance wise, do a fromuser touser comma seperated list of owners
or individually run 10 exp/imp commands. Are smaller individual files easier to handle/read by Oracle exp/imp utility?

Thanx

and we said...

1) you would not need to pre-create anything if you don't want to.

2) yes

3) it is, well, used to get a full export. It gets not only schema's but "public" things too (eg: it gets public synonyms - a schema or object level export would not)

4) each time you run IMP, it reads the dmp file from start to finish. Hence if you run it 10 times, you will read that dmp file 10 times.

Either run IMP once OR export the 10 schemas to 10 separate files, and imp the 10 separate files. You might do the latter in order to "parallel process" with exp/imp

Regarding 4) each time you run IMP, it reads the dmp file from start to finish. Hence if you run it 10 times, you will read that dmp file 10 times. Either run IMP once OR export the 10 schemas to 10 separate files, and imp the 10 separate files. You might do the latter in order to "parallel process" with exp/imp Question :- Will the following be faster than the above point 4. Do a single file export of 10 schemas so we get just one file . But Run 10 imp commands with this big single file each time with just the required schema.So we parallelize it but with a BIG fat file consisting of 10 schemas of which only one schema is used in each imp command. This way I do single export but 10 imports Thanx

Followup   February 03, 2009 - 10:08 am UTC

do the math. think about it. what do you think would be faster:

10 processes reading concurrently from start to finish a COMMON file that is 10 times larger than they need (so each does 10 times the IO it should)

versus

10 processes reading concurrently from start to finish a PRIVATE file that is perfectly sized - has not one extra byte of data in it?

Hello, I never do full import, I prefer import only users I need. Is import full very safe ? On a fresh database (with sys, system, dbsnmp users, ...), I'm afraid that full import will add and modify important data. What about import full into a 9.2.0.8 with export file done in 9.2.0.4 for example ? FB

Followup   February 03, 2009 - 10:21 am UTC

if you start with a 'fresh' database, a full import is 'safe' and has been done by many thousands of people - in particular when they move across different platforms with old releases (before cross platform transports)
Hi Tom, you are the best and thanks a lot for sharing your knowledge. I'm trying to import a full dump exported in the UNIX platform to a Windows platform.I have created all tablesapces needed. But when I run the imp command the following error comes up for all tablespaces, IMP-00017: following statement failed with ORACLE error 12913: "CREATE TABLESPACE "CONFIG_TABLES01" BLOCKSIZE 4096 DATAFILE '/export/home/" "pa08/dbase/CONFIG_DATA/CONFIG_DATA01_pa08.dbf' SIZE 167874560 AUTOEXT" "END ON NEXT 53248 MAXSIZE 4000M EXTENT MANAGEMENT DICTIONARY DEFAULT NOCO" "MPRESS STORAGE(INITIAL 102400 NEXT 102400 MINEXTENTS 1 MAXEXTENTS 256 PCTI" "NCREASE 5) ONLINE PERMANENT " IMP-00003: ORACLE error 12913 encountered ORA-12913: Cannot create dictionary managed tablespace 1. Since my system tablespace is a locally managed one i'm unable to create a dictionary manged tablespace. 2. It seems it is trying to creat tablespaces in the same location as the source database. I can do a fromuser/touser imp and it works fine with my current tablespaces. But I need to import the entire database. Can you please advice if there are any limitations when doing a imp full=y and elaborate on that? Thanks in advance, Nadee

Followup   April 13, 2010 - 7:26 am UTC

1) correct
2) yes, that is what it is programmed to do (impdp - datapump - is more 'configurable' that way)

You will have to precreate your tablespaces where you want them, using the options you want to use.

Or, provide us a database instance that supports dictionary managed tablespaces and a file system that corresponds to the system you are coming from.

I'd go with the former option myself, use imp indexfile=foo.sql and you'll get a script with all of the creates - you can find the tablespaces and edit them as you see fit - run them and then import.

Please clarify. I hope to upgrade a 9i database from 32-bit Windows to 64 bit OS on faster new machine, target 10g database (that is all the app has been tested for) I have known good full export set oracle_sid=ORCL C:\oracle\ora92\bin\exp SYSTEM/ full=Y FILE=E:\EXPORTS\EXPORT.dmp log=e:\exports export.log CONSISTENT=Y I really do not want to precreate all users with rights to each table if I don't have to. I read I could pre-create the database on new system in 10g. My question regards 10g import parameters and DESTROY? If I do a import, what parameters are best, and should I use destroy if the 10g tablespaces have the same name as the 9i tablespaces? I read from an Oracle powerpoint presentation that in 11g I should precreate and fully patch the database, do an import FULL=Y. Does that work in 10g? Advice much appreciated.
Hi Tom, We need to use datapump to copy a full database from different platform (endian is different, tablespaces are in disorganized so we cannot use transportable tablespaces). Would you please explain the best way to do expdp/impdp into a 'fresh' database? Of course we only need the 'application' type schemas, but as you mentioned above, we need all the public 'SYS' type stuff (public synonyms, roles, etc.). What is the best way to do this? Especially without getting lots of errors saying object already exists (e.g. for SYS tables, etc.). Thanks, Robert.
Tom, When we do full import to newly created DB. whether the source ( from which dump was exported) characterset would be overwritten onto the target DB characterset? regards
Tom, further to above post. - Target database ( newly created) would already having the SYS and SYSTEM schema. - when we do IMP full=y .. it would attempt to create those schemas/objects... - at the same time .. we would like source databse dictionary get imported into target how to deal in such situation.. regards

Followup   May 07, 2012 - 5:50 am UTC

if the export is in character set X

and the database is built with character set Y

then upon import - X will be mapped into Y, converted in Y

that means that data will be altered, changed.

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch2charset.htm#i1006853
Thanks Tom Further, I have silly doubt regarding the FULL import. I have the scenario : source database : having schema A alongwith SYS, SYSTEM... and other default schema Target database ( newly created) : has SYS, SYSTEM and other default schema. Now a) In target , I have pre created all the tablespaces as in source. b) In target IMP full=Y... In step (b) above what would happen to the Objects ( owned by SYS, SYSTEM ... ) already existing in target databases? It might replaces them, skip them or append the underlying tables on SYS , SYSTEM schemas...? we chosen FULL=y because we wanted to Import schema A from source to target alongwith roles, privilges... etc and we want to make sure we wont miss anything in target. regards

Followup   May 08, 2012 - 11:20 am UTC

we don't export those things in a full export.
thanks Tom. But when I tested the same.. [oracle@hostA ]$ exp system/<pwd> full=Y file=full.dmp log=full.log I can see .. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) About to export the entire database ... . exporting tablespace definitions ... ..snip.. ... . about to export SYSTEM's tables via Conventional Path ... . . exporting table DEF$_AQCALL 0 rows exported . . exporting table DEF$_AQERROR 0 rows exported . . exporting table DEF$_CALLDEST 0 rows exported ..... ... SYSTEM's tables are exported. regards

Followup   May 10, 2012 - 3:14 am UTC

only the data that needs to be. SYS - not exported. things it needs to pick up (so you get jobs exported and the like) they are done 'correctly for you'

it does the right things.

watch that character set conversion - set your NLS_LANG before exporting - otherwise you've just lost all of your characters above ASCII 127

Many thanks Tom.
I exported SYS.ADU$ table using SYSTEM user. But when I tried to import it using SYSTEM user, I am getting error : Insufficient Privileages. Please help on this. I dont want to use SYS user while importing the tables into SYS schema.

Followup   June 15, 2012 - 7:56 am UTC

you cannot do that. You cannot write to SYS owned tables. You cannot write into SYS.AUD$
i want to export full from my db but i dont need some of data of tables.what should i do?

Followup   January 04, 2013 - 10:57 am UTC

then obviously - you do not want to export full from your database!

you don't give a version, in 10g and above you have the data pump and data pump can easily use exclude and include parameters to give filters for objects to skip or to include.

don't use exp/imp anymore, use data pump.

Hi Tom, I need to export and import the database. In New database, I have just changed the block size and increased length of some columns from existing schema. The reason to change the block size to allow more size of index so that I will get minimum 8000 bytes size to the index/key. I have to take backup of all indexes, schema , relationship .. Can you please provide the steps to me?
Hi Tom, I need to export and import the database. In New database, I have just changed the block size and increased length of some columns from existing schema. The reason to change the block size to allow more size of index so that I will get minimum 8000 bytes size to the index/key. I have to take backup of all indexes, schema , relationship .. Can you please provide the steps to me?
C:\Documents and Settings\Administrator>imp PAY/PAYPH2@ARCH file='D:\Dump\Tables1.dmp' ignore=y fromuser=PAYM touser=PAY; Import: Release 10.2.0.1.0 - Production on Thu Feb 20 12:17:04 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Export file created by EXPORT:V10.02.01 via conventional path import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set . importing PAYMGRPH2's objects into PAYMGRPH2; "ALTER SESSION SET CURRENT_SCHEMA= "PAYMGRPH2;"" IMP-00003: ORACLE error 1435 encountered ORA-01435: user does not exist IMP-00000: Import terminated unsuccessfully
Hi. I have created a user with sysdba prvis but wit this schema I cant imp full db. It throws lots error and also changes sys and system password atomatically. Could you pl help in this?
Hi Tom, I want to cleanup a database for a full import. What schemas should I leave in the database? Thank you for your help!

Followup   November 26, 2014 - 9:12 pm UTC

create new empty database with features you need. You don't want to "clean up" an existing database for something like this.

See

https://docs.oracle.com/cd/E11882_01/server.112/e23633/expimp.htm#UPGRD12564

to get started.

We are in the process of upgrading 10g databases to 12c. I took the dumpfile (full =y ) from 10g and did import as system to 12c. I ran utltp to compile all the sys objects. I currently have 440 objects in SYSMAN , OLAPSYS , PUBLIC. Not sure where I went wrong ? a) Should I do full import as sys b) Is the full import from 10g to 12c is supported ? c) Do we have to run any cleanyp script.
why datapump is faster than traditional import and export? i want indepth what is happening?
Set Screen Reader Mode On


723