Oracle Accounts

Create new profile P_UNLIMIT

This profile P_UNLIMIT has all value set to unlimit and will temporary be used for account managements.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


CREATE PROFILE "P_UNLIMIT"
LIMIT
    COMPOSITE_LIMIT UNLIMITED
    SESSIONS_PER_USER UNLIMITED
    CPU_PER_SESSION UNLIMITED
    CPU_PER_CALL UNLIMITED
    LOGICAL_READS_PER_SESSION UNLIMITED
    LOGICAL_READS_PER_CALL UNLIMITED
    IDLE_TIME UNLIMITED
    CONNECT_TIME UNLIMITED
    PRIVATE_SGA UNLIMITED
    FAILED_LOGIN_ATTEMPTS UNLIMITED
    PASSWORD_LIFE_TIME UNLIMITED
    PASSWORD_REUSE_TIME UNLIMITED
    PASSWORD_REUSE_MAX UNLIMITED
    PASSWORD_VERIFY_FUNCTION NULL
    PASSWORD_LOCK_TIME UNLIMITED
    PASSWORD_GRACE_TIME UNLIMITED ;

Unlock account oradba

Unlock account oradba and set profile to P_ADMIN.

1
2
3
4


alter user oradba identified by "Sky_Wa1ker" 
 profile P_ADMIN account unlock ;

grant sysdba to oradba container = ALL;

Create new user oradba in CDB

Set profile to P_ADMIN and grant CREATE SESSION, SYSDBA.

1
2
3
4
5
6
7
8


CREATE USER ORADBA
 PROFILE "P_ADMIN" 
 IDENTIFIED BY "Sky_Wa1ker"
 ACCOUNT UNLOCK CONTAINER=ALL ;

GRANT CREATE SESSION TO ORADBA CONTAINER=ALL ;
GRANT SYSDBA TO ORADBA WITH ADMIN OPTION CONTAINER=ALL ;
ALTER USER ORADBA SET CONTAINER_DATA=DEFAULT CONTAINER=CURRENT ;

Create new user oradba in NonCDB

Set profile to P_ADMIN and grant CONNECT, CREATE SESSION, SYSDBA and UNLIMITED TABLESPACE.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


CREATE USER ORADBA
 IDENTIFIED BY "Sky_Wa1ker"
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK;
 
GRANT CONNECT TO ORADBA;
GRANT CREATE SESSION TO ORADBA;
GRANT DBA TO ORADBA;
GRANT SYSDBA TO ORADBA;

ALTER USER ORADBA DEFAULT ROLE ALL;
GRANT UNLIMITED TABLESPACE TO ORADBA;
ALTER USER ORADBA PROFILE P_ADMIN;

Set new password for user ORADBA with profile P_ADMIN

To be able to change password for user ORADBA the profile will be temporary change to P_UNLIMIT and back to P_ADMIN.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- Change profile from ADMIN_USER to P_UNLIMIT
alter user oradba profile P_UNLIMIT;

-- Unlock account
alter user oradba account unlock;

-- Set new password
alter user oradba identified by "&Password" account unlock;

-- Set back profile to P_ADMIN
alter user oradba profile P_ADMIN;

Set account user and password

1

alter user &username identified by "&passwd" account unlock;

Create New PDB User USR01

This is an exampel of create new user USR01 with own tablespace USR01_TS for test. Using server srvdb01, CDB CDB01 and PDB PDB01.

Connect to PDB PDB01

1
2


sqlplus / as sysdba
alter session set container = PDB01;

Check existing tablespace and datafiles

Purpose is to find datafiles storage location before creating the new tablespace. First check if init parameter is set for db_create_file_dest.

1
2
3


SELECT name, value 
FROM v$parameter 
WHERE name IN ('db_create_file_dest', 'db_create_online_log_dest_1', 'db_recovery_file_dest');

If parameter db_create_file_dest is not set use SQL query below to check other tablespace and the datafiles location.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


set line 160 pagesize 999 tab off

col "Tablespace" format a15
col "Filename" format a60

select
       TABLESPACE_NAME "Tablespace",
       STATUS "Status", 
       BYTES/1024/1024 "Size MB",
       round(MAXBYTES/1024/1024,0) "Max Size MB",
       ONLINE_STATUS "Online", 
       AUTOEXTENSIBLE "AutoExt",
       FILE_ID,
       FILE_NAME "Filename"
from
       dba_data_files
order by tablespace_name ;

Output

The datafile storage location is /u02/oradata/CDB01/PDB01/.

1
2
3
4
5
6


Tablespace      Status       Size MB Max Size MB Online  Aut    FILE_ID Filename
--------------- --------- ---------- ----------- ------- --- ---------- ------------------------------------------------------------
SYSAUX          AVAILABLE        235    33554432 ONLINE  YES          9 /u02/oradata/CDB01/PDB01/sysaux01.dbf
SYSTEM          AVAILABLE        240    33554432 SYSTEM  YES          8 /u02/oradata/CDB01/PDB01/system01.dbf
UNDOTBS1        AVAILABLE        325    33554432 ONLINE  YES         10 /u02/oradata/CDB01/PDB01/undotbs01.dbf
USERS           AVAILABLE          5       32768 ONLINE  YES         11 /u02/oradata/CDB01/PDB01/users01.dbf

Create new tablespace USR01_TS

1
2
3
4
5


CREATE TABLESPACE USR01_TS 
 DATAFILE '/u02/oradata/CDB01/PDB01/USERS01.DBF' SIZE 100M 
 REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
 EXTENT MANAGEMENT LOCAL
 SEGMENT SPACE MANAGEMENT AUTO;

Re-create tablespace USR01_TS

If you need to re-create the tablespace again use the DDL below.

1

drop tablespace USR01_TS including contents and datafiles;

Create new user USR01

1
2
3
4
5


CREATE USER usr01 IDENTIFIED BY "Sky_Wa1ker"
DEFAULT TABLESPACE USR01_TS
TEMPORARY TABLESPACE temp;

ALTER USER USR01 QUOTA 10G ON USR01_TS;

To Drop User USR01 If Needed Use

1

drop user USR01 cascade;

Grant necessary privileges

Exampel of usefull privileges.

1
2
3
4
5
6
7
8


GRANT CREATE SESSION TO usr01;
GRANT CREATE TABLE TO usr01;
GRANT CREATE SEQUENCE TO usr01;
GRANT CREATE PROCEDURE TO usr01;
GRANT CREATE TRIGGER TO usr01;
GRANT CREATE VIEW TO usr01;
GRANT CREATE TYPE TO usr01;
GRANT CREATE SYNONYM TO usr01;

Create Test Table USR_TEST_TBL with data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


CREATE TABLE "USR01"."USR_TEST_TBL" 
(	
 "NAME" VARCHAR2(80 BYTE) NOT NULL ENABLE, 
 "CREATE_DATE" DATE NOT NULL ENABLE
) 
TABLESPACE "USR01_TS" ;

COMMENT ON COLUMN "USR01"."USR_TEST_TBL"."NAME" IS 'User Name';
COMMENT ON COLUMN "USR01"."USR_TEST_TBL"."CREATE_DATE" IS 'Record Create Date';

INSERT INTO usr_test_tbl ( name, create_date ) VALUES ( 'Donald Duck', to_date('2025-04-03 19:23:22', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO usr_test_tbl ( name, create_date ) VALUES ( 'Donald Trump', to_date('2025-04-03 19:24:24', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO usr_test_tbl ( name, create_date ) VALUES ( 'Don Carry', to_date('2025-04-03 20:14:34', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;

Test connect to user usr01 from sqlplus

Use JDBC connection string with Oracle Sqldeveloper, use custom JDBC URL.

1

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srvdb01.home.loc)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PDB01)))

Connect to PDB01 user usr01. Note! Run one line at time, do not copy and paste all lines since it fails.

1
2
3
4
5
6


$ sqlplus /nolog
connect usr01@PDB01
set line 132 pagesize 999 tab off feedback off

show con_name
show user

Select Test Table USR_TEST_TBL

1
2
3
4


col name format a15
col create_date format a19

SELECT name, to_char(create_date, 'YYYY-MM-DD HH24:MI:SS') as create_date FROM usr_test_tbl;

SQL Output

1
2
3
4
5


NAME            CREATE_DATE
--------------- -------------------
Donald Duck     2025-04-03 19:23:22
Donald Trump    2025-04-03 19:24:24
Don Carry       2025-04-03 20:14:34

Get user account key value

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


alter session set container = PDB01;
set line 300 pagesize 999 tab off

-- SQL Query 1
with t as
( select TO_CHAR(dbms_metadata.get_ddl('USER','&Username')) ddl from dual )
  select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER') || ';' from t;

-- SQL Quert 2
select name || '=> ' || spare4  from sys.user$ where name = '&username' ;

SQL Output 1

1
2
3
4
5


REPLACE(SUBSTR(DDL,1,INSTR(DDL,'DEFAULT')-1),'CREATE','ALTER')||';'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   ALTER USER "SCOTT" IDENTIFIED BY VALUES 'S:E6D0625412D2CBC0F39895BAE3E42430B1216459DE6044F9F88F38187D12;T:42FDA59872753D5F9D9C5214BAC407CABB05B46E7696D3E8F73E6752B4431019D52A492B9FB0170832500482DA7C982053AAFA34B5C1DC4BD8E8211595DB4EF9AD120363491A029732485E70F83E93C8'
      ;

SQL Output 2

1
2
3
4


NAME||'=>'||SPARE4
------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT=> S:E6D0625412D2CBC0F39895BAE3E42430B1216459DE6044F9F88F38187D12;T:42FDA59872753D5F9D9C5214BAC407CABB05B46E7696D3E8F73E6752B4431019D52A492B9FB01
70832500482DA7C982053AAFA34B5C1DC4BD8E8211595DB4EF9AD120363491A029732485E70F83E93C8

Create new profile P_UNLIMIT#

Unlock account oradba#

Create new user oradba in CDB#

Create new user oradba in NonCDB#

Set new password for user ORADBA with profile P_ADMIN#

Set account user and password#

Create New PDB User USR01#

Connect to PDB PDB01#

Check existing tablespace and datafiles#

Output#

Create new tablespace USR01_TS#

Re-create tablespace USR01_TS#

Create new user USR01#

To Drop User USR01 If Needed Use#

Grant necessary privileges#

Create Test Table USR_TEST_TBL with data#

Test connect to user usr01 from sqlplus#

Select Test Table USR_TEST_TBL#

SQL Output#

Get user account key value#

SQL Output 1#

SQL Output 2#