Skip to main content

ORACLE Express Edition: Getting Started


1. Introduction to Oracle Database 21c Express Edition (XE)

- Free, lightweight version of Oracle Database

- Ideal for learning and small-scale applications

- Limited to 12GB of user data and uses up to 2GB of RAM

2. Installation and Setup

2.1 Installing Oracle 21c XE

1. Download Oracle 21c XE from: https://www.oracle.com/database/technologies/xe-downloads.html

2. Run the installer:

   - Windows: Double-click the .exe file

   - Linux: Use `rpm` or `yum` command

3. Follow the installation wizard:

  •  Accept the license agreement
  • Choose an installation location (default is usually fine)
  • Set a password for the SYS, SYSTEM, and PDBADMIN accounts (write this down!)
  • Select the option to start the database service automatically (recommended) 
4. Complete the installation:
  • Wait for the installation process to finish
  • Note down the database connection details provided at the end
  • The default container database (CDB) name is XE
  • The default pluggable database (PDB) name is XEPDB1

2.2 Installing SQL Developer

1. Download from: https://www.oracle.com/tools/downloads/sqldev-downloads.html

2. Unzip the file

3. Run `sqldeveloper.exe` (Windows) or `sqldeveloper.sh` (Linux/macOS)


3. Connecting to the Database

3.1 Using SQL*Plus

1. Open a command prompt or terminal

2. Launch SQL*Plus:

   

   sqlplus system/your_password@//localhost:1521/XEPDB1

    

3.2 Using SQL Developer

1. Open SQL Developer

2. Click the "+" icon to create a new connection

3. Enter connection details:

   - Connection Name: LocalXE

   - Username: SYSTEM

   - Password: [Your password]

   - Hostname: localhost

   - Port: 1521

   - Service name: XEPDB1

4. Test the connection and save


4. Basic Oracle Commands

4.1 SQL*Plus Commands

- `SHOW USER`: Display current user

- `DESCRIBE table_name`: Show table structure

- `SET LINESIZE 100`: Set output line width

- `SET PAGESIZE 50`: Set lines per page

- `EXIT`: Quit SQL*Plus


4.2 Basic SQL Commands

- Select data:

    SELECT * FROM table_name WHERE condition;

   

- Insert data:

   INSERT INTO table_name (column1, column2) VALUES (value1, value2);

  

- Update data:

    UPDATE table_name SET column1 = value1 WHERE condition;

   

- Delete data:

   DELETE FROM table_name WHERE condition;

   

- Create a table:

     CREATE TABLE table_name (

      column1 datatype,

      column2 datatype,

      ...

  );

  

5. Practical Exercises


Exercise 1: Creating and Populating a Table

1. Create a "Students" table:

   

   CREATE TABLE Students (

       student_id NUMBER PRIMARY KEY,

       first_name VARCHAR2(50),

       last_name VARCHAR2(50),

       email VARCHAR2(100)

   );

   

2. Insert sample data:

   

   INSERT INTO Students VALUES (1, 'John', 'Doe', 'john.doe@example.com');

   INSERT INTO Students VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com');

   INSERT INTO Students VALUES (3, 'Bob', 'Johnson', 'bob.johnson@example.com');

  


3. Verify the data:

   SELECT * FROM Students;

 

Exercise 2: Basic Queries

1. Select students with a specific last name:

  

   SELECT * FROM Students WHERE last_name = 'Smith';

  


2. Count the number of students:

 

   SELECT COUNT(*) FROM Students;

 

Exercise 3: Modifying Data

1. Update a student's email:

   UPDATE Students SET email = 'john.doe.new@example.com' WHERE student_id = 1;

2. Delete a student:

   DELETE FROM Students WHERE student_id = 3;

3. Verify the changes:

   SELECT * FROM Students;


6. Troubleshooting Tips

  1. If SQL*Plus is not recognized:
    • Ensure Oracle 21c XE is properly installed
    • Add Oracle's bin directory to your system's PATH environment variable (e.g., C:\app<username>\product\21c\dbhomeXE\bin for Windows)
  2. If you can't connect to the database:
    • Verify the Oracle service is running (service name is typically OracleServiceXE)
    • Check your username and password
    • Confirm the hostname, port, and service name are correct
    • For PDB connections, ensure you're using XEPDB1 as the service name
  3. If SQL Developer fails to launch:
    • Ensure you have a compatible Java Runtime Environment (JRE) installed
    • Check SQL Developer's log files for error messages

  4. If you encounter "table or view does not exist" errors:

       - Check that you're connected to the correct database/schema

       - Verify table name and capitalization

  5. If you encounter issues with PDBs:
    • Ensure the PDB is open: 
      ALTER PLUGGABLE DATABASE XEPDB1 OPEN;
    • To connect to a PDB in SQL*Plus, use:
      CONNECT username/password@//localhost:1521/XEPDB1

Remember to consult Oracle's official documentation for Oracle Database 21c Express Edition or seek community support for more complex issues.

7. Conclusion

This session introduced you to Oracle 21c XE, covering installation, basic connections, fundamental SQL commands, and simple database operations. As you progress, explore more advanced features and SQL commands to enhance your Oracle database skills.


Comments

Post a Comment

Popular posts from this blog

Practical 1: Getting Started with MYSQL

 Getting Started with MySQL Introduction to MySQL Definition: MySQL is an open-source relational database management system (RDBMS) Uses: Web applications, data warehousing, e-commerce, logging applications Key features: Speed, reliability, scalability, and ease of use Installing MySQL Download MySQL Community Server from official website Follow installation wizard for your operating system Set root password during installation Verify installation: mysql --version MySQL Command-line Client Accessing MySQL: mysql -u root -p Basic commands: SHOW DATABASES ; CREATE DATABASE mydb ; USE mydb ; SHOW TABLES ; MySQL Workbench Introduction: Visual tool for database design and management Key features: SQL development Data modeling Server administration Example: Creating a new connection New Connection > Enter details (hostname, username, password) PHPMyAdmin Web-based MySQL administration tool Often comes pre-installed with web hosting packag...
Post a Comment
Read more

MYSQL Constraints

 PK - Primary Key: Uniquely identifies each record in a table. NN - Not Null: Ensures a column cannot have a NULL value. UQ - Unique: Ensures all values in a column are different. B - Binary: Stores binary byte strings. UN - Unsigned: For numeric types, allows only non-negative values. ZF - Zero Fill: Pads numeric values with zeros to the left. AI - Auto Increment: Automatically generates a unique number for new records. G - Generated Column: Value is computed from an expression. PK - Primary Key A primary key uniquely identifies each record in a table. It must contain unique values and cannot have NULL values.  Example: CREATE TABLE Students ( StudentID INT PRIMARY KEY , Name VARCHAR ( 50 ) , Age INT ) ; Here, StudentID is the primary key. NN - Not Null  This constraint ensures that a column cannot have NULL values.  Example: CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY , Name VARCHAR ( 50 ) NOT NULL , Email VA...
Post a Comment
Read more