Administrative SQLs

User account and privilege management (Access: DBA or specific user management privileges)

-- Create user
-- Access: DBA or users with CREATE USER privilege
CREATE USER new_user IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users
QUOTA 50M ON temp;

-- Grant privileges
-- Access: DBA or users with GRANT privilege
GRANT CONNECT, RESOURCE TO new_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO new_user;
GRANT CREATE SESSION TO new_user;

-- Create role
-- Access: DBA or users with CREATE ROLE privilege
CREATE ROLE hr_manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO hr_manager;
GRANT SELECT ON hr.departments TO hr_manager;

-- Grant role to user
-- Access: DBA or users with GRANT privilege
GRANT hr_manager TO new_user;

-- Revoke privileges
-- Access: DBA or users with REVOKE privilege
REVOKE DELETE ON hr.employees FROM new_user;

-- Alter user
-- Access: DBA or users with ALTER USER privilege
ALTER USER new_user IDENTIFIED BY new_password;
ALTER USER new_user QUOTA 200M ON users;
ALTER USER new_user ACCOUNT LOCK;
ALTER USER new_user ACCOUNT UNLOCK;

-- Drop user
-- Access: DBA or users with DROP USER privilege
DROP USER new_user CASCADE;

Tablespace creation, modification, and management (Access: DBA or tablespace management privileges)

-- Create tablespace
-- Access: DBA or users with CREATE TABLESPACE privilege
CREATE TABLESPACE hr_data
DATAFILE '/u01/oracle/data/hr_data01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

-- Add datafile to tablespace
-- Access: DBA or users with ALTER TABLESPACE privilege
ALTER TABLESPACE hr_data
ADD DATAFILE '/u01/oracle/data/hr_data02.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

-- Resize datafile
-- Access: DBA or users with ALTER DATABASE privilege
ALTER DATABASE
DATAFILE '/u01/oracle/data/hr_data01.dbf'
RESIZE 200M;

-- Set tablespace to read-only
-- Access: DBA or users with ALTER TABLESPACE privilege
ALTER TABLESPACE hr_data READ ONLY;

-- Set tablespace to read-write
-- Access: DBA or users with ALTER TABLESPACE privilege
ALTER TABLESPACE hr_data READ WRITE;

-- Drop tablespace
-- Access: DBA or users with DROP TABLESPACE privilege
DROP TABLESPACE hr_data INCLUDING CONTENTS AND DATAFILES;

-- Tablespace backup mode
-- Access: DBA or users with ALTER TABLESPACE privilege
ALTER TABLESPACE hr_data BEGIN BACKUP;
-- Perform backup operations
ALTER TABLESPACE hr_data END BACKUP;

Index and constraint management operations (Access: Appropriate table/index privileges)

-- Create index
-- Access: Users with CREATE INDEX privilege on table
CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_emp_name ON employees(last_name, first_name);
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- Create bitmap index
-- Access: Users with CREATE INDEX privilege on table
CREATE BITMAP INDEX idx_emp_dept_bitmap ON employees(department_id);

-- Create function-based index
-- Access: Users with CREATE INDEX privilege on table
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

-- Analyze index
-- Access: Users with ANALYZE privilege on table
ANALYZE INDEX idx_emp_dept COMPUTE STATISTICS;

-- Rebuild index
-- Access: Users with ALTER INDEX privilege
ALTER INDEX idx_emp_dept REBUILD;

-- Drop index
-- Access: Users with DROP INDEX privilege
DROP INDEX idx_emp_dept;

-- Add constraint
-- Access: Users with ALTER TABLE privilege
ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (emp_id);
ALTER TABLE employees ADD CONSTRAINT fk_emp_dept 
    FOREIGN KEY (department_id) REFERENCES departments(department_id);
ALTER TABLE employees ADD CONSTRAINT chk_salary 
    CHECK (salary > 0);

-- Drop constraint
-- Access: Users with ALTER TABLE privilege
ALTER TABLE employees DROP CONSTRAINT fk_emp_dept;

-- Enable/disable constraint
-- Access: Users with ALTER TABLE privilege
ALTER TABLE employees DISABLE CONSTRAINT fk_emp_dept;
ALTER TABLE employees ENABLE CONSTRAINT fk_emp_dept;