0

I am very much new to oracle database, please forgive me for any technical errors in my question. I am using oracle 19c and I think my password for "system" user is different than the password for "sysdba". At the time of installation I used the same password every time but now while connecting to system or "\ as sysdba" , there is no problem connecting but when I type the password for "orcl_listener" or "orcl" or "sysdba" , the password is wrong . for example: conn sysdba Enter password: ERROR: ORA-01017: invalid username/password; logon denied

when I create a new connection in SQL Developer, I use a username as a system, and the password is correct. success when I create a new connection in SQL Developer, I use username as \ as sysdba and password is correct .success when I create a new connection in SQL Developer, I use username as sysdba and password is correct .not success and error says Status: Failure -Test failed: ORA-01017: invalid username/password; logon denied .I am such a new user, if possible please provide me basic details of oracle databases in the simplest ways.

Now the second problem is that after creating a new connection in SQL developer using system username as its password is working, I expanded connection and at the bottom, I right-clicked on other users and created a new user with username "hr" and here it says "ORA-65096: invalid common user or role name".when I used c## as prefix no error occurs but I don't want any c##, it means it has to be a local user but where is option to create a local user? please help me.

gkd
  • 11
  • 6
  • A couple quick thoughts. First keep in mind the difference between operating system users (Linux users, perhaps) and database users. "orcl_listener" is perhaps an OS user; I doubt that you created a database user by that name. Try to keep those two types of user in mind - they are quite different (but, in the case of SYS, they are related). Second, SYSDBA is not a user; rather, you `conn sys as sysdba`. SYSDBA is a collection of privileges SYS has; often you will want to `con sys as sysoper` so you don't damage things in the database. –  May 16 '20 at 15:20
  • Now: in most cases, for the user SYS you will use OS authentication; that is, you don't need to give a database password when you log in (and even if you do, it will be ignored). Instead, the database will trust that SYS is logged in correctly through the OS. Note, however, that if you set up a multi-tenant database, that will only be true for the root container, not for pluggable databases. –  May 16 '20 at 15:23
  • Thanks but If I create a user inside "\ as sysdba" and a user inside "SYSTEM" , then what will be the difference? Well I am using windows10 platform – gkd May 17 '20 at 06:53
  • how can I create a new user like system which I can use to store all databse? I want a HR user ,a userdefined ,how to create this? will I have to create inside system user? – gkd May 17 '20 at 06:57
  • 1
    Oracle database is a very complex database system and it's getting even more complex with multi-tenant option (CDB/PDB stuff). Just to be able to create a user account requires that you understand a lot of things like CDB/PDB, tablespaces, security, etc. I suggest you try to do some self training with https://docs.oracle.com/en/database/oracle/oracle-database/19/admqs/index.html and OBE (Oracle by example) tutorials. – pifor May 17 '20 at 07:31

1 Answers1

1

To understand common users and local users you have first to understand multitenant architecture in Oracle: container database (CDB) and pluggable databases (PDB).

In multitenant architecture you can only create a local user in a PDB.

To create a local user connected with SQL*Plus as user SYS:

Check your current CDB/PDB

show con_name

List existing PDBS

show pdbs

Go to an existing PDB:

alter session set container=mypdb;

Check your current PDB

show con_name

Create a local user in the current PDB and grant some basic privileges:

create user myuser identified by "mypwd" quota unlimited on tablespace myts;
grant create session to myuser;
grant create table to myuser;

To connect directly with SQL*Plus to this PDB you must use the Oracle Net service for this PDB:

sqlplus myuser/mypwd@myhost:myport/mypdb
pifor
  • 7,419
  • 2
  • 8
  • 16
  • I tried this first: alter session set container=orclpdb; Session altered. ________________________________________________ But Here is the error: create user myuser identified by "mypwd" quota unlimited on tablespace myts * ERROR at line 1: ORA-00922: missing or invalid option _______________________________________________ And If I just use this then error for : create user myuser identified by "mypwd" * ERROR at line 1: ORA-01109: database not open – gkd May 17 '20 at 04:04
  • I want to achieve this task: Connection Name: myconnection Username: hr Password: hr Hostname: localhost Port: 1521 SID: ORCL This is the user I want to create – gkd May 17 '20 at 04:05
  • You get ORA-00922 error because tablespace myts does not exist: in my answer it is only an example you must replace it by an existing tablespace name that you can retrieve with `select tablespace_name from dba_tablespaces`. – pifor May 17 '20 at 07:26
  • But I don't know why you get `ORA-01109: database not open`; – pifor May 17 '20 at 07:27
  • I think I should learn the basics of oracle and Thanks a lot. – gkd May 17 '20 at 15:04