Function to create metadata

Hello guys,

I'm lost. I'm searching the web (google), Oracle-books and dbasupport.com for this, but no result... I know it is on this site, but I still can't find it :confused:

The problem: I want to copy all users from one database, so I can introduce them into another db.

In DBA Studio (Oracle 8.1.7.4.9) you can choose "Show Object code" and will provide you with the correct create script.

I know there's some kind of DBMS_METADATA package that will return a create script. But I couldn't find it.

Can someone enlighten me?

Humble grasshopper hops around.... confused...

Can't you use export/import with ROWS=N?

MH

I think your looking for DBMS_METADATA.GET_DDL which was introduced in 9i

HTH

That's why I couldn't find it... Thanks Jovery !!

Mr. Hanky: well, that's worth a try. I can create a script that creates a batch file, that executes "imp user/passw@db fromuser=x touser=x rows=N" for each user...

Thank's !!

I have a loooooong list of users spooled in a text file.

Is there a way to make a delimited horizontal list out of this 'vertical' list of names?

Example:

Now:
JOHN
LISA
MARTIN

Result:
JOHN,LISA,MARTIN

Write and UTIL file.
step 1) Read from file1 one which data like...
JOHN
LISA
MARTIN
step 2) write to file2... add COMMA to the value from file1.

Just check UTIL_FILE package whether you can append (Iam nit sure).

HTH

Oke Thanks!

awk '{ a=a"," $0 } END { print substr(a, 2) }' your_text_file

Pando? What dialect are you talking?

Furthermore: I tried something, but now I see that many users are only created so they can access the application. The users have no objects, but are granted select/insert/etc rights to access the application-schema. Great!

Now I think to use to create a script from selecting DBA_USERS to create all those users at the other db.

Question :

How do I transport the password?

The following statement fails:
SELECT 'CREATE USER '||user_name||' IDENTIFIED BY '||password||';'
FROM DBA_USERS;

Is there a trick to create a users password by using the encrypted password in DBA_USERs?

you can get the password by one method, but before populating into that table, you need to expire the acc & when login the user will have to enter new password. then you can capture old & new pass in the table using a function and assinging a profile.

Quote:

---

Originally posted by efrijters
Pando? What dialect are you talking?

---

LOL :D :D :D

You never used awk? You dont know what you are missing boy....

I remember a trick of a Oracle teacher: he copied the encrypted password into the clipboard, changed the password and later on he updated the same field with the original password.

I think I got the solution now...

Thanks!

Quote:

---

Originally posted by guru_heaven
LOL :D :D :D

You never used awk? You dont know what you are missing boy....

---

AWK? Never heard of it... (bit of an AWKward name... awk...)

Okay, I'll let google search for it. Probably I'll have to study again.... Thanks a LOT! :D

Quote:

---

Originally posted by efrijters
Pando? What dialect are you talking?

Furthermore: I tried something, but now I see that many users are only created so they can access the application. The users have no objects, but are granted select/insert/etc rights to access the application-schema. Great!

Now I think to use to create a script from selecting DBA_USERS to create all those users at the other db.

Question :

How do I transport the password?

The following statement fails:
SELECT 'CREATE USER '||user_name||' IDENTIFIED BY '||password||';'
FROM DBA_USERS;

Is there a trick to create a users password by using the encrypted password in DBA_USERs?

---

I'm feeling generous:

spool r1_create_users.sql

select 'create user '||username||
decode(password,'EXTERNAL',' identified externally',
' identified by values '||''''||password||'''')||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||';'
from sys.dba_users
where username NOT IN ('ADAMS', 'BLAKE', 'CLARK', 'JONES', 'SCOTT',
'ANONYMOUS','AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
'CTXSYS', 'DBSNMP', 'LBACSYS', 'MDSYS', 'MTSSYS',
'ODM', 'ODM_MTR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS',
'OSE$HTTP$ADMIN', 'OUTLN', 'OAS_PUBLIC',
'HR', 'OE', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM',
'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS', 'SH',
'PERFSTAT', 'REPADMIN', 'RMAN',
'SYS', 'SYSTEM', 'TRACESVR', 'WEBSYS',
'WKPROXY', 'WKSYS', 'WMSYS', 'XDB')
order by username
/

spool off

The list of users you can edit as reuired, but its handy as it stops you getting the standard set of users etc.

just run the spool file against your new database and chocks away. Your users will still have their old passwords. You can use this script as a basis to do a similar thing with roles, and then grants.

Barry

Quote:

---

Originally posted by efrijters
Pando? What dialect are you talking?

---

:confused:

err it's a simple awk script which does what you want convert vertical text to horizontal right?

Quote:

---

Originally posted by bazza
I'm feeling generous:

spool r1_create_users.sql

select 'create user '||username||
decode(password,'EXTERNAL',' identified externally',
' identified by values '||''''||password||'''')||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||';'
from sys.dba_users
where username NOT IN ('ADAMS', 'BLAKE', 'CLARK', 'JONES', 'SCOTT',
'ANONYMOUS','AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
'CTXSYS', 'DBSNMP', 'LBACSYS', 'MDSYS', 'MTSSYS',
'ODM', 'ODM_MTR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS',
'OSE$HTTP$ADMIN', 'OUTLN', 'OAS_PUBLIC',
'HR', 'OE', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM',
'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS', 'SH',
'PERFSTAT', 'REPADMIN', 'RMAN',
'SYS', 'SYSTEM', 'TRACESVR', 'WEBSYS',
'WKPROXY', 'WKSYS', 'WMSYS', 'XDB')
order by username
/

spool off

The list of users you can edit as reuired, but its handy as it stops you getting the standard set of users etc.

just run the spool file against your new database and chocks away. Your users will still have their old passwords. You can use this script as a basis to do a similar thing with roles, and then grants.

Barry

---

Thanks Barry, you hit the bullseye!