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

Python OOPs Concepts: Using Variables and Methods

  Types of Variables in OOPs Python   Instance Variable Static Variable Local Variable   Object Level Variables Class Level Variables Method Level Variables When to use: For Every Object if you want Separate copy, use Instance Variables For all object one copy is required, use static variables Inside method, Just used for temporary requirement Where to Declare Inside the constructor method (in general) Within the class directly, outside of methods (in general)   Within the method only. How to Declare Within the constructor: Instance variables can be declared within the constructor method using the self .   Using default values : Instance variables can be assigned default values during initialization.   Outside the class: use object name.   · ...
1 comment
Read more

Polymorphism: Method Overloading vs Method Overriding

  Method Overloading In object-oriented programming languages, method overloading enables a class to have several methods with the same name but different parameters. However, in Python, method overloading is not directly supported as opposed to languages such as Java or C++. This is because Python allows developers to define default arguments for their methods and pass arguments of any type to a method. This flexibility allows a single method to handle various types of arguments, eliminating the need for overloading.   However, there is a way to simulate method overloading in Python by using default argument values or variable length arguments and conditional statements. Here's an example: Program using default arguments:       Program using variable length arguments:   Multiple methods with Same Name: When we define multiple methods with same name, Python will consider the last defined method only. Python will not support method overload...
Post a Comment
Read more

Is Li-Fi Better than Wi-Fi?

Li-Fi  ( light fidelity )  is a bidirectional wireless system that transmit data to the devices like mobiles, laptop, etc., via infrared light or LED. The device has a receiver to pick up light signals and a transmitter to send light signal back to the lamp using infrared light or LED. It was first unveiled in 2011 and, unlike Wi-Fi, which uses radio frequency, Li-Fi technology only needs a light source with a chip to transmit an internet signal through light waves. Light fidelity (LiFi) is a faster, more secure and efficient wireless connection that uses light waves to transmit data Li-Fi technology still has a long way to go before worldwide adoption but every year, we are getting nearer to enjoying it for ourselves. The future surely looks bright with LiFi. How LiFi Works? LiFi makes use of visible light through overhead lighting for the transmission of data. This is possible through the use of a Visible Light Communications (VLC) system for data transmission. A VLC system ...
1 comment
Read more