Script to recreate user before import of data [message #233652] |
Fri, 27 April 2007 01:54 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Langberg
Messages: 5 Registered: April 2007 Location: Denmark
|
Junior Member |
|
|
Oracle 10G on Windows.
Anybody has a working script to extract DDL to be used to recreate user with rigth password, configuration, etc. - but without any objects.
I tried with "select dbms_metadata.get_ddl", but afterwards I keep getting an error saying that the password is wrong !
|
|
|
|
|
|
|
|
Re: Script to recreate user before import of data [message #233709 is a reply to message #233652] |
Fri, 27 April 2007 03:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Langberg
Messages: 5 Registered: April 2007 Location: Denmark
|
Junior Member |
|
|
Sorry folks - it seems to work anyway.
My mistake is that doing my testing - I used this method to "clone" a schema like this:
1. extract ddl with "select dbms_metadata.get_ddl ('USER','TESTUSERA') from dual;"
2. drop user 'testuserb'
3. create user 'testuserb' with adjusted DDL from 1.
4. Logon as testuserb/password
-> wrong userid or password
So the explanation must be that the "username" is used for encrypting the password - right ?
The method above works ok for dropping/creating a user.
|
|
|
|
Re: Script to recreate user before import of data [message #233744 is a reply to message #233652] |
Fri, 27 April 2007 04:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Langberg
Messages: 5 Registered: April 2007 Location: Denmark
|
Junior Member |
|
|
But anyway - does anybody has a proper script to extract all the necessary DDL to recreate the user without manually adjusting the generated script afterwards.
If I'm doing like following, it generates blank lines etc.
- set long 90000
- select dbms_metadata.get_ddl
('USER','username'),';' from dual;
- select dbms_metadata.get_granted_ddl
('ROLE_GRANT','username'),';' from dual;
- select dbms_metadata.get_granted_ddl
('SYSTEM_GRANT','username'),';' from dual;
|
|
|
Re: Script to recreate user before import of data [message #233745 is a reply to message #233744] |
Fri, 27 April 2007 04:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
There is no way to avoir blank lines (but how does it hurt?).
You can ask him to add the ";" (I removed some blank lines to save space.):
SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);
PL/SQL procedure successfully completed.
SQL> select dbms_metadata.get_ddl ('USER',user) from dual;
DBMS_METADATA.GET_DDL('USER',USER)
-----------------------------------------------------------------
CREATE USER "MICHEL" IDENTIFIED BY VALUES 'A16FD5BFC24EA1EC'
DEFAULT TABLESPACE "TS_D01"
TEMPORARY TABLESPACE "TEMP"
PROFILE "PFL_ORADBA";
1 row selected.
SQL> select dbms_metadata.get_granted_ddl ('ROLE_GRANT',user) from dual;
DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',USER)
-----------------------------------------------------------------
GRANT "SELECT_CATALOG_ROLE" TO "MICHEL";
1 row selected.
SQL> select dbms_metadata.get_granted_ddl ('SYSTEM_GRANT',user) from dual;
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',USER)
-----------------------------------------------------------------
GRANT SELECT ANY DICTIONARY TO "MICHEL";
GRANT CREATE TYPE TO "MICHEL";
GRANT CREATE TRIGGER TO "MICHEL";
GRANT CREATE PROCEDURE TO "MICHEL";
GRANT CREATE DATABASE LINK TO "MICHEL";
GRANT CREATE SEQUENCE TO "MICHEL";
GRANT CREATE VIEW TO "MICHEL";
GRANT CREATE SYNONYM TO "MICHEL";
GRANT SELECT ANY TABLE TO "MICHEL";
GRANT CREATE TABLE TO "MICHEL";
GRANT CREATE USER TO "MICHEL";
GRANT UNLIMITED TABLESPACE TO "MICHEL";
GRANT CREATE SESSION TO "MICHEL";
1 row selected.
Regards
Michel
|
|
|
|