Oracle

Below is a comprehensive list of commands and techniques for managing Oracle systems, including Oracle Database, Oracle Cloud, Oracle Linux, and Oracle VM.

Oracle Database

Basic SQL Commands

Connect to Oracle Database:

sqlplus username/password@//hostname:port/service_name

Select Data:
```
SELECT * FROM employees;
```

Insert Data:

INSERT INTO employees (id, name, age, department) VALUES (1, 'John Doe', 30, 'HR');

Update Data:

UPDATE employees SET age = 31 WHERE id = 1;

Delete Data:
```
DELETE FROM employees WHERE id = 1;
```

Data Definition Language (DDL)

Create Table:

CREATE TABLE employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    age NUMBER,
    department VARCHAR2(50)
);

Drop Table:
```
DROP TABLE employees;
```

Create Index:

CREATE INDEX idx_name ON employees (name);

Drop Index:
```
DROP INDEX idx_name;
```

Alter Table (Add Column):

ALTER TABLE employees ADD (salary NUMBER);

Alter Table (Modify Column):

ALTER TABLE employees MODIFY (salary NUMBER(10, 2));

Alter Table (Drop Column):

ALTER TABLE employees DROP COLUMN salary;

Data Manipulation Language (DML)

Merge Statement:

MERGE INTO employees e
USING (SELECT id, name FROM new_employees) n
ON (e.id = n.id)
WHEN MATCHED THEN
  UPDATE SET e.name = n.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (n.id, n.name);

Data Control Language (DCL)

Grant Privileges:

GRANT SELECT, INSERT ON employees TO username;

Revoke Privileges:

REVOKE INSERT ON employees FROM username;

Transaction Control

Commit Transaction:
```
COMMIT;
```
Rollback Transaction:
```
ROLLBACK;
```
Savepoint:
```
SAVEPOINT savepoint_name;
```
Rollback to Savepoint:
```
ROLLBACK TO SAVEPOINT savepoint_name;
```

Advanced SQL Commands

Subqueries:

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'HR');

Joins:

SELECT e.name, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id;

Union:

SELECT name FROM employees
UNION
SELECT name FROM contractors;

PL/SQL Programming

Declare and Execute PL/SQL Block:

DECLARE
    v_employee_name VARCHAR2(100);
BEGIN
    SELECT name INTO v_employee_name FROM employees WHERE id = 1;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;

Create Function:

CREATE OR REPLACE FUNCTION get_employee_name (p_id NUMBER)
RETURN VARCHAR2
IS
    v_name VARCHAR2(100);
BEGIN
    SELECT name INTO v_name FROM employees WHERE id = p_id;
    RETURN v_name;
END;

Create Procedure:

CREATE OR REPLACE PROCEDURE update_employee_salary (p_id NUMBER, p_salary NUMBER)
IS
BEGIN
    UPDATE employees SET salary = p_salary WHERE id = p_id;
    COMMIT;
END;

Create Trigger:

CREATE OR REPLACE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('New Employee Inserted: ' || :NEW.name);
END;

Performance Tuning

Explain Plan:

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Use Index Hint:

SELECT /*+ INDEX(employees idx_name) */ name FROM employees WHERE name = 'John Doe';

Gather Statistics:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

Backup and Recovery

Export Data (Data Pump):

expdp username/password@//hostname:port/service_name DIRECTORY=dpump_dir1 DUMPFILE=table.dmp TABLES=employees

Import Data (Data Pump):

impdp username/password@//hostname:port/service_name DIRECTORY=dpump_dir1 DUMPFILE=table.dmp TABLES=employees

Security Management

Create User:

CREATE USER new_user IDENTIFIED BY password;

Drop User:
```
DROP USER new_user CASCADE;
```
Grant Role to User:
```
GRANT connect, resource TO new_user;
```
Revoke Role from User:
```
REVOKE resource FROM new_user;
```

Data Warehousing and Analytics

Create Materialized View:

CREATE MATERIALIZED VIEW emp_mv AS
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;

Refresh Materialized View:

BEGIN
    DBMS_MVIEW.REFRESH('emp_mv', 'C');
END;

Create OLAP Cube:

CREATE CUBE sales_cube
DIMENSION BY (time_dim, product_dim, store_dim)
MEASURE BY (sales_amount, sales_quantity)
USING sales_fact_table;

Query OLAP Cube:

SELECT time_dim.year, SUM(sales_amount)
FROM sales_cube
GROUP BY time_dim.year;

Advanced Oracle Techniques

Partitioning

Create Partitioned Table:

CREATE TABLE employees (
    id NUMBER,
    name VARCHAR2(100),
    hire_date DATE
)
PARTITION BY RANGE (hire_date) (
    PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

Add Partition:

ALTER TABLE employees
ADD PARTITION p4 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY'));

Advanced PL/SQL

Bulk Collect:

DECLARE
    TYPE t_emp IS TABLE OF employees%ROWTYPE;
    l_emp t_emp;
BEGIN
    SELECT * BULK COLLECT INTO l_emp FROM employees;
END;

Forall Statement:

DECLARE
    TYPE t_ids IS TABLE OF employees.id%TYPE;
    l_ids t_ids := t_ids(1, 2, 3);
BEGIN
    FORALL i IN l_ids.FIRST..l_ids.LAST
        DELETE FROM employees WHERE id = l_ids(i);
    COMMIT;
END;

Dynamic SQL:

EXECUTE IMMEDIATE 'CREATE TABLE temp_table (id NUMBER)';

Oracle RAC and Data Guard

Oracle Real Application Clusters (RAC)

Check Cluster Status:
```
srvctl status database -d mydb
```
Start Cluster:
```
srvctl start database -d mydb
```
Stop Cluster:
```
srvctl stop database -d mydb
```

Oracle Data Guard

Switch to Standby:

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

Switch to Primary:

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Enable Data Guard Broker:
```
ALTER SYSTEM SET dg_broker_start=true;
```

Oracle Cloud Infrastructure (OCI)

OCI CLI Commands

Install OCI CLI:

bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

Configure OCI CLI:
```
oci setup config
```
List All Compartments:
```
oci
```

  oci iam compartment list

Create a Compute Instance:

oci compute instance launch --availability-domain "xyz" --compartment-id "ocid1.compartment.oc1..exampleuniqueID" --shape "VM.Standard2.1" --image-id "ocid1.image.oc1..exampleuniqueID" --subnet-id "ocid1.subnet.oc1..exampleuniqueID" --display-name "myInstance"

List Compute Instances:

oci compute instance list --compartment-id "ocid1.compartment.oc1..exampleuniqueID"

Terminate a Compute Instance:

oci compute instance terminate --instance-id "ocid1.instance.oc1..exampleuniqueID"

Create a Block Volume:

oci bv volume create --availability-domain "xyz" --compartment-id "ocid1.compartment.oc1..exampleuniqueID" --size-in-gbs 50 --display-name "myVolume"

Attach a Block Volume:

oci compute volume-attachment create --instance-id "ocid1.instance.oc1..exampleuniqueID" --volume-id "ocid1.volume.oc1..exampleuniqueID" --type "iscsi"

Detach a Block Volume:

oci compute volume-attachment detach --volume-attachment-id "ocid1.volumeattachment.oc1..exampleuniqueID"

Create a VCN (Virtual Cloud Network):

oci network vcn create --compartment-id "ocid1.compartment.oc1..exampleuniqueID" --cidr-block "10.0.0.0/16" --display-name "myVCN"

Create a Subnet:

oci network subnet create --compartment-id "ocid1.compartment.oc1..exampleuniqueID" --vcn-id "ocid1.vcn.oc1..exampleuniqueID" --cidr-block "10.0.1.0/24" --availability-domain "xyz" --display-name "mySubnet"

Create a Load Balancer:

oci lb load-balancer create --compartment-id "ocid1.compartment.oc1..exampleuniqueID" --shape-name "100Mbps" --subnet-ids '["ocid1.subnet.oc1..exampleuniqueID"]' --display-name "myLoadBalancer"

Create a Database System:

oci db system launch --compartment-id "ocid1.compartment.oc1..exampleuniqueID" --availability-domain "xyz" --db-name "myDB" --admin-password "YourPassword123!" --shape "VM.Standard2.1" --subnet-id "ocid1.subnet.oc1..exampleuniqueID" --display-name "myDBSystem"

OCI Security

Create a Policy:

oci iam policy create --compartment-id "ocid1.compartment.oc1..exampleuniqueID" --name "myPolicy" --statements '["Allow group Administrators to manage all-resources in compartment myCompartment"]'

List Policies:

oci iam policy list --compartment-id "ocid1.compartment.oc1..exampleuniqueID"

Create a User:

oci iam user create --compartment-id "ocid1.compartment.oc1..exampleuniqueID" --name "myUser" --description "New User"

Create a Group:

oci iam group create --compartment-id "ocid1.compartment.oc1..exampleuniqueID" --name "myGroup" --description "New Group"

Add User to Group:

oci iam group add-user --user-id "ocid1.user.oc1..exampleuniqueID" --group-id "ocid1.group.oc1..exampleuniqueID"

Oracle Linux

System Administration

Install Software Package:
```
sudo yum install package_name
```
Update System Packages:
```
sudo yum update
```

Start/Stop a Service:

sudo systemctl start service_name
sudo systemctl stop service_name

Enable/Disable a Service:

sudo systemctl enable service_name
sudo systemctl disable service_name

Check System Status:
```
systemctl status
```
Check Disk Usage:
```
df -h
```
Check Memory Usage:
```
free -m
```

User Management

Create User:
```
sudo useradd newuser
```
Delete User:
```
sudo userdel newuser
```
Change User Password:
```
sudo passwd newuser
```
Add User to Group:
```
sudo usermod -aG groupname newuser
```

Oracle VM

Oracle VM Manager CLI (OVMCLI)

Connect to Oracle VM Manager:
```
ssh admin@hostname
```
List Virtual Machines:
```
list vm
```

Create Virtual Machine:

create vm name=vmName repository=repoName server_pool=poolName

Start Virtual Machine:
```
start vm name=vmName
```
Stop Virtual Machine:
```
stop vm name=vmName
```
Delete Virtual Machine:
```
delete vm name=vmName
```

Create Virtual Disk:

create virtual_disk name=diskName repository=repoName size=20G

Attach Virtual Disk to VM:

create virtual_disk_mapping virtual_disk=diskName vm=vmName

Oracle Enterprise Manager

Command Line Interface (EMCLI)

Install EMCLI:
```
emcli setup
```
Login to EMCLI:
```
emcli login -username=sysman
```

Discover Targets:

emcli discover_targets -hosts="host1,host2" -credentials=credentials_file

Create Monitoring Template:

emcli create_template -name="TemplateName" -type="monitoring"

Apply Monitoring Template:

emcli apply_template -name="TemplateName" -targets="target1,target2"

Create Incident Rule:

emcli create_rule_set -name="RuleSetName" -rules=rules_file

Advanced Techniques

Oracle Database

Flashback Database:
```
FLASHBACK DATABASE TO SCN scn_number;
```

Using Oracle Data Pump for Full Database Export/Import:

expdp username/password FULL=Y DIRECTORY=dpump_dir1 DUMPFILE=full_db.dmp
impdp username/password FULL=Y DIRECTORY=dpump_dir1 DUMPFILE=full_db.dmp

Cross-Platform Database Transport:

BEGIN
    DBMS_FILE_TRANSFER.GET_FILE(
        source_directory_object => 'SOURCE_DIR',
        source_file_name        => 'source_file',
        destination_directory_object => 'DEST_DIR',
        destination_file_name   => 'dest_file'
    );
END;

Oracle Cloud Infrastructure (OCI)

Using OCI CLI with Scripts:

# Example: List all compute instances and output to a file
oci compute instance list --compartment-id "ocid1.compartment.oc1..exampleuniqueID" > instances.txt

Automating OCI Tasks with Bash Scripts:

# Example: Stop all running instances in a compartment
instances=$(oci compute instance list --compartment-id "ocid1.compartment.oc1..exampleuniqueID" --query "data [*].id" --raw-output)
for instance in $instances; do
    oci compute instance action --instance-id $instance --action STOP
done

Summary

This comprehensive list covers a wide range of Oracle commands and techniques for managing Oracle Database, Oracle Cloud, Oracle Linux, and Oracle VM. It includes basic and advanced SQL commands, PL/SQL programming, Oracle RAC and Data Guard management, OCI CLI commands, Oracle VM Manager CLI, and more. These commands and techniques are essential for administrators and developers working with Oracle technologies.

Name		Name	Last commit message	Last commit date
Latest commit History 2 Commits
README.md		README.md

DeadmanXXXII/Oracle

Folders and files

Latest commit

History

Repository files navigation

Oracle

Oracle Database

Basic SQL Commands

Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Control Language (DCL)

Transaction Control

Advanced SQL Commands

PL/SQL Programming

Performance Tuning

Backup and Recovery

Security Management

Data Warehousing and Analytics

Advanced Oracle Techniques

Partitioning

Advanced PL/SQL

Oracle RAC and Data Guard

Oracle Real Application Clusters (RAC)

Oracle Data Guard

Oracle Cloud Infrastructure (OCI)

OCI CLI Commands

OCI Security

Oracle Linux

System Administration

User Management

Oracle VM

Oracle VM Manager CLI (OVMCLI)

Oracle Enterprise Manager

Command Line Interface (EMCLI)

Advanced Techniques

Oracle Database

Oracle Cloud Infrastructure (OCI)

Summary

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Packages