 Exporting User as well as DDL [message #171419] |
Tue, 09 May 2006 20:18  |
DataSheet
Messages: 1
Registered: May 2006
Location: USA
|
Junior Member |
|
|
Hi there,
Is it possible to export User along with data. I have 5 tables which belong to user DEVELOPER. I want to EXP this user with 5 tables.
When I run the IMP, it errors out saying USER does not exist.
I use like USERID=SYSTEM/MANAGER FULL=Y or I tried also with FROMUSER=DEVELOPER , TOUSER=DEVELOPER
I can get away from this error by pre-creating DEVELOPER user but what if I have 20 users with their data.
Is it possible that IMP will create user first and then import the data?
I am using Oracle 9i on Windows.
Thanks,
Data Sheet
|
|
|
|
|
|
| Re: Exporting User as well as DDL [message #171611 is a reply to message #171419] |
Wed, 10 May 2006 09:46  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Import will create the user, provided
1. You have done the full export
2. You have the same directory structure. Becuase first tablespaces are crated.
then, users are created with default tablespace allocation.
If any of above fails, user creation fails.
Why not you test yourself?
first
then import with show=y
imp scott/tiger full=y show=y log=somefile.log
...
..
...
Import terminated successfully without warnings.
Now check the logfile
14 "CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 8192 TEMPFILE '/u01/app/oracl"
15 "e/oradata/mutation/temp01.dbf' SIZE 48234496 AUTOEXTEND ON NEXT 65536"
16 "0 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576"
17 "CREATE TABLESPACE "TOOLS" BLOCKSIZE 8192 DATAFILE '/u01/app/oracle/oradata"
18 "/mutation/tools01.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 327680 MAXSI"
19 "ZE 32767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGIN"
20 "G SEGMENT SPACE MANAGEMENT AUTO"
21 "CREATE TABLESPACE "USERS" BLOCKSIZE 8192 DATAFILE '/u01/app/oracle/oradata"
22 "/mutation/users01.dbf' SIZE 720896000 AUTOEXTEND ON NEXT 1310720 MAX"
23 "SIZE 32767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGG"
24 "ING SEGMENT SPACE MANAGEMENT AUTO"
25 "CREATE PROFILE "RESTRICT_USER" LIMIT COMPOSITE_LIMIT DEFAULT SESSIONS_PER_U"
26 "SER DEFAULT CPU_PER_SESSION DEFAULT CPU_PER_CALL DEFAULT LOGICAL_READS_PER_"
27 "SESSION DEFAULT LOGICAL_READS_PER_CALL DEFAULT IDLE_TIME DEFAULT CONNECT_TI"
28 "ME DEFAULT PRIVATE_SGA DEFAULT FAILED_LOGIN_ATTEMPTS DEFAULT PASSWORD_LIFE_"
29 "TIME DEFAULT PASSWORD_REUSE_TIME DEFAULT PASSWORD_REUSE_MAX DEFAULT PASSWOR"
30 "D_LOCK_TIME DEFAULT PASSWORD_GRACE_TIME DEFAULT"
31 "ALTER SESSION SET CURRENT_SCHEMA= "SYSTEM""
32 "BEGIN SYS.DBMS_PSWMG_IMPORT.IMPORT_PSW_VERIFY_FN(' RESTRICT_USER ', ' CHE"
33 "CK_NEW_PASSWORD ', ' (username VARCHAR2,"
34 " password VARCHAR2,"
35 " old_password VARCHAR2) RETURN boolean IS"
36 "BEGIN"
37 "if length(password) < 4 then"
38 " raise_application_error(-20001, ''password policy: Password should be grea"
39 "ter than 4 characters'') ;"
40 "END if;"
41 "end;'); END;"
42 "ALTER PROFILE "RESTRICT_USER" LIMIT PASSWORD_VERIFY_FUNCTION "CHECK_NEW_PAS"
43 "SWORD""
44 "ALTER USER "SYS" IDENTIFIED BY VALUES '4DE42795E66117AE' TEMPORARY TABLESPA"
45 "CE "TEMP""
46 "ALTER USER "SYSTEM" IDENTIFIED BY VALUES '1C4DEB81D4E4B2B4' TEMPORARY TABLE"
47 "SPACE "TEMP""
48 "CREATE USER "OUTLN" IDENTIFIED BY VALUES '4A3BA55E08595C81' TEMPORARY TABLE"
49 "SPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK"
50 "CREATE USER "DBSNMP" IDENTIFIED BY VALUES 'E066D214D5421CCC' TEMPORARY TABL"
51 "ESPACE "TEMP""
52 "CREATE USER "WMSYS" IDENTIFIED BY VALUES '7C9BA362F8314299' TEMPORARY TABLE"
53 "SPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK"
54 "CREATE USER "DBADMIN" IDENTIFIED BY VALUES '1177FD6FD635EB27' TEMPORARY TAB"
55 "LESPACE "TEMP""
56 "CREATE USER "TEST2" IDENTIFIED BY VALUES 'B3D53232BA2B2059' TEMPORARY TABLE"
57 "SPACE "TEMP""
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
