User creation Oracle

Background

I was involved in transfer from one machine to another in oracle. Initially I have never done this kind of data transfer kind of things. I searched for this one on the google, and It started showing lots of different example and stuffs where we have to unlock the SCOTT user which is also a default user in oracle as system and sys, grant privileges to it etc. In my case, those things may already have been taken care of.

There are mainly two paths you could follow to replicate the same schema to another.

• Transfer through file dump
• Transfer through SQL/PL-SQL using dblinks

Now there are some advantages and disadvantages of those two methods.

• The PL-SQL methods is easier for the implementation as it is quiet straight forward, but not everything is transferred by this method. You may transfer the tables, stored procedures, but there are many objects in the schema and it is hard to transfer all of them. For example: It is most unlikely that you will replicate the users with same usernames and passwords, and you may skip the privileges that they were granted in the source schema.
• The file dump method is slight tedious. You have to first create a dump file for the schema. The size of the dump may be much larger than the current size of the schema itself because the history data are also stored in the schema unless you forced it to re-size it. You may use the compress all option to create the compressed version of the dump. You then transfer it to the target machine through FTPor SCP or others. Then you have to import the file to the database. Its main advantage is that everything in the schema is transferred. The users, privileges, and everything else.

Create a dump of the schema that you want to transfer(server1)

expdp <schemauser>/<password> schemas=<schemaname> dumpfile=<dump>.dmp DIRECTORY=<DUMP_DIR>

Note:

• expdp is a os level command for oracle that gets installed on the user or group where oracle is installed.
• The DIRECTORY object should already have been created in oracle pointing to a location, and the dump file will be created in the location.(Create or replace directory <Directory_name> as '/orcl/data/dumps')

Transfer the dump to server2(server1)

Now we need to transfer the dump created above to server2 where we could actually import it.

This could be done by scp or ftp commands. I am going to use the scp command.

scp <src> <dst>

scp abc.txt user@<host>:/location/of/remote/directory

scp user@<host>:/location/of/remote/file  /path/to/local/directory

Import the dump file to a schema(server2)

To import the dump file just transferred to server2, we could use the following command.

impdp <username>/<password> schema=<schema_name> dumpfile=<filename>.dmp DIRECTORY=<DUMP_DIRECTORY>  

NOTE:

• The schema <schema_name> on which we are trying to import the dump, should already be created.
• if the schema names etc were different then the source schema names, remap commands should be used.
• The schema/user should have appropriate privileges.

Create an schema passing the location where to store the .dbf file(server2)

The dbf file is where the actual informations and metadata regarding the schema are stored. First login to the server using putty(SSH) and hit the following command on the terminal.

CREATE bigfile tablespace tablespacename
 datafile '/orcl/data1/oradata/orcl/tablespacename.dbf'
 size 100M
 autoextend ON NEXT 100M maxsize unlimited
 SEGMENT SPACE MANAGEMENT AUTO
 NOLOGGING;

Create a user, with that tablespace name as the default tablespace(server2)

CREATE USER username IDENTIFIED BY oracle DEFAULT TABLESPACE tablespacename

Grant create session to the user(server2)

GRANT create session TO username;

Now you may also need knowledge about the deletion of users in case your first try did not go so well as mine. Well here's how you do it.

How to drop a user including everything of the user(server2)

 DROP USER XXXXXXXX cascade;

The cascade word will delete everything related to the user including its privileges etc.

Drop the tablespace all everything of it(server2)

DROP TABLESPACE XXXXXXXXXXX INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS