How to Create Database user and grant Privileges

How to Create Database user and grant Privileges

-- Create User via SYSDBA
CREATE USER testuser IDENTIFIED BY 123

-- Grant Connection Privilege via SYSDBA
GRANT CONNECT, DBA, RESOURCE TO testuser IDENTIFIED BY 123;

--Grant Select on HR Tables & Views --> Execute the Statements
SELECT 'GRANT SELECT ON ' || object_name || ' to testuser;'
  FROM dba_objects
 WHERE owner = 'HR' AND object_type IN ('TABLE', 'VIEW')

--Grant Execute on Packages --> Execute the Statements


SELECT 'GRANT EXECUTE  ON ' || object_name || ' to testuser;'
  FROM dba_objects
 WHERE object_name LIKE 'XXX%' AND object_type IN ('PACKAGE', 'PACKAGE BODY')

-- View Package Code

SELECT DBMS_METADATA.get_ddl ('PACKAGE', 'XX_HR_PACKAGE', 'APPS') FROM DUAL;

Note:
If one wants to avoid using the schema name in the beginning of any object in the custom schema, we need to create public synonyms as show below to avoid the same

E.g.: Select * from apps.per_all_people_f

SELECT    'CREATE OR REPLACE PUBLIC SYNONYM '
       || object_name
       || ' FOR '
       || object_name
       || ';'
  FROM dba_objects
 WHERE owner = 'HR' AND object_type = 'TABLE'

Once the Public Synonyms are create, one can use the objects without using the schema name.
E.g.: Select * from per_all_people_f

ORA-01102: cannot mount database in EXCLUSIVE mode

ORA-01102: cannot mount database in EXCLUSIVE mode


Issue:

After installing 11gR2, i faced issue of ORA-01102 cannot mount database in EXCLUSIVE mode when i tried to start the database.

ORA-01102: cannot mount database in EXCLUSIVE mode

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01102: cannot mount database in EXCLUSIVE mode
Here is how I resolved it:

>> Shutdown database

SQL> shutdown immediate;
ORA-01507: database not mounted

>> Find out all the processes belonging to the database still running

ps -ef | grep ora_ | grep $ORACLE_SID

>>  Kill all the processes related to SID only

[oracle@DEV dbs]$ kill -9 324531

>> Check that there are no more processes running

ps -ef | grep ora_ | grep $ORACLE_SID

>> Remove the lk<SID> file from $ORACLE_HOME/dbs location
----------------------------
[oracle@DEV dbs]$ cd $ORACLE_HOME


[oracle@DEV dbs]$ cd dbs/


[oracle@DEV dbs]$ ls
 hc_DEV.dat  init.ora  lkDEV  orapwDEV  spfileDEV.ora


[oracle@DEV dbs]$ rm lkDEV

>>  Start the database
-------------------
SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2213896 bytes
Variable Size             956303352 bytes
Database Buffers          687865856 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.

Total size of Database

Total size of Database

An oracle database consists of data files, redo log files, control files, temporary files. 
The size of the database actually means the total size of all these files.


SQL> select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB" from dual

OUTPUT
-----------------------------------------
Size in GB
615.947875976563
---------------------------------------

ORA-28002: the password will expire within 7 days

ORA-28002: the password will expire within 7 days

 
Cause: The user's account is about to about to expire and the password needs 
to be changed.
Action: Change the password or contact the database administrator.


Solutions:

1) Simply change the password to avoid it temporary

$ sqlplus scott/tiger
  Connected to:
  Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

  SQL> PASSWORD
  Changing password for SCOTT
  Old password:
  New password:
  Retype new password:
  Password changed


2) Set PASSWORD_LIFE_TIME of the profile assigned user to UNLIMITED 
   
   
 $ sqlplus scott/tiger

  ERROR:
  ORA-28002: the password will expire within 7 days

  Connected to:
  Oracle Database 11g Release 11.2.0.1.0 - 64bit Production   

  SQL> connect / as sysdba
  Connected.
  
  SQL> SELECT PROFILE FROM dba_users WHERE username = 'SCOTT';

  PROFILE
  ------------------------------
  DEFAULT

  SQL> SELECT  LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' 
  AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

  LIMIT
  ----------------------------------------
  60

  SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

  Profile altered.

  SQL> SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='SCOTT';

  ACCOUNT_STATUS
  --------------------------------
  EXPIRED(GRACE)

  SQL> connect scott/tiger
  Connected.
  
  SQL> PASSWORD
  Changing password for SCOTT
  Old password:
  New password:
  Retype new password:
  Password changed