Why am I getting these Oracle SQL Developer errors-CodePudding

I can't figure out what I am doing wrong here

--1.Create a unique Profile based on the following requirements

CREATE PROFILE PDylanReed LIMIT

  SESSIONS_PER_USER          3 

  CPU_PER_SESSION            UNLIMITED 

  CPU_PER_CALL               3000 

  CONNECT_TIME               45 

  LOGICAL_READS_PER_SESSION  DEFAULT 

  LOGICAL_READS_PER_CALL     1000 

  PRIVATE_SGA                15K

  COMPOSITE_LIMIT            5000000

  FAILED_LOGIN_ATTEMPTS   4

  PASSWORD_LIFE_TIME         120

  PASSWORD_LOCK_TIME         1/24

  PASSWORD_GRACE_TIME        110

  PASSWORD_REUSE_TIME        600

  PASSWORD_REUSE_MAX         5

  PASSWORD_VERIFY_FUNCTION   ora12c_verify_function;

--2.Verify your Profile was successfully created by Creating and executing a SQL statement querying the appropriate Data Dictionary objects

SELECT * FROM DBA_SQL_PROFILES where name = PDylanReed;

--3.Create 2 users assign them to the Permanent Tablespace of Users with a Quota of 30M

CREATE USER U1DylanReed

   IDENTIFIED BY temporary_password 

   PERMANENT TABLESPACE Users

   QUOTA 30M ON Users

   PROFILE PDylanReed

   PASSWORD EXPIRE;


CREATE USER U2DylanReed

   IDENTIFIED BY temporary_password 

   PERMANENT TABLESPACE Users

   QUOTA 30M ON Users

   PROFILE PDylanReed 

   PASSWORD EXPIRE;

--4.Create a role allowing users assigned to be able to connect to the database and create tables.

CREATE ROLE R1DylanReed;

GRANT CREATE SESSION, CREATE TABLE TO R1DylanReed;

SET ROLE R1DylanReed;  -> enables the role

Errors I'm getting

enter image description here

Error starting at line : 3 in command -
CREATE PROFILE PDylanReed LIMIT 
  SESSIONS_PER_USER          3 
  CPU_PER_SESSION            UNLIMITED 
  CPU_PER_CALL               3000 
  CONNECT_TIME               45 
  LOGICAL_READS_PER_SESSION  DEFAULT 
  LOGICAL_READS_PER_CALL     1000 
  PRIVATE_SGA                15K
  COMPOSITE_LIMIT            5000000
  FAILED_LOGIN_ATTEMPTS   4
  PASSWORD_LIFE_TIME         120
  PASSWORD_LOCK_TIME         1/24
  PASSWORD_GRACE_TIME        110
  PASSWORD_REUSE_TIME        600
  PASSWORD_REUSE_MAX         5
  PASSWORD_VERIFY_FUNCTION   ora12c_verify_function
Error report -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

Error starting at line : 23 in command -
SELECT * FROM DBA_SQL_PROFILES where name = PDylanReed
Error at Command Line : 23 Column : 15
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Error starting at line : 28 in command -
CREATE USER U1DylanReed
   IDENTIFIED BY temporary_password 
   PERMANENT TABLESPACE Users
   QUOTA 30M ON Users
   PROFILE PDylanReed
   PASSWORD EXPIRE
Error report -
ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:

Error starting at line : 35 in command -
CREATE USER U2DylanReed
   IDENTIFIED BY temporary_password 
   PERMANENT TABLESPACE Users
   QUOTA 30M ON Users
   PROFILE PDylanReed 
   PASSWORD EXPIRE
Error report -
ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:

Error starting at line : 44 in command -
CREATE ROLE R1DylanReed
Error report -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

Error starting at line : 46 in command -
GRANT CREATE SESSION, CREATE TABLE TO R1DylanReed
Error report -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

Role R1DYLANREED succeeded.


Error starting at line : 48 in command -
  -> enables the role
Error report -
Unknown Command

CodePudding user response：

In line 3 you must place the word grant, to allow the profile to be created.

Ej: GRANT CREATE ...

Most of the time that solves the permissions issues...

CodePudding user response：

Screenshots you posted suggest that you don't have privileges to do those operations. User you're connected to lacks in the DBA role; "normal" users can't create profiles or other users.

Therefore, connect as a privileged user (such as SYS or SYSTEM) which is capable of maintaining the system. Or, even better, create a new user (let's call it MY_DBA) which will be granted the DBA role. Why? So that you wouldn't have to work with SYS and/or SYSTEM who are special, they own the database so you'd rather NOT mess up something.

How to get those privileges? If you're the only user of that database (because you installed it on your PC, for example), then do as I suggested. Otherwise, talk to your DBA (a person) who'll know how to help you.

Once you do that, work on tasks you've been given.

Note that create table statements you wrote are invalid; there's no such thing as permanent tablespace. You should follow documentation and use valid options only. If you meant to say that you don't want to use a temporary tablespace (which is a valid option) but rather a "permanent" (which doesn't exist), then you're wrong. A "temporary tablespace" is used for e.g. sorting data (if you're working with millions of rows and don't have enough RAM, it is done on a hard disk, in a temporary tablespace).