Oracle Database 11g | Create User |Check Instance Name| Host Name | Services | Parameters |

In this blog we will see how we can setup the Oracle Database user & check details related to service names, host name & parameters.


Create User Script Oracle 11g :

Connect session using sys/system or a user which has privilege to create user, then execute below commands.

CREATE USER <schema_name> IDENTIFIED BY <password>;
GRANT CONNECT, RESOURCE TO <schema_name>; 
GRANT CREATE SESSION TO <schema_name> ;
GRANT DBA TO <schema_name> ;
GRANT ALL PRIVILEGES TO <schema_name>;

I’m executing below commands using system user, also I’m granting DBA to my user.

CREATE USER DEV_SCHEMA IDENTIFIED BY welcome;
GRANT CONNECT, RESOURCE, DBA TO DEV_SCHEMA;
GRANT CREATE SESSION TO DEV_SCHEMA;
GRANT DBA TO DEV_SCHEMA;
GRANT ALL PRIVILEGES TO DEV_SCHEMA;

1

User created now you can configure your connection in SQL Developer:

Check the service name of your database using below SQL query:

select * from V$PARAMETER where name in ('instance_name','service_names');

2

Open command prompt & type below commad. It will give you details for hostname, port & services.

lsnrctl status

3

tnsping orcl

6

Configure connection in SQL Developer: Provide details of user, password, hostname, port & service name.

4


 

Check the schema name using SQL:

select SYS_CONTEXT('USERENV','SESSION_USER') from DUAL;
SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;

5

Check IP Address & Host:

SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;
SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;

Check NLS Parameters:  Example NLS_LENGTH_SEMANTICS, NLS_DATE_FORMAT

select * from V$NLS_PARAMETERS;

Some other V$ objects : Which will give you session, instance etc related details.

SELECT * FROM V$SERVICES;
SELECT * FROM V$PARAMETER;
SELECT * FROM V$SESSION;
SELECT * FROM V$INSTANCE;
SELECT * FROM V$DATABASE;

 



Thanks!

Happy Learning! Your feedback would be appreciated!