The following steps outline the creation of a dedicated tablespace and a corresponding user account within an Oracle database.

-- Create a tablespace named GIS_DATA
CREATE TABLESPACE GIS_DATA
   DATAFILE 'C:\ORACLE\DATA\GIS_DATA01.DBF' SIZE 150M
   AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
   LOGGING
   EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   SEGMENT SPACE MANAGEMENT AUTO;

-- Create a user account for the application
CREATE USER APP_USER
   IDENTIFIED BY secure_pass123
   DEFAULT TABLESPACE GIS_DATA
   TEMPORARY TABLESPACE TEMP;

-- Assign standard roles
GRANT CONNECT, RESOURCE TO APP_USER;

-- Grant specific object privileges
GRANT CREATE SESSION,
      CREATE ANY TABLE,
      CREATE ANY VIEW,
      CREATE ANY INDEX,
      CREATE ANY PROCEDURE,
      ALTER ANY TABLE,
      ALTER ANY PROCEDURE,
      DROP ANY TABLE,
      DROP ANY VIEW,
      DROP ANY INDEX,
      DROP ANY PROCEDURE,
      SELECT ANY TABLE,
      INSERT ANY TABLE,
      UPDATE ANY TABLE,
      DELETE ANY TABLE
   TO APP_USER;

-- Grant system-level privileges
GRANT UNLIMITED TABLESPACE TO APP_USER;
GRANT CREATE ANY VIEW TO APP_USER;

Oracle Database Initialization Script

This batch script can be used to apply a set of common initialization parameters and security settings to an Oracle database instance. Execute with caution.

@ECHO OFF
SET DB_ADMIN=system
SET ADMIN_PWD=mgr_pass
SET SERVICE_NAME=prod_db

ECHO ================================================
ECHO Oracle Database Initialization Script
ECHO Ensure you have appropriate administrative rights
ECHO ================================================
PAUSE

:EXEC_SETUP
ECHO. > init_params.sql
ECHO CONNECT %DB_ADMIN%/%ADMIN_PWD%@%SERVICE_NAME% AS SYSDBA >> init_params.sql
REM Disable password case sensitivity
ECHO ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; >> init_params.sql
ECHO ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; >> init_params.sql
REM Ensure empty tables are included in exports for 11g
ECHO ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION = FALSE; >> init_params.sql
REM Increase open cursors limit
ECHO ALTER SYSTEM SET open_cursors=25000; >> init_params.sql
REM Modify the maximum number of processes/connections
ECHO ALTER SYSTEM SET processes=4000 SCOPE=SPFILE; >> init_params.sql
REM Disable auditing
ECHO TRUNCATE TABLE AUD$; >> init_params.sql
ECHO ALTER SYSTEM SET audit_trail=none SCOPE=SPFILE; >> init_params.sql
REM Force cursor sharing
ECHO ALTER SYSTEM SET cursor_sharing=force SCOPE=BOTH; >> init_params.sql

ECHO EXIT; >> init_params.sql
sqlplus /nolog @init_params.sql
DEL /Q init_params.sql
ECHO Initialization complete.
PAUSE

:END_SCRIPT

Removing Tablespaces and Associated Data Files

Different methods exist for dropping a tablespace, depending on whether it contains objects and if the physical files should be removed.

-- Drop an empty tablespace, keep the data file
DROP TABLESPACE data_archive;

-- Drop a non-empty tablespace, keep the data file
DROP TABLESPACE data_archive INCLUDING CONTENTS;

-- Drop an empty tablespace and delete its data file
DROP TABLESPACE data_archive INCLUDING DATAFILES;

-- Drop a non-empty tablespace and delete its data files
DROP TABLESPACE data_archive INCLUDING CONTENTS AND DATAFILES;

-- Use CASCADE CONSTRAINTS if foreign keys from other tablespaces reference this one
DROP TABLESPACE data_archive INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Complete User and Tablespace Cleanup

The following sequence, executed by a privileged user like SYSTEM, completely removes a user and their associated tablespace.

-- List all database users
SELECT * FROM DBA_USERS;

-- View data file locations for tablespaces
SELECT * FROM DBA_DATA_FILES;

-- Example: Remove user DEV_USER and their schema objects
DROP USER DEV_USER CASCADE;

-- Remove the DEV_TBS tablespace and its physical files
DROP TABLESPACE DEV_TBS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Important Notes:
To drop a tablespace, the user must possess the DROP TABLESPACE system privilege. If using the DATAFILES clause, it must be preceded by the INCLUDING CONTENTS keywords to avoid an ORA-01911 error.