How to backup database in XE?

It is very simple to backup your database in Oracle XE with already prepared .bat file quickly.

Steps;
Press Windows Key -> Type "Backup Database" -> Click "Backup Database" link -> .bat file "Backup.bat" runs -> Follow the steps -> Done

Finally, check the backup path;
...\oracle\fast_recovery_area\XE

e.g. truncate table cascade

Preparation of Test Data
test=# 
create table employees (id integer, name varchar, department_id integer);

test=# 
create table departments (id int, department_name varchar);

test=# 
ALTER TABLE DEPARTMENTS
ADD CONSTRAINT DEPT_PK 
PRIMARY KEY (ID);

test=# 
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK 
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(ID);

test=# 
insert into departments
values (1,'IT');

test=# 
insert into departments
values (2,'Sales');

test=# 
insert into departments
values (3,'Marketing');

test=# 
insert into employees
values (1,'John',1);

test=# 
insert into employees
values (2,'Alex',1);

test=# 
insert into employees
values (3,'Guru',2);

test=# 
insert into employees
values (4,'Maximus',3);

test=# 
commit;

Data Check
-- select all rows of parent table 
test=# 
select * from departments;

-- select all rows of child table
test=# 
select * from employees;

Action
-- by using truncate table cascade, parent table and child table data will be removed. 
test=# 
truncate table departments cascade;

Last Data Check
-- select all rows of parent table 
test=# 
select * from departments;

-- select all rows of child table
test=# 
select * from employees;

Info: In Oracle 12c, you are able to use "cascade" clause with "truncate table" statement as well.

ORA-02292 solution

Preparation
-- Insert test data into DEPARTMENT and EMPLOYEES tables.
SQL>
INSERT INTO DEPARTMENTS
VALUES (280,'New Department',100,1700);

SQL>
INSERT INTO EMPLOYEES
VALUES (207,'John','Mathew','JMATHEW','111.111.111',TO_DATE('25.04.2018','DD.MM.YYYY'),'IT_PROG',10000,0.5,100,280);

SQL>
INSERT INTO EMPLOYEES
VALUES (208,'Jim','Parker','JPARKER','111.111.111',TO_DATE('25.05.2018','DD.MM.YYYY'),'IT_PROG',20000,0.6,100,280);

SQL>
INSERT INTO EMPLOYEES
VALUES (209,'Sophia','Ran','SRAN','111.111.111',TO_DATE('25.06.2018','DD.MM.YYYY'),'IT_PROG',30000,0.7,100,280);

SQL>
INSERT INTO EMPLOYEES
VALUES (210,'Wendi','Blake','WBLAKE','111.111.111',TO_DATE('25.07.2018','DD.MM.YYYY'),'IT_PROG',40000,0.8,100,280);

SQL>
COMMIT;

Action
SQL>
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 280;

Error
ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found

Solution
-- Find referencial integrity constraint between DEPARTMENT and EMPLOYEES tables for this error.
SQL>
SELECT * 
FROM ALL_CONSTRAINTS 
WHERE TABLE_NAME IN ('EMPLOYEES', 'DEPARTMENTS') AND CONSTRAINT_TYPE = 'R';

-- Drop constraint
SQL>
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_DEPT_FK;

-- Re-define constraint with "ON DELETE CASCADE" option
SQL>
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK 
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(DEPARTMENT_ID)
ON DELETE CASCADE;

-- Try again
SQL>
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 280;

SQL>
COMMIT;

e.g. count and distinct aggregate functions

examples; count (1) - count(*) - count(column_name) - count(distinct (column_name))

e.g. count aggregate function
SQL>
SELECT
    COUNT(*)
FROM
    employees;

  COUNT(*)
----------
       107

SQL>
SELECT
    COUNT(1)
FROM
    employees;

  COUNT(*)
----------
       107

SQL>
SELECT
    COUNT(commission_pct)
FROM
    employees;

COUNT(COMMISSION_PCT)
---------------------
                   35

Info: COUNT(1) and COUNT(*) calculate all rows even all columns data are NULL. However, COUNT(COLUMN_NAME) ignores NULL data.

e.g. distinct aggregate function
SQL>
SELECT
    distinct(commission_pct)
FROM
    employees;

COMMISSION_PCT
--------------
          NULL
           ,15
           ,35
            ,4
            ,3
            ,2
           ,25
            ,1

8 rows selected.

Info: DISTINCT(COLUMN_NAME) function uses not to see duplicated rows and also it shows NULL column if it exists.

e.g. both of count and distinct aggregate functions
SQL>
SELECT
    COUNT(DISTINCT(commıssıon_pct) )
FROM
    employees;

COUNT(DISTINCT(COMMISSION_PCT))
-------------------------------
                              7

Info: count(column_name) ignores NULL values, even if distinct function is used.

ORA-02449 solution

Preparation
SQL>
CREATE TABLESPACE EXAMPLE_TS DATAFILE
  'C:\ORACLEXE_XE\APP\ORACLE\ORADATA\XE\example_01.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SQL>
CREATE TABLESPACE EXAMPLE_TS_2 DATAFILE
  'C:\ORACLEXE_XE\APP\ORACLE\ORADATA\XE\example_02.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SQL>
CREATE TABLE T_EXAMPLE (ID NUMBER)
TABLESPACE EXAMPLE_TS;

SQL>
CREATE TABLE T_EXAMPLE_CHILD (ID NUMBER, FID NUMBER)
TABLESPACE EXAMPLE_TS_2;

SQL>
ALTER TABLE T_EXAMPLE
ADD CONSTRAINT ID_EXAMPLE_PK
PRIMARY KEY (ID);

SQL>
ALTER TABLE T_EXAMPLE_CHILD
ADD CONSTRAINT FID_EXAMPLE_FK
FOREIGN KEY (FID)
REFERENCES T_EXAMPLE (ID);

Action
SQL>
DROP TABLESPACE EXAMPLE_TS;

Error
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

Next Action
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES;

Next Error
SQL>
ORA-02449: unique/primary keys in table referenced by foreign keys

Solid Solution
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

ORA-01549 solution

Preparation
SQL>
CREATE TABLESPACE EXAMPLE_TS DATAFILE
  'C:\ORACLE_XE\APP\ORACLE\ORADATA\XE\example_01.dbf' SIZE 512K REUSE
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SQL>
CREATE TABLE T_EXAMPLE (ID NUMBER)
TABLESPACE EXAMPLE_TS;

Action
SQL>
DROP TABLESPACE EXAMPLE_TS;

Error
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

Solution
SQL>
DROP TABLESPACE EXAMPLE_TS INCLUDING CONTENTS AND DATAFILES;

e.g. pivot clause

Preparation 
* Define column names for pivot table
SQL>
SELECT LISTAGG (
          '''' || JOB_ID || '''' || ' AS ' || '"' || JOB_TITLE || '"' || ', ')
       WITHIN GROUP (ORDER BY JOB_TITLE)
  FROM JOBS;

Pivot example
* Show that how many jobs are set to persons according to department names.
SQL>    
SELECT *
    FROM (SELECT JOB_ID, DEPARTMENT_NAME
            FROM EMPLOYEES A, DEPARTMENTS B
           WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) PIVOT (
COUNT (JOB_ID)
            FOR JOB_ID
            IN  ('AD_VP' AS "ADMIN",
                'FI_ACCOUNT' AS "Accountant",
                'AC_MGR' AS "Accounting Manager",
                'AD_ASST' AS "Administration Assistant",
                'FI_MGR' AS "Finance Manager",
                'HR_REP' AS "Human Resources Representative",
                'MK_MAN' AS "Marketing Manager",
                'MK_REP' AS "Marketing Representative",
                'AD_PRES' AS "Presidentt",
                'IT_PROG' AS "Programmer",
                'AC_ACCOUNT' AS "Public Accountant",
                'PR_REP' AS "Public Relations Represent.",
                'PU_CLERK' AS "Purchasing Clerk",
                'PU_MAN' AS "Purchasing Manager",
                'SA_MAN' AS "Sales Manager",
                'SA_REP' AS "Sales Representative",
                'SH_CLERK' AS "Shipping Clerk",
                'ST_CLERK' AS "Stock Clerk",
                'ST_MAN' AS "Stock Manager"))
ORDER BY DEPARTMENT_NAME DESC;

How to use SPOOL command in Oracle?

WINDOWS
e.g.
SQL> 
sqlplus / as sysdba;

SQL>
SET LINESIZE 200;
SET PAGESIZE 100;

-- define a output file for writing
SPOOL 'C:\output_SQL.txt'; 

  SELECT owner,
         object_name,
         created,
         timestamp
    FROM DBA_OBJECTS
   WHERE STATUS = 'INVALID'
ORDER BY OBJECT_NAME;

-- stop to write
SPOOL OFF; 

LINUX
e.g.
SQL> 
sqlplus / as sysdba;

SQL>
SET LINESIZE 200;
SET PAGESIZE 100;

--define a output file for writing
SPOOL '/tmp/output_SQL.txt'; 

  SELECT owner,
         object_name,
         created,
         timestamp
    FROM DBA_OBJECTS
   WHERE STATUS = 'INVALID'
ORDER BY OBJECT_NAME;

-- stop to write
SPOOL OFF; 

-> to see output file
$ cat /tmp/output_SQL.txt

What is implicit commit?

After every DDL operation, implicit commit occurs such as while defining a new table.

e.g.
CREATE TABLE HR.EXAMPLE(ABC char(1));

Also we call it as "auto commit" operation.

blob vs bfile data types

BLOB (binary large objects) stores unstructured binary large objects. It is often used for graphic images, video clips, and sounds. The BLOB data type stores the content inside the Oracle database.

BFILE (binary file) is also used to large binary file stored outside the database. Oracle can read the file only, not modify it. Oracle requires appropriate operating system level read permissions on the file.

Ref: Oracle SQL Interactive Workbook (Alice Rischert)

How to do CTAS in Oracle?

CTAS stands for Create Table As Select

e.g.
SQL>
CREATE TABLE HR.EMPLOYEES_NEW
AS
   SELECT * FROM HR.EMPLOYEES;

-> By default, only "NOT_NULL" constraints copy to new table (HR.EMPLOYEES_NEW) if it has (HR.EMPLOYEES). (PK, FK, index, etc. does not copy)

CTAS is faster because UNDO and REDO data does not generate. (It use NOLOGGING AND PARALEL methods)

NULLS LAST keyword usage

-> HR.EMPLOYEES will be test table.
SQL> 
DESC HR.EMPLOYEES;

e.g.
To populate all data
SQL> 
SELECT * FROM HR.EMPLOYEES;

To manipulate data by "COMMISSION_PCT" column descending order.
SQL> 
  SELECT *
    FROM HR.EMPLOYEES
ORDER BY COMMISSION_PCT DESC;

-> This is wrong query cause "NULL" columns show on top. Therefore, "NULLS LAST" keyword should be used.
SQL>
  SELECT *
    FROM HR.EMPLOYEES
ORDER BY COMMISSION_PCT DESC NULLS LAST;

ORA-02270 solution

Preparation
SQL>
create table A (id number, soid number);

SQL>
create table B (id number);

Action
SQL> 
alter table A
add constraint SOID_B_FK
foreign key (soid)
references B (id);

Error
ORA-02270: no matching unique or primary key for this column-list

Reason
Parent table (B) does not have any primary key for matching with child table (A)

Solution
SQL>
alter table B
add constraint ID_PK 
primary key (id);

ORA-00959 solution

Action
SQL> 
CREATE TABLE HR.EXAMPLE(ABC char(1));

Error
ORA-00959: tablespace '_$deleted$40$0' does not exist

Reason
Defined default tablespace is not available any more for HR user.

Solution
SQL>
ALTER USER HR DEFAULT TABLESPACE NEW_USERS;

ORA-02449 solution

Preparation
SQL>
-- A table is child table
create table A (id number, soid number);

SQL>
-- B table is parent table
create table B (id number); 

SQL>
alter table B
add constraint ID_B_PK 
primary key (id);

SQL>
alter table A
add constraint ID_A_PK 
primary key (id);

SQL>
alter table A
add constraint SOID_B_FK
foreign key (soid)
references B (id);

Action
SQL> 
drop table B;

Error
ORA-02449: unique/primary keys in table referenced by foreign keys

Solution
SQL>
drop table B cascade constraints;

Result
By "cascade constraints" keywords, only SOID_B_FK foreign key removed from Table A, ID_A_PK primary key remains for Table A.

ORA-01720 solution

Preparation
"employees" table exists in HR schema

"v_employees" view exists in HR2 schema;
SQL> grant select on hr.employees_new to hr2;
SQL> create view hr2.v_employees as select * from hr.employees;

User wants to see H2.V_EMPLOYEES view with HR3 user.
Action
SQL> grant select on hr2.v_employees to hr3;

Error
ORA-01720: grant option does not exist for 'HR.EMPLOYEES'

Solution
Append grant option to HR.EMPLOYEES table for HR2 user
SQL> grant select on hr.employees to hr2 with grant option;

Then, grant select permission to HR3 user
SQL> grant select on hr2.v_employees to hr3;

Result
SQL> select * from HR2.v_employees;

How to see both Oracle VM Virtual Box 32bit/64bit options?

e.g. If there are only 32 bit Linux distro in the OS list, you should enable virtualization support in BIOS

* BIOS -> CMOS Setup Utility -> Virtualization Technology -> [Enabled]

e.g. bulk insert

SET TIMING ON
/
CREATE TABLE HR.EMPLOYEES_TEST
AS
   SELECT *
     FROM HR.EMPLOYEES
    WHERE ROWNUM < 0;
/
DECLARE
   TYPE TABLE_TYP IS TABLE OF HR.EMPLOYEES%ROWTYPE;

   TABLE_ROWS   TABLE_TYP := TABLE_TYP ();

   CURSOR TEST_CURSOR
   IS
      SELECT * FROM HR.EMPLOYEES;

   V_COUNT      NUMBER;
BEGIN
   SELECT COUNT (1) INTO V_COUNT FROM HR.EMPLOYEES;

   DBMS_OUTPUT.PUT_LINE ('TOTAL NUMBER OF ROWS: ' || V_COUNT);

   OPEN TEST_CURSOR;

   LOOP
      FETCH TEST_CURSOR BULK COLLECT INTO TABLE_ROWS LIMIT 10;

      FORALL A IN TABLE_ROWS.FIRST .. TABLE_ROWS.LAST
         INSERT INTO HR.EMPLOYEES_TEST
              VALUES TABLE_ROWS (A);

      COMMIT;
      DBMS_OUTPUT.PUT_LINE (
         'Looping, TEST_CURSOR%rowcount = ' || TEST_CURSOR%ROWCOUNT);
      EXIT WHEN TEST_CURSOR%NOTFOUND;
   
   --EXIT WHEN TEST_CURSOR%ROWCOUNT > 1000000;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE (
      'Exited, TEST_CURSOR%rowcount = ' || TEST_CURSOR%ROWCOUNT);

   CLOSE TEST_CURSOR;
END;
/

[ADVICE] Free Database Monitoring Tool

You may use following free tool instead of Oracle EM (Enterprise Manager)

• ASH Viewer (Active Session History)

Naming conventions for database objects

index -> [SHORT_TABLE_NAME]_[SHORT_COLUMN_NAME]_[INDEX_TYPE]
e.g. EMP_DEPARTMENT_IX

constraint -> [SHORT_TABLE_NAME]_[SHORT_COLUMN_NAME]_[CONSTRAINT_TYPE]
e.g. 
NOT NULL const. -> EMP_EMAIL_NN
FOREIGN KEY (Referential Integrity) const. 
Source table: EMPLOYEES
Column name of source table: DEPARTMENT_ID
-> EMP_DEPT_FK

trigger -> [TABLE_NAME]_TRG
e.g. HR.EMPLOYEES_TRG

sequence -> [TABLE_NAME]_SEQ
e.g. HR.EMPLOYEES_SEQ

type -> [TABLE_NAME]_TYP
e.g. HR.EMPLOYEES_TYP