1.1. Checking Tablespace Utilization
Use the following query to monitor tablespace storage consumption across the database:
SELECT a.tablespace_name,
ROUND(a.total_size) "total_size_gb",
ROUND(a.total_size) - ROUND(b.free_size, 2) "used_size_gb",
ROUND(b.free_size, 2) "free_size_gb",
ROUND(b.free_size / total_size * 100, 2) || '%' free_rate
FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 / 1024 total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 / 1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);
Alternative formatted query with percentage calculations:
SET LINESIZE 200 PAGES 999
COL TBS_NAME FOR A25
SET SPACE 2
COMP SUM OF "TOTAL_GB" "USED_GB" "FREE_GB" ON REPORT
BREAK ON REPORT
SELECT a.tablespace_name tbs_name,
ROUND(a.bytes / 1024 / 1024 / 1024) total_gb,
ROUND((a.bytes - NVL(b.bytes, 0)) / 1024 / 1024 / 1024) used_gb,
ROUND((1 - ((a.bytes - NVL(b.bytes, 0)) / a.bytes)) * 100, 2) pct_free,
ROUND(((a.bytes - NVL(b.bytes, 0)) / a.bytes) * 100, 2) pct_used,
NVL(ROUND(b.bytes / 1024 / 1024 / 1024), 0) free_gb,
auto
FROM (SELECT tablespace_name, SUM(bytes) bytes, MAX(autoextensible) auto
FROM sys.dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;
1.2. Creating Tablespaces
1.2.1. Standard Data Tablespace
CREATE TABLESPACE data_tbs
LOGGING
DATAFILE '/u01/oradata/company/datafile01.dbf' SIZE 4G;
1.2.2. Temporary Tablespace
CREATE TEMPORARY TABLESPACE temp_tbs
TEMPFILE '/u01/oradata/company/tempfile01.dbf' SIZE 2G;
1.2.3. Undo Tablespace
CREATE UNDO TABLESPACE undo_tbs
DATAFILE '/u01/oradata/company/undo01.dbf' SIZE 2G;
1.3. Extending Tablespaces
-- Extend data tablespace
ALTER TABLESPACE data_tbs ADD DATAFILE '/u01/oradata/company/datafile02.dbf' SIZE 4G;
-- Extend temporary tablespace
ALTER TABLESPACE temp_tbs ADD TEMPFILE '/u01/oradata/company/tempfile02.dbf' SIZE 2G;
-- Extend undo tablespace
ALTER TABLESPACE undo_tbs ADD DATAFILE '/u01/oradata/company/undo02.dbf' SIZE 2G;
1.4. Removing Tablespaces
-- Remove standard tablespace
DROP TABLESPACE data_tbs INCLUDING CONTENTS AND DATAFILES;
-- Remove temporary tablespace (ensure another temp tablespace exists first)
DROP TABLESPACE temp_tbs INCLUDING CONTENTS AND DATAFILES;
-- Remove undo tablespace (ensure another undo tablespace exists first)
DROP TABLESPACE undo_tbs INCLUDING CONTENTS AND DATAFILES;
1.5. Raw Volume Tablespace Management
Raw volumes bypass the filesystem for direct block acces:
-- Create data tablespace on raw volume
CREATE TABLESPACE raw_data_tbs
DATAFILE '/dev/vg01/lv_data01.dbf' SIZE 8G;
-- Create temporary tablespace on raw volume
CREATE TEMPORARY TABLESPACE raw_temp_tbs
TEMPFILE '/dev/vg01/lv_temp01.dbf' SIZE 4G;
- User and Privilege Management
2.1. Creating Database Users
CREATE USER app_user
PROFILE DEFAULT
IDENTIFIED BY "SecurePass123"
DEFAULT TABLESPACE data_tbs
QUOTA UNLIMITED ON data_tbs
ACCOUNT UNLOCK;
2.2. Modifying User Passwords
ALTER USER app_user IDENTIFIED BY "NewSecurePass456";
2.3. Locking and Unlocking Accounts
-- Lock account
ALTER USER app_user ACCOUNT LOCK;
-- Unlock account
ALTER USER app_user ACCOUNT UNLOCK;
2.4. Granting System Privileges
GRANT CONNECT, RESOURCE, SELECT ANY TABLE, UPDATE ANY TABLE,
DELETE ANY TABLE, INSERT ANY TABLE, CREATE ANY PROCEDURE,
EXECUTE ANY PROCEDURE, CREATE ANY TRIGGER, CREATE ANY VIEW,
CREATE ANY SEQUENCE, CREATE DATABASE LINK, CREATE PUBLIC SYNONYM,
SELECT_CATALOG_ROLE
TO app_user;
2.5. Granting Object-Level Privileges
-- Grant specific table privileges
GRANT SELECT, INSERT, UPDATE ON hr.employees TO app_user;
-- Grant all privileges on a table
GRANT ALL ON hr.departments TO app_user;
2.6. Revoking Privileges
REVOKE DELETE ANY TABLE FROM app_user;
ALTER USER app_user DEFAULT ROLE ALL;
2.7. Dropping Users
-- Drop user without dependent objects
DROP USER app_user;
-- Drop user with all dependent objects
DROP USER app_user CASCADE;
2.8. Viewing User Information
-- View all user details
SELECT username, account_status, created, default_tablespace
FROM dba_users
WHERE username = 'APP_USER';
-- View system privileges granted to user
SELECT * FROM dba_sys_privs WHERE grantee = 'APP_USER';
-- View object privileges granted to user
SELECT * FROM dba_tab_privs WHERE grantee = 'APP_USER';
2.9. Role Management
-- Create custom role
CREATE ROLE data_analyst_role;
-- Grant privileges to role
GRANT SELECT ANY TABLE, CREATE VIEW TO data_analyst_role;
-- Assign role to user
GRANT data_analyst_role TO app_user;
-- Drop role
DROP ROLE data_analyst_role;
- Table Operations
3.1. Creating Tables
3.1.1. Basic Table Creation
CREATE TABLE employees (
employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE DEFAULT SYSDATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4)
) TABLESPACE data_tbs;
3.1.2. Create Table As Select (CTAS)
-- Copy table structure and data
CREATE TABLE employees_backup AS SELECT * FROM employees;
-- Copy structure only (with no rows)
CREATE TABLE employees_template AS SELECT * FROM employees WHERE 1=0;
3.2. Basic DML Operations
-- Insert single row
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (1001, 'John', 'Smith', 'jsmith@example.com', SYSDATE, 'IT_PROG', 5000);
-- Insert multiple rows
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1002, 'Jane', 'Doe');
-- Update records
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 50;
-- Delete records
DELETE FROM employees WHERE employee_id = 1001;
-- Truncate table (faster, no rollback)
TRUNCATE TABLE employees_backup;
3.3. Loop Insert in PL/SQL
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO test_records (id, description, created_date)
VALUES (i, 'Batch insert record ' || i, SYSDATE);
END LOOP;
COMMIT;
END;
/
3.4. Table Migration
Move a table to a different tablespace:
-- Move table to new tablespace
ALTER TABLE large_table MOVE TABLESPACE new_data_tbs NOLOGGING PARALLEL 4;
-- Compress table during move
ALTER TABLE large_table MOVE COMPRESS;
-- Rebuild indexes after table move
ALTER INDEX idx_primary REBUILD;
ALTER INDEX idx_foreign REBUILD;
3.5. Table Statistics Collection
-- Gather statistics for table and dependent objects
BEGIN
DBMS_STATS.gather_table_stats(
ownname => 'SCHEMA_NAME',
tabname => 'EMPLOYEES',
degree => 4,
cascade => TRUE,
estimate_percent => 30
);
END;
/
-- Check last statistics collection time
SELECT owner, table_name, num_rows, last_analyzed
FROM dba_tables
WHERE table_name = 'EMPLOYEES';
- Partitioned Tables
4.1. Benefits of Partitioning
- Availability: Failure in one partition doesn't affect others
- Maintainability: Repair only affected partitions
- I/O Balance: Map partitions to different disks
- Performance: Query only relevant partitions
4.2. Range Partitioning
Create a table partitioned by date ranges:
CREATE TABLE sales_data (
transaction_id NUMBER,
transaction_date DATE,
amount NUMBER(10,2),
region VARCHAR2(20)
)
PARTITION BY RANGE (transaction_date) (
PARTITION p_2024_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'))
TABLESPACE data_tbs,
PARTITION p_2024_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD'))
TABLESPACE data_tbs,
PARTITION p_2024_q3 VALUES LESS THAN (TO_DATE('2024-10-01', 'YYYY-MM-DD'))
TABLESPACE data_tbs,
PARTITION p_2024_q4 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
TABLESPACE data_tbs,
PARTITION p_future VALUES LESS THAN (MAXVALUE)
TABLESPACE data_tbs
);
4.3. Hash Partitioning
Create a table with hash partitioning for even data distribution:
CREATE TABLE customer_records (
customer_id NUMBER,
customer_name VARCHAR2(100),
email VARCHAR2(100),
created_date DATE
)
PARTITION BY HASH (customer_id) (
PARTITION p_hash_1 TABLESPACE data_tbs,
PARTITION p_hash_2 TABLESPACE data_tbs,
PARTITION p_hash_3 TABLESPACE data_tbs,
PARTITION p_hash_4 TABLESPACE data_tbs
);
4.4. List Partitioning
Create a table with list partitioning by region:
CREATE TABLE regional_sales (
sale_id NUMBER,
region VARCHAR2(20),
amount NUMBER(10,2)
)
PARTITION BY LIST (region) (
PARTITION p_north VALUES ('NORTH', 'NORTHEAST'),
PARTITION p_south VALUES ('SOUTH', 'SOUTHEAST'),
PARTITION p_west VALUES ('WEST', 'NORTHWEST'),
PARTITION p_central VALUES ('CENTRAL'),
PARTITION p_other VALUES (DEFAULT)
);
4.5. Partition Operations
-- Add new partition
ALTER TABLE sales_data ADD PARTITION p_2025_q1
VALUES LESS THAN (TO_DATE('2025-04-01', 'YYYY-MM-DD'));
-- Drop partition
ALTER TABLE sales_data DROP PARTITION p_2024_q1;
-- Truncate partition
ALTER TABLE sales_data TRUNCATE PARTITION p_2024_q2;
-- Merge adjacent partitions
ALTER TABLE sales_data MERGE PARTITIONS p_2024_q1, p_2024_q2
INTO PARTITION p_2024_first_half;
-- Split partition
ALTER TABLE sales_data SPLIT PARTITION p_2024_q2
AT (TO_DATE('2024-05-01', 'YYYY-MM-DD'))
INTO (PARTITION p_2024_q2a, PARTITION p_2024_q2b);
-- Coalesce hash partitions
ALTER TABLE customer_records COALESCE PARTITION;
-- Rename partition
ALTER TABLE sales_data RENAME PARTITION p_2024_q1 TO p_2024_jan_mar;
4.6. Querying Partition Information
-- Query specific partition
SELECT * FROM sales_data PARTITION (p_2024_q1);
-- Cross-partition query
SELECT COUNT(*) FROM sales_data PARTITION (p_2024_q1)
UNION ALL
SELECT COUNT(*) FROM sales_data PARTITION (p_2024_q2);
-- List all partitioned tables for user
SELECT table_name FROM user_part_tables;
-- View partition details
SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'SALES_DATA';
- View Operations
5.1. Creating Views
5.1.1. Simple View
CREATE VIEW active_employees_v AS
SELECT employee_id, first_name, last_name, job_id, department_id
FROM employees
WHERE hire_date > ADD_MONTHS(SYSDATE, -12);
5.1.2. Complex View with Aggregates
CREATE VIEW department_summary_v AS
SELECT d.department_name,
COUNT(e.employee_id) AS employee_count,
MIN(e.salary) AS min_salary,
MAX(e.salary) AS max_salary,
AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;
5.2. View Restrictions
Views with GROUP BY, DISTINCT, ROWNUM, or expressions cannot accept DML operations. Use WITH CHECK OPTION to enforce data integrity:
CREATE VIEW emp_dept_20_v AS
SELECT employee_id, first_name, salary, department_id
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT emp_dept_20_ck;
5.3. Dropping Views
DROP VIEW department_summary_v;
- Index Management
6.1. Creating Indexes
-- Basic B-tree index
CREATE INDEX idx_emp_lastname ON employees(last_name);
-- Composite index
CREATE INDEX idx_emp_dept_job ON employees(department_id, job_id);
-- Bitmap index
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
6.2. Index Rebuild and Maintenance
-- Rebuild index online
ALTER INDEX idx_emp_lastname REBUILD TABLESPACE index_tbs NOLOGGING ONLINE;
-- Coalesce index (defragment)
ALTER INDEX idx_emp_dept_job COALESCE;
-- Monitor index usage
ALTER INDEX idx_emp_lastname MONITORING USAGE;
-- Check usage statistics
SELECT * FROM v$object_usage;
-- Drop unused index
ALTER INDEX idx_emp_lastname NOMONITORING USAGE;
6.3. Partitioned Table Indexes
-- Local partitioned index
CREATE INDEX idx_sales_region ON sales_data(region)
LOCAL (
PARTITION idx_p1 TABLESPACE index_tbs,
PARTITION idx_p2 TABLESPACE index_tbs,
PARTITION idx_p3 TABLESPACE index_tbs
);
-- Global partitioned index
CREATE INDEX idx_sales_date ON sales_data(transaction_date)
GLOBAL PARTITION BY RANGE (transaction_date) (
PARTITION idx_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
PARTITION idx_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
PARTITION idx_future VALUES LESS THAN (MAXVALUE)
);
6.4. Dropping Indexes
DROP INDEX idx_emp_lastname;
DROP INDEX idx_emp_dept_job;
- Constraints
7.1. Constraint Types
- NOT NULL: Prevents NULL values
- UNIQUE: Ensures all values are unique
- PRIMARY KEY: Unique identifier, no NULLs
- FOREIGN KEY: References another table
- CHECK: Validates conditions
7.2. Creating Constraints
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER NOT NULL,
order_date DATE DEFAULT SYSDATE,
total_amount NUMBER(10,2),
status VARCHAR2(20) DEFAULT 'PENDING',
CONSTRAINT fk_customer FOREIGN KEY (customer_id)
REFERENCES customers(customer_id),
CONSTRAINT chk_status CHECK (status IN ('PENDING', 'SHIPPED', 'DELIVERED', 'CANCELLED')),
CONSTRAINT chk_amount CHECK (total_amount >= 0)
);
7.3. Adding Constraints to Existing Tables
-- Add foreign key constraint
ALTER TABLE order_items
ADD CONSTRAINT fk_order_product
FOREIGN KEY (product_id) REFERENCES products(product_id);
-- Add check constraint
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);
7.4. Enabling and Disabling Constraints
-- Disable constraint
ALTER TABLE orders DISABLE CONSTRAINT fk_customer;
-- Enable constraint
ALTER TABLE orders ENABLE CONSTRAINT fk_customer;
-- Drop constraint
ALTER TABLE orders DROP CONSTRAINT fk_customer;
7.5. Viewing Constraint Information
-- View constraint definitions
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'ORDERS';
-- View constraint columns
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'ORDERS';
- Stored Procedures and Functions
8.1. Creating Stored Procedures
CREATE OR REPLACE PROCEDURE update_employee_salary(
p_emp_id IN NUMBER,
p_new_sal IN NUMBER
) AS
v_old_sal NUMBER(8,2);
BEGIN
-- Get current salary
SELECT salary INTO v_old_sal
FROM employees
WHERE employee_id = p_emp_id;
-- Update salary
UPDATE employees
SET salary = p_new_sal
WHERE employee_id = p_emp_id;
DBMS_OUTPUT.put_line('Employee ' || p_emp_id ||
': Salary changed from ' || v_old_sal || ' to ' || p_new_sal);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('Employee not found: ' || p_emp_id);
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
ROLLBACK;
END;
/
8.2. Creating Functions
CREATE OR REPLACE FUNCTION get_employee_name(p_emp_id NUMBER)
RETURN VARCHAR2 AS
v_name VARCHAR2(100);
BEGIN
SELECT first_name || ' ' || last_name INTO v_name
FROM employees
WHERE employee_id = p_emp_id;
RETURN v_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END;
/
8.3. Parameter Modes
- IN: Passes value to procedure (default)
- OUT: Returns value to caller
- IN OUT: Both input and output
CREATE OR REPLACE PROCEDURE get_employee_stats(
p_emp_id IN NUMBER,
p_emp_name OUT VARCHAR2,
p_dept_name OUT VARCHAR2,
p_salary OUT NUMBER
) AS
BEGIN
SELECT e.first_name || ' ' || e.last_name,
d.department_name,
e.salary
INTO p_emp_name, p_dept_name, p_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id = p_emp_id;
END;
/
8.4. Executing Procedures
-- Execute in SQL*Plus
EXEC update_employee_salary(1001, 6500);
-- Execute in PL/SQL block
BEGIN
update_employee_salary(p_emp_id => 1001, p_new_sal => 7000);
END;
/
8.5. Dropping Procedures and Functions
DROP PROCEDURE update_employee_salary;
DROP FUNCTION get_employee_name;
- Packages
9.1. Package Structure
Packages consist of a specification (public interface) and body (implementation):
-- Package specification
CREATE OR REPLACE PACKAGE employee_pkg AS
PROCEDURE add_employee(
p_emp_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2
);
PROCEDURE remove_employee(p_emp_id IN NUMBER);
FUNCTION get_employee_count RETURN NUMBER;
END employee_pkg;
/
-- Package body
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
g_emp_count NUMBER := 0;
PROCEDURE add_employee(
p_emp_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2
) IS
BEGIN
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (p_emp_id, p_first_name, p_last_name, p_email);
g_emp_count := g_emp_count + 1;
COMMIT;
END add_employee;
PROCEDURE remove_employee(p_emp_id IN NUMBER) IS
BEGIN
DELETE FROM employees WHERE employee_id = p_emp_id;
g_emp_count := g_emp_count - 1;
COMMIT;
END remove_employee;
FUNCTION get_employee_count RETURN NUMBER IS
BEGIN
RETURN g_emp_count;
END get_employee_count;
END employee_pkg;
/
- Triggers
10.1. Creating Triggers
-- Row-level trigger
CREATE OR REPLACE TRIGGER trg_emp_salary_audit
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > 0)
BEGIN
-- Log salary changes
INSERT INTO salary_audit (
employee_id,
old_salary,
new_salary,
change_date,
changed_by
) VALUES (
:NEW.employee_id,
:OLD.salary,
:NEW.salary,
SYSDATE,
USER
);
END;
/
10.2. Trigger Types
- Statement triggers (execute once per statement)
- Row triggers (execute for each affected row)
- INSTEAD OF triggers (for views)
- System event triggers (logon, logoff, startup, shutdown)
- Database Jobs
11.1. Job Queue Configuration
-- Set job queue processes
ALTER SYSTEM SET job_queue_processes = 10;
11.2. Creating Jobs
DECLARE
v_job_id NUMBER;
BEGIN
DBMS_JOB.submit(
job => v_job_id,
what => 'BEGIN generate_daily_report; END;',
next_date => TRUNC(SYSDATE) + 1,
interval => 'TRUNC(SYSDATE) + 1'
);
COMMIT;
DBMS_OUTPUT.put_line('Job ID: ' || v_job_id);
END;
/
11.3. Managing Jobs
-- Run job immediately
BEGIN
DBMS_JOB.run(job_id);
END;
-- Remove job
BEGIN
DBMS_JOB.remove(job_id);
END;
-- Mark job as broken and restart
BEGIN
DBMS_JOB.broken(job_id, FALSE, NEXT_DATE => SYSDATE);
END;
11.4. Viewing Job Information
-- View scheduled jobs
SELECT job, what, next_date, interval, broken
FROM user_jobs;
-- View running jobs
SELECT j.job, s.sid, s.serial#, s.username
FROM dba_jobs_running j, v$session s
WHERE j.sid = s.sid;
- Synonyms
12.1. Creating Synonyms
-- Private synonym
CREATE SYNONYM emp_syn FOR employees;
-- Public synonym (requires privileges)
CREATE PUBLIC SYNONYM all_employees FOR schema.employees;
12.2. Dropping Synonyms
DROP SYNONYM emp_syn;
DROP PUBLIC SYNONYM all_employees;
- Database Links
13.1. Creatnig Database Links
-- Private database link
CREATE DATABASE LINK remote_db CONNECT TO remote_user
IDENTIFIED BY remote_password
USING 'remote_tns_entry';
-- Public database link
CREATE PUBLIC DATABASE LINK public_remote CONNECT TO remote_user
IDENTIFIED BY remote_password
USING '192.168.1.100:1521/orcl';
13.2. Using Database Links
-- Query remote table
SELECT * FROM employees@remote_db;
-- Insert via database link
INSERT INTO local_table (col1, col2)
SELECT col1, col2 FROM remote_table@remote_db;
13.3. Dropping Database Links
DROP DATABASE LINK remote_db;
DROP PUBLIC DATABASE LINK public_remote;
- Sequences
14.1. Creating Sequences
CREATE SEQUENCE emp_seq
INCREMENT BY 1
START WITH 1001
NOMAXVALUE
NOCYCLE
CACHE 20;
14.2. Using Sequences
-- Get next value
SELECT emp_seq.NEXTVAL FROM DUAL;
-- Get current value
SELECT emp_seq.CURRVAL FROM DUAL;
-- Use in INSERT
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe');
14.3. Modifying Sequences
ALTER SEQUENCE emp_seq
INCREMENT BY 10
MAXVALUE 999999
CYCLE
NOCACHE;
14.4. Dropping Sequences
DROP SEQUENCE emp_seq;
- Session Management
15.1. Killing Sessions
-- Kill session using SID and serial#
ALTER SYSTEM KILL SESSION '45,1234' IMMEDIATE;
15.2. Viewing Active Sessions
SELECT sid, serial#, username, status, program, machine
FROM v$session
WHERE username IS NOT NULL;
- Database Configuration
16.1. Viewing Connection Limits
-- View maximum processes
SELECT value FROM v$parameter WHERE name = 'processes';
-- View current connections
SELECT COUNT(*) FROM v$process;
SELECT COUNT(*) FROM v$session;
16.2. Modifying Connection Limits
-- Modify processes (requires restart)
ALTER SYSTEM SET processes = 300 SCOPE = SPFILE;
-- Modify sessions (automatic based on processes)
ALTER SYSTEM SET sessions = 335 SCOPE = SPFILE;
16.3. Managing Control Files
-- Backup control file to trace
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
16.4. Managing Redo Log Files
-- Switch logfile manually
ALTER SYSTEM SWITCH LOGFILE;
-- Force checkpoint
ALTER SYSTEM CHECKPOINT;
-- Add new logfile group
ALTER DATABASE ADD LOGFILE GROUP 4
('/u01/oradata/redo04a.log', '/u01/oradata/redo04b.log')
SIZE 100M;
- Initialization Parameters
17.1. Creating SPFILE from PFILE
CREATE SPFILE = '/u01/oradata/spfileorcl.ora'
FROM PFILE = '/u01/oradata/init.ora';
17.2. Creating PFILE from SPFILE
CREATE PFILE = '/u01/oradata/init.ora'
FROM SPFILE = '/u01/oradata/spfileorcl.ora';
17.3. Dynamic Parameter Modification
-- Modify parameter in memory only (immediate effect)
ALTER SYSTEM SET memory_target = 2G SCOPE = MEMORY;
-- Modify parameter in spfile only (effective after restart)
ALTER SYSTEM SET processes = 500 SCOPE = SPFILE;
-- Modify in both memory and spfile
ALTER SYSTEM SET job_queue_processes = 20 SCOPE = BOTH;
- Character Set Configuration
-- Check current character set
SELECT userenv('language') FROM DUAL;
SELECT nls_charset_name(to_number('0354', 'xxxx')) FROM DUAL;