Oracle DB - Cheat Sheet

Installation

[root@dvpilot01 ~]# /etc/init.d/oracle-xe-18c configure
Specify a password to be used for database accounts. Oracle 
recommends that the password entered should be at least 8 characters 
in length, contain at least 1 uppercase character, 1 lower case 
character and 1 digit [0-9]. Note that the same password will be 
used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database XE.
Enter SYS user password:
*******
Enter SYSTEM user password:
****
Enter PDBADMIN User Password:
*******
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
31% complete
34% complete
38% complete
41% complete
43% complete
Completing Database Creation
47% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/XE.
Database Information:
Global Database Name:XE
System Identifier(SID):XE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/XE/XE.log" for further details.

Connect to Oracle Database using one of the connect strings:
     Pluggable database: dvpilot01.pal.tecnet/XEPDB1
     Multitenant container database: dvpilot01.pal.tecnet
Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE

SYSTEM password IBMDem0s

Making Oracle DB EM Express avaialbe to remote clients

[root@dvpilot02 ~]# sqlplus system

SQL*Plus: Release 18.0.0.0.0 - Production on Tue May 18 16:47:25 2021
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Tue May 18 2021 15:52:26 -04:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> exec DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Create user

login to the XEPDB1 database

create user comics identified by superheros;

grant connect to comics ;
grant create session to comics with admin option;

grant create table to comics; 
grant create view, create procedure, create sequence to comics;

grant unlimited tablespace to comics;

Create Tables

CREATE TABLE COMICS.UNIVERSES (
    UniverseID int not null, 
    Universe_Name varchar(254), 
    primary key(UniverseID)
);

CREATE TABLE COMICS.CHARACTERS (
    CharcaterID int not null, 
    Character_Name varchar(254), 
    UniverseID int,
    primary key (CharcaterID)
);

Insert Data

insert into COMICS.UNIVERSES (UniverseID, Universe_Name) Values (1, 'DC Comics');
-- insert into COMICS.UNIVERSES (UniverseID, Universe_Name) Values (2, 'Marvel');
-- insert into COMICS.UNIVERSES (UniverseID, Universe_Name) Values (3, 'DC Comics');