Partitioning and Storage

Table partitioning operations and management (Access: Table management privileges)

-- Create partitioned table
-- Access: Users with CREATE TABLE privilege
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    customer_id NUMBER,
    amount NUMBER(10,2)
) PARTITION BY RANGE (sale_date) (
    PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

-- Create hash partitioned table
-- Access: Users with CREATE TABLE privilege
CREATE TABLE customers (
    customer_id NUMBER,
    customer_name VARCHAR2(100),
    email VARCHAR2(100)
) PARTITION BY HASH (customer_id) PARTITIONS 4;

-- Create list partitioned table
-- Access: Users with CREATE TABLE privilege
CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    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_east VALUES ('EAST', 'SOUTHEAST')
);

-- Add partition
-- Access: Users with ALTER TABLE privilege
ALTER TABLE sales ADD PARTITION p2025 VALUES LESS THAN (DATE '2026-01-01');

-- Drop partition
-- Access: Users with ALTER TABLE privilege
ALTER TABLE sales DROP PARTITION p2023;

-- Truncate partition
-- Access: Users with ALTER TABLE privilege
ALTER TABLE sales TRUNCATE PARTITION p2024;

-- Split partition
-- Access: Users with ALTER TABLE privilege
ALTER TABLE sales SPLIT PARTITION p_future AT (DATE '2025-01-01')
INTO (PARTITION p2025, PARTITION p_future);

-- Merge partitions
-- Access: Users with ALTER TABLE privilege
ALTER TABLE sales MERGE PARTITIONS p2023, p2024 INTO p_combined;

-- Exchange partition
-- Access: Users with ALTER TABLE privilege
ALTER TABLE sales EXCHANGE PARTITION p2024 WITH TABLE sales_staging;

-- Move partition
-- Access: Users with ALTER TABLE privilege
ALTER TABLE sales MOVE PARTITION p2024 TABLESPACE users_new;

-- Partition information
-- Access: DBA or users with DBA_TAB_PARTITIONS privilege
SELECT 
    table_name,
    partition_name,
    tablespace_name,
    num_rows,
    blocks,
    last_analyzed
FROM dba_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_name;

Index partitioning operations and management (Access: Index management privileges)

-- Create partitioned index
-- Access: Users with CREATE INDEX privilege
CREATE INDEX idx_sales_date ON sales (sale_date)
LOCAL (
    PARTITION p2023,
    PARTITION p2024,
    PARTITION p_future
);

-- Create global partitioned index
-- Access: Users with CREATE INDEX privilege
CREATE INDEX idx_sales_customer ON sales (customer_id)
GLOBAL PARTITION BY RANGE (sale_date) (
    PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

-- Create bitmap index on partitioned table
-- Access: Users with CREATE INDEX privilege
CREATE BITMAP INDEX idx_sales_region ON sales (region)
LOCAL;

-- Rebuild partitioned index
-- Access: Users with ALTER INDEX privilege
ALTER INDEX idx_sales_date REBUILD PARTITION p2024;

-- Rebuild all partitions
-- Access: Users with ALTER INDEX privilege
ALTER INDEX idx_sales_date REBUILD;

-- Drop index partition
-- Access: Users with ALTER INDEX privilege
ALTER INDEX idx_sales_date DROP PARTITION p2023;

-- Split index partition
-- Access: Users with ALTER INDEX privilege
ALTER INDEX idx_sales_date SPLIT PARTITION p_future AT (DATE '2025-01-01')
INTO (PARTITION p2025, PARTITION p_future);

-- Index partition information
-- Access: DBA or users with DBA_IND_PARTITIONS privilege
SELECT 
    index_name,
    partition_name,
    tablespace_name,
    status,
    last_analyzed
FROM dba_ind_partitions
WHERE index_name = 'IDX_SALES_DATE'
ORDER BY partition_name;