ORACLE SQL Usages


By W.ZH.



1. How to create a user by SQL Plus?

We suppose you do not have user in DB yet. or you can use :

drop user user_ABC cascade;

 then create spaces for new user:

create tablespace ts_user_ABC datafile ‘D:\DAPSDB\ts_user_ABC.dbf’ size 100M;

create temporary tablespace temp_user_ABC tempfile ‘D:\DAPSDB\user_ABC_temp01.dbf’ size 100M;

Create User:

create user user_ABC identified by user_ABC account unlock default tablespace ts_user_ABC temporary tablespace temp_user_ABC;

alter user user_ABC quota unlimited on ts_user_ABC;

Give user some basic permission and roles:

grant connect to user_ABC;

grant create table to user_ABC;

grant create view to user_ABC;

grant create procedure to user_ABC;

grant create sequence to user_ABC;

grant EXP_FULL_DATABASE to user_ABC;

grant IMP_FULL_DATABASE to user_ABC;


If you do these from EM console, you also need to do these steps to create a new user.

check DB connections

conn user_ABC/password;

conn sysman/password as sysdba;



2. How to export and import data for a user?


1. First, go the command console (not in SQLplus) of the DB to be backuped. backup the user DB by export a user:

exp username/userpass FIlE=abcuserdata.dmp OWNER=username


    or sth like this:

exp userid=system/manager file=c:exportsmysid.dmp log=c:exportsmysid.log full=y


      then you can copy the dmp data file to the target DB that you want to import.


2. Go to your target DB create a user  with table spaces and the roles/permission etc. then you can import user’s data from dmp file:


imp username/userpass FULL=y FIlE=abcuserdata.dmp


    or use this:

imp sysman/password  FROMUSER=username1 TOUSER=username2 FILE=abcuserdata.dmp


3. Then should be ok, you can use SQLDeveloper to get the data.

 

Advertisements