Oracle Sysdba Basic

# Creating a new user and granting privileges
sqlplus
Enter user-name: sys as sysdba
Enter password:

# melihat versi oracle
SELECT banner FROM v$version WHERE ROWNUM = 1;

# melihat user
SELECT user FROM dual;
USER
--------------------------------------------------------------------------------
SYS

# melihat servicename untuk koneksi di toad dll
SELECT sys_context('userenv','instance_name') FROM dual;
SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------
ORCLCDB

# Overview of the services used by all sessions provides the dictionary view v$session
# (or gv$session for RAC databases) in the column SERVICE_NAME
select SERVICE_NAME from gv$session where sid in (
select sid from V$MYSTAT);
SERVICE_NAME
--------------------------------------------------------------------------------
ORCLCDB

# To see what services are available in the database use following queries:
select name from V$SERVICES;
select name from V$ACTIVE_SERVICES;
NAME
--------------------------------------------------------------------------------
ORCLCDBXDB
SYS$BACKGROUND
SYS$USERS
ORCLCDB
orclpdb1

# When you connect to the Oracle database server, you connect to a container database (CDB) named ROOT.
# To show the current database, you use the SHOW command:
SHOW con_name;
CON_NAME
------------------------------
CDB$ROOT

# switch to a pluggable database, bisa lihat servicename di tnsnames.ora
# located in the ORACLE_HOME/network/admin directory
ORCLPDB1

# lihat pdb
SELECT pdb_name, status
  FROM cdb_pdbs
# alter session
alter session set container = pdborcl1;
SHOW con_name;

# Before creating a new user, you need to change the database to open
ALTER DATABASE OPEN;

# Create user in the pluggable database
CREATE USER MIMIT IDENTIFIED BY Orcl123;

# Grant priviliges
GRANT CONNECT, RESOURCE, DBA TO MIMIT;

# melihat user dengan kewenangan sysdba
desc v$pwfile_users;
select * from v$pwfile_users;

# melihat semua user
SELECT * FROM all_users ORDER BY created;

# Check the Status of the Oracle Database Listeners
lsnrctl status
lsnrctl status [nama listener]

# perlu diingat, nama listener ada di file listener.ora, service name ada di file tnsnames.ora