If your goal is to create an Oracle user and then use it to do stuff with your DB you probably want to consider doing this:
- log as sysdba
- create a pdb (a.k.a. pluggable database)
- create a user for your pdb
What follows assume you are working with a db in localhost, if not simply change "localhost" with your db uri.
Sample code
Log as sysdba:
$ sqlplus sys/<your_admin_pws>@localhost as sysdba
then execute this:
create pluggable database MYDATABASE
admin user Scott identified by tiger;
file_name_convert = ('/pdbseed/', '/mydatabase/')
;
alter pluggable database MYDATABASE open;
then you may want to grant some permission to user Scott: log out from @localhost and log back in as sysdba to your new db
$ sqlplus sys/<your_admin_pws>@localhost/MYDATABASE as sysdba
and grant Scott whatever permission you want, e.g.
grant connect to Scott;
grant create view to Scott;
grant create table to Scott;
grant create trigger to Scott;
now you are good to go: you have an empty database instance and a user. Just log in by doing
$ sqlplus Scott/tiger@localhost/MYDATABASE
Bonus
I had problem with tablespaces and quotas after that.
Log with sqlplus sys/<your_admin_pws>@localhost/MYDATABASE as sysdba
You can list all tablespaces with
SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;
You can create a new tablespace with
CREATE TABLESPACE TABLESPACENAME DATAFILE 'tablespace_datafile.dat' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M;
You can drop your broken tablespaces with
Drop tablespace TABLESPACENAME including contents and datafiles;
You can grant "access" to the tablespace to Scott with
alter user Scott quota unlimited on TABLESPACENAME;
N.B. quota unlimited
is probably a bad practice, check oracle docs on how to limit user's quota
11g
, you ought to be on12c
.19c
but still facing same issue. any suggestion?