ORACLE Create Database, Table
ORACLE
To create a table in Oracle 26ai, you first need to connect to the PDB (Plugable Database) inside the CDB (Container Database). Multiple PDBs (databases) can be created within a single CDB (a host database container).
CDB (Container Database, which acts as a database container for multiple PDBs)
➔ The CDB (often referred to as the root container CDB$ROOT) is a host system that contains everything needed to support PDBs, including metadata and Oracle-supplied objects.
➔ A container database (CDB) can host multiple PDBs.
PDB (Plugable Database)
➔ A PDB is a self-contained, user-created database that contains application-specific data, schema objects, and users, where database users and application developers work with database objects such as tables and views all the time.
➔ In Oracle's multitenant architecture, a single pluggable database (PDB) can contain only one specific logical database, which contains the schema objects, data, and metadata for the database.
➔ Each PDB appears and acts as a separate database to users and applications.
➔ A PDB has its own dedicated data dictionary and tablespace.
➔ Although a PDB contains only one logical database, multiple PDBs can be plugged into a single CDB.
➔ In Oracle multitenant architecture, a pluggable database (PDB) can share common data belonging to a container database (CDB).
Connect to the CDB Root: Log in to Oracle instance as a SYSDBA privileges.
Syntax
sqlplus / as sysdba
Verify Your Connection as a SYSDBA privileges.
Verify Your Connection, connect to the CDB Root. SHOW CON_NAME; command will display CDB$ROOT.
If the session is set to a PDB, switch to the root using the following command: ALTER SESSION SET CONTAINER=CDB$ROOT;
Syntax
SQL> SHOW CON_NAME;
CON_NAME
------------------------------
CDB$ROOT
SQL> SHOW CON_ID;
CON_ID
------------------------------
1
SQL>
Run following command to create pluggable database.
Syntax
SQL> ALTER SESSION SET CONTAINER = PDB$SEED;
Session altered.
SQL> SELECT FILE_NAME FROM dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\PDBSEED\SYSTEM01.DBF
C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\PDBSEED\SYSAUX01.DBF
C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\PDBSEED\UNDOTBS01.DBF
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
Session altered.
CREATE PLUGGABLE DATABASE demopdb ADMIN USER demopdbadmin IDENTIFIED BY demopdbadmin FILE_NAME_CONVERT=('C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\PDBSEED\','C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\demopdb\');
Open the PDB in MOUNTED mode and it must be in READ WRITE mode.
Syntax
ALTER PLUGGABLE DATABASE demopdb OPEN;
Switch Session to move from the CDB root to PDB.
Syntax
ALTER SESSION SET CONTAINER = demopdb;
A new pluggable database (PDB) does not have a USERS tablespace by default, so follow the steps below to create one.
Syntax
ALTER SESSION SET CONTAINER = demopdb;
SELECT name FROM v$datafile;
CREATE TABLESPACE users DATAFILE 'C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\DEMOPDB\users01.dbf' SIZE 500M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
ALTER DATABASE DEFAULT TABLESPACE users;
Create a Local User and grant the user necessary permissions to create objects.
Syntax
CREATE USER demopdbuser IDENTIFIED BY demopdbuser;
GRANT CONNECT, RESOURCE, DBA TO demopdbuser;
ALTER USER demopdbuser QUOTA UNLIMITED ON USERS;
Security Consideration
➔ Granting DBA privileges gives a user extensive power, and the UNLIMITED TABLESPACE system privilege allows the user to create objects in any tablespace, bypassing all explicit tablespace quotas.
➔ Granting an Oracle user the CONNECT, RESOURCE, and DBA roles gives them broad access.
➔ The CONNECT role allows sessions to be created, the RESOURCE role allows objects (tables, procedures) to be created, and the DBA role provides the user with full administrative privileges.
➔ So, the `GRANT CONNECT, RESOURCE, DBA TO username` command actually allows a user to operate with full administrative powers.
Application-Level Access
➔ Users who only need application-level access (to create objects like tables, views, etc.) can use GRANT CONNECT, RESOURCE TO username.
➔ The following commands in Oracle 26ai create a user that only requires application-level permissions and gives them the ability to create tables/views/procedures and use unlimited tablespaces.
Syntax
-- Create the user
CREATE USER demopdbdeveloper IDENTIFIED BY demopdbdeveloper;
-- Grant session connect
GRANT CREATE SESSION TO demopdbdeveloper;
-- Provide full developer roles (23c/26ai).
GRANT DB_DEVELOPER_ROLE TO demopdbdeveloper;
-- Grant Limited Tablespace.
ALTER USER demopdbdeveloper QUOTA 500M ON users;
-- Grant Unlimited Tablespace.
GRANT UNLIMITED TABLESPACE TO demopdbdeveloper;
-- Set the default tablespace (recommended, although it is optional)
ALTER USER demopdbdeveloper DEFAULT TABLESPACE users;
Connect to the PDB as the local user:
Syntax
CONNECT demopdbuser/demopdbuser@//localhost:1521/demopdb;
Show/check Available Database
➔ In Oracle Database 26ai, as in previous versions of Oracle, there is no direct SHOW DATABASES command like in MySQL or PostgreSQL.
➔ If you are connected to a CDB (container database, which acts as a database container for multiple PDBs) as a privileged user (SYSDBA) and want to see all PDBs, use this command SHOW PDBS;.
Syntax
SHOW PDBS;
Create tables in the database (PDB)
Syntax
CREATE TABLE emp (
emp_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(50),
hire_date DATE DEFAULT SYSDATE
);
Check and Describe tables to see the structure
Syntax
DESCRIBE emp;
SELECT table_name FROM user_tables;
Insert data into table
Syntax
INSERT INTO emp (emp_id, first_name, last_name, email, hire_date)
VALUES (100, 'John', 'Doe', 'john@email.com', TO_DATE('2026-04-08', 'YYYY-MM-DD'));
Query table to check data.
Syntax
SELECT * FROM emp;
Now we will practice the lesson of this chapter in a learning environment.
Step 01. Login as sysdba
Step 02. Log in to the Oracle instance with SYSDBA privileges. Verify Your Connection, connect to the CDB Root. SHOW CON_NAME; command will display CDB$ROOT.
If the session is set to a PDB, switch to the root using the following command: ALTER SESSION SET CONTAINER=CDB$ROOT;
Need to check the file location of PDB$SEED database.
Syntax
SQL> ALTER SESSION SET CONTAINER = PDB$SEED;
Session altered.
SQL> SELECT FILE_NAME FROM dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\PDBSEED\SYSTEM01.DBF
C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\PDBSEED\SYSAUX01.DBF
C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\PDBSEED\UNDOTBS01.DBF
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
Session altered.
CREATE PLUGGABLE DATABASE demopdb ADMIN USER demopdbadmin IDENTIFIED BY demopdbadmin FILE_NAME_CONVERT=('C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\PDBSEED\','C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\demopdb\');
Step 03. Run following command to create pluggable database.
Step 04. Open the PDB in MOUNTED mode and it must be in READ WRITE mode.
Step 05. Switch Session to move from the CDB root to PDB.
Step 06. By default a new pluggable database (PDB) does not have a USERS tablespace , so follow the steps below to create one.
Step 07. Create a Local User and grant the user necessary permissions to create objects.
Step 08. Login to the PDB as the local user.
Login to database as user or connect to user as sysdba
--Sysdba can connect to demopdbuser user by following command:
sqlplus / as sysdba
SHOW PDBS;
ALTER PLUGGABLE DATABASE demopdb OPEN;
ALTER SESSION SET CONTAINER = demopdb;
CONNECT demopdbuser/demopdbuser@//localhost:1521/demopdb;
SELECT table_name FROM user_tables;
SHOW user;
CONNECT / AS SYSDBA;
SHOW user;
-- Oracle, simply creating a user does not allow them to log in.
-- SYSDBA must explicitly grant them the CREATE SESSION privilege.
sqlplus / as sysdba
SHOW PDBS;
ALTER PLUGGABLE DATABASE demopdb OPEN;
ALTER SESSION SET CONTAINER = demopdb;
GRANT CREATE SESSION TO demopdbuser;
--demopdbuser user can login to demopdb database by following command:
sqlplus demopdbuser/demopdbuser@//localhost:1521/demopdb
SELECT table_name FROM user_tables;
Step 09. Create tables in the database (PDB), SYSDBA connect to demopdbuser
Step 10. Insert data into table and select query on table
Step 11. An explicit COMMIT is required to permanently save changes made by DML statements (INSERT, UPDATE, DELETE).
Useful Commands Oracle 26ai
Some commands require SYSDBA privileges to execute.
-- Identifying Current Container.
SHOW CON_NAME; -- Show Current Container Name
SHOW CON_ID; -- Show Current Container ID
SHOW PDBS; -- List All PDBs (while in CDB$ROOT)
-- Navigating Between CDB and PDB
ALTER SESSION SET CONTAINER = pdb_name; -- Switch to a PDB
ALTER SESSION SET CONTAINER = CDB$ROOT; -- Switch back to CDB Root
-- PDB Lifecycle commands are typically executed from the CDB$ROOT.
CREATE PLUGGABLE DATABASE pdb_name ADMIN USER admin_name IDENTIFIED BY password;--Create a PDB from Seed
ALTER PLUGGABLE DATABASE pdb_name OPEN; -- Open a PDB
ALTER PLUGGABLE DATABASE pdb_name CLOSE IMMEDIATE; -- Close a PDB
ALTER PLUGGABLE DATABASE ALL SAVE STATE; -- Save PDB State (for automatic restart)
-- Monitoring Views
SELECT * FROM V$CONTAINERS; -- List all containers (CDB and PDBs)
SELECT * FROM V$PDBS; -- View PDB-specific details
SELECT * FROM DBA_PDBS; -- Administrative info about PDBs
SELECT USERNAME, COMMON FROM CDB_USERS; -- Check user types (Common vs Local)
-- Check Connection, Type and Verifies if it is in a CDB or PDB.
SELECT name, open_mode FROM v$pdbs;
SELECT cdb FROM v$database;
-- Delete PDB and Physical Data Files
-- Connect to the CDB Root:
SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
--Close the PDB:
SQL> ALTER PLUGGABLE DATABASE pdb_name CLOSE;
--Drop the PDB with Data Files:
SQL> DROP PLUGGABLE DATABASE pdb_name INCLUDING DATAFILES;
-- list of available tablespaces in a database
SELECT tablespace_name FROM dba_tablespaces;
-- Check the file location of PDB$SEED database.
SQL> ALTER SESSION SET CONTAINER = PDB$SEED;
SQL> SELECT FILE_NAME FROM dba_data_files;
ALTER SESSION SET CONTAINER = demopdb;
-- Create USERS tablespace
SELECT name FROM v$datafile;
CREATE TABLESPACE users DATAFILE 'C:\APP\DEVOPS\PRODUCT\26AI\ORADATA\FREE\DEMOPDB\users01.dbf'
SIZE 500M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
ALTER DATABASE DEFAULT TABLESPACE users;
-- Unlock account for a user
ALTER USER username ACCOUNT UNLOCK;
-- Change Password for a user
ALTER USER username IDENTIFIED BY "NewPassword";
-- Grand create session to user
GRANT CREATE SESSION TO demopdbuser;
-- check user status as sysdba
SELECT username, account_status FROM dba_users WHERE username = 'DEMOPDBUSER';