| running sql plus worksheet as test user [message #148888] |
Mon, 28 November 2005 20:02  |
clyded
Messages: 2
Registered: November 2005
|
Junior Member |
|
|
hello,
i want to create a bunch of tables using a test user that has dba privileges. i want the tables to be in the test schema
when im in the worksheet, i connect ok
if i use the "sysdba" with my test user, the table is create in the sys schema
if i connect "normal" i get an invalid login/password error and it disconnects me.
i have found that if i change the create table to test.table1 it works
the problem that i have is the number of tables, there are like 100+ tables in the sql script. i would have to change that in every table name!
any suggestions?
thanks
|
|
|
|
 Re: running sql plus worksheet as test user [message #149231 is a reply to message #148888] |
Wed, 30 November 2005 12:36  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Here is how I do it:
'Create user oradba(or whatever you want to call it) identified by <pw>;'
As sys, grant create table and other things to your newly created user.
Then login as that user (like oradba above) and use the password in the create user statement.
Now you can create tables and grants. You will also want to create a synonym (create public symonym table1 for test.table1) for each table to advoid putting the 'test.' in front of each table. Give each table in your scema a different name. I suggest using the table name in the create synonym statement, but that is your call.
Don't put your tables in the system tablespace!
Keep data and indexes in seperate tablespaces.
Keep several copies of your controlfile, use seperate devices.
Put your redo and archivelog files on seperate devices from your data. Make sure your database is in archivelog mode if you ever want to recover it, and trust me, you will.
Name the tablespace to use in your 'create table... storage... ' statement.
Neil.
[Updated on: Wed, 30 November 2005 12:37] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
