tablespace_management.sql
-- ------------------------------------------------------------------------------------- File Name : tablespace_management.sql-- Author : Pierre Montbleau-- Description : Oracle script for creating and managing tablespaces with automated sizing and monitoring-- Purpose : Standardize tablespace creation and maintenance across Oracle databases-- Call Syntax : @F:\DBA\Scripts\tablespace_management.sql (tablespace_name) (datafile_path) (initial_size_MB) (autoextend_size_MB)-- - With no parameters: displays all tablespaces-- - With 4 parameters: creates new tablespace-- Parameters : tablespace_name - Name of the tablespace to create-- datafile_path - Full path for the datafile including filename-- initial_size_MB - Initial size of the tablespace in megabytes-- autoextend_size_MB - Autoextend size in megabytes (0 for no autoextend)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set SQL*Plus environment-- -----------------------------------------------------------------------------------
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
CLEAR SCREEN
-- ------------------------------------------------------------------------------------- DECISION LOGIC: Check if parameters are provided-- -----------------------------------------------------------------------------------
PROMPT Tablespace Management UtilityPROMPT ==============================PROMPT
-- Check if first parameter existsCOLUMN param_count NEW_VALUE param_count NOPRINTSELECT COUNT(*) AS param_count FROM ( SELECT '&1' FROM DUAL WHERE '&1' IS NOT NULL AND '&1' != '');
DEFINE param_count = ¶m_count
-- Generate output filename based on whether we're creating or just queryingCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT CASE WHEN '&1' IS NOT NULL AND '&1' != '' THEN 'F:\DBA\Scripts\AI\Reports\DB_tablespace_management_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_CREATE_' || '&1' || '.log' ELSE 'F:\DBA\Scripts\AI\Reports\DB_tablespace_management_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_QUERY_ALL.log' END AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
-- Start spoolingSPOOL &output_filename
-- ------------------------------------------------------------------------------------- MODE 1: NO PARAMETERS - Display all tablespaces-- -----------------------------------------------------------------------------------
PROMPT STEP 1: MODE 1: NO PARAMETERS - Display all tablespaces
WHENEVER SQLERROR EXIT SQL.SQLCODE
DECLARE v_param_count NUMBER := ¶m_count;BEGIN IF v_param_count = 0 THEN DBMS_OUTPUT.PUT_LINE('No parameters provided. Displaying all tablespaces...'); DBMS_OUTPUT.PUT_LINE('To create a tablespace, provide all 4 parameters:'); DBMS_OUTPUT.PUT_LINE(' tablespace_name datafile_path initial_size_MB autoextend_size_MB'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Example: @tablespace_management.sql MY_TS /u01/app/oracle/oradata/MYDB/my_ts.dbf 100 50'); DBMS_OUTPUT.PUT_LINE(''); END IF;END;/
-- If no parameters, display all tablespaces and exitWHENEVER SQLERROR CONTINUE
SELECT 'MODE: QUERY ALL TABLESPACES' AS script_modeFROM DUALWHERE ¶m_count = 0;
-- Display all tablespaces summaryPROMPT PROMPT =============================================================================PROMPT STEP 2: ALL TABLESPACES SUMMARYPROMPT =============================================================================
COLUMN tablespace_name FORMAT A35COLUMN status FORMAT A10COLUMN contents FORMAT A10COLUMN extent_management FORMAT A8 HEADING "EXT_MGMT"COLUMN allocation_type FORMAT A12 HEADING "ALLOC_TYPE"COLUMN segment_space_mgmt FORMAT A6 HEADING "SEG_MGMT"COLUMN bigfile FORMAT A3 HEADING "BIG"
SELECT tablespace_name, status, contents, extent_management, allocation_type, segment_space_management AS segment_space_mgmt, bigfileFROM dba_tablespacesORDER BY tablespace_name;
-- Display tablespace sizes and usagePROMPT PROMPT =============================================================================PROMPT STEP 3: TABLESPACE STORAGE DETAILSPROMPT =============================================================================
COLUMN tablespace_name FORMAT A35COLUMN file_name FORMAT A65COLUMN total_mb FORMAT 999,999,999COLUMN used_mb FORMAT 999,999,999COLUMN free_mb FORMAT 999,999,999COLUMN pct_used FORMAT 990.99 HEADING "% USED"COLUMN max_mb FORMAT 999,999,999COLUMN autoextensible FORMAT A5 HEADING "AUTO"COLUMN increment_by FORMAT 999,999 HEADING "INC_MB"
WITH ts_usage AS ( SELECT df.tablespace_name, df.file_name, ROUND(df.bytes / 1024 / 1024) AS total_mb, ROUND((df.bytes - NVL(fs.bytes, 0)) / 1024 / 1024) AS used_mb, ROUND(NVL(fs.bytes, 0) / 1024 / 1024) AS free_mb, ROUND((df.bytes - NVL(fs.bytes, 0)) / df.bytes * 100, 2) AS pct_used, ROUND(df.maxbytes / 1024 / 1024) AS max_mb, df.autoextensible, ROUND(df.increment_by * 8 / 1024) AS increment_by -- Convert blocks to MB (assuming 8K blocks) FROM dba_data_files df LEFT JOIN ( SELECT file_id, SUM(bytes) AS bytes FROM dba_free_space GROUP BY file_id ) fs ON df.file_id = fs.file_id)SELECT tablespace_name, file_name, total_mb, used_mb, free_mb, pct_used, max_mb, autoextensible, increment_byFROM ts_usageORDER BY tablespace_name, file_name;
-- Summary statisticsPROMPT PROMPT =============================================================================PROMPT STEP 4: TABLESPACE USAGE SUMMARYPROMPT =============================================================================
COLUMN tablespace_name FORMAT A35COLUMN total_mb FORMAT 999,999,999COLUMN used_mb FORMAT 999,999,999COLUMN free_mb FORMAT 999,999,999COLUMN pct_used FORMAT 990.99 HEADING "% USED"COLUMN pct_free FORMAT 990.99 HEADING "% FREE"COLUMN status FORMAT A10
SELECT dt.tablespace_name, dt.status, ROUND(SUM(df.bytes) / 1024 / 1024) AS total_mb, ROUND((SUM(df.bytes) - SUM(NVL(fs.bytes, 0))) / 1024 / 1024) AS used_mb, ROUND(SUM(NVL(fs.bytes, 0)) / 1024 / 1024) AS free_mb, ROUND((SUM(df.bytes) - SUM(NVL(fs.bytes, 0))) / SUM(df.bytes) * 100, 2) AS pct_used, ROUND(SUM(NVL(fs.bytes, 0)) / SUM(df.bytes) * 100, 2) AS pct_freeFROM dba_tablespaces dtJOIN dba_data_files df ON dt.tablespace_name = df.tablespace_nameLEFT JOIN dba_free_space fs ON df.file_id = fs.file_idGROUP BY dt.tablespace_name, dt.statusORDER BY dt.tablespace_name;
-- Exit if no parameters were providedWHENEVER SQLERROR EXIT SQL.SQLCODE
BEGIN IF ¶m_count = 0 THEN DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Query completed successfully.'); DBMS_OUTPUT.PUT_LINE('Output saved to: &output_filename'); -- Clean exit for query mode return; END IF;END;/
-- ------------------------------------------------------------------------------------- MODE 2: CREATE TABLESPACE - All parameters provided-- -----------------------------------------------------------------------------------
PROMPT PROMPT =============================================================================PROMPT STEP 5: CREATE TABLESPACE MODEPROMPT =============================================================================
-- Check if we have all 4 parameters-- Check if we have all 4 parametersDECLARE v_param1 VARCHAR2(100) := '&1'; v_param2 VARCHAR2(100) := '&2'; v_param3 VARCHAR2(100) := '&3'; v_param4 VARCHAR2(100) := '&4'; v_missing_count NUMBER := 0;BEGIN -- Count missing parameters IF v_param1 IS NULL OR v_param1 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param2 IS NULL OR v_param2 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param3 IS NULL OR v_param3 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param4 IS NULL OR v_param4 = '' THEN v_missing_count := v_missing_count + 1; END IF; -- If all parameters are missing IF v_missing_count = 4 THEN DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('TABLESPACE CREATION SCRIPT'); DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('Usage: @script_name <tablespace_name> <datafile_path> <initial_size_MB> <autoextend_size_MB>'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Parameters:'); DBMS_OUTPUT.PUT_LINE(' 1. Tablespace Name : Name of the tablespace to create'); DBMS_OUTPUT.PUT_LINE(' 2. Datafile Path : Full path for the datafile'); DBMS_OUTPUT.PUT_LINE(' 3. Initial Size : Initial size in MB'); DBMS_OUTPUT.PUT_LINE(' 4. Autoextend Size : Autoextend size in MB (use 0 for no autoextend)'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Example:'); DBMS_OUTPUT.PUT_LINE(' @create_tablespace.sql MY_TBS /u01/oradata/MYDB/mytbs01.dbf 100 50'); DBMS_OUTPUT.PUT_LINE('=========================================='); RETURN; -- Exit without error END IF; -- Individual parameter validation IF v_param1 IS NULL OR v_param1 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Tablespace name parameter is required'); ELSIF v_param2 IS NULL OR v_param2 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Datafile path parameter is required'); ELSIF v_param3 IS NULL OR v_param3 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Initial size parameter is required'); ELSIF v_param4 IS NULL OR v_param4 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Autoextend size parameter is required (use 0 for no autoextend)'); END IF; DBMS_OUTPUT.PUT_LINE('Creating tablespace with parameters:'); DBMS_OUTPUT.PUT_LINE(' Tablespace Name: ' || v_param1); DBMS_OUTPUT.PUT_LINE(' Datafile Path: ' || v_param2); DBMS_OUTPUT.PUT_LINE(' Initial Size: ' || v_param3 || ' MB'); DBMS_OUTPUT.PUT_LINE(' Autoextend Size: ' || v_param4 || ' MB'); DBMS_OUTPUT.PUT_LINE('');END;/
-- Display parameter valuesPROMPT Parameters Received:PROMPT Tablespace Name: &1PROMPT Datafile Path: &2PROMPT Initial Size: &3 MBPROMPT Autoextend Size: &4 MBPROMPT
-- ------------------------------------------------------------------------------------- VALIDATION: Verify parameters and check for existing tablespace-- -----------------------------------------------------------------------------------
PROMPT PROMPT =============================================================================PROMPT STEP 6: VALIDATION: Verify parameters and check for existing tablespacePROMPT =============================================================================
PROMPT Validating parameters and checking for existing tablespace...
DECLARE v_tablespace_exists NUMBER; v_initial_size NUMBER := TO_NUMBER('&3'); v_autoextend_size NUMBER := TO_NUMBER('&4');BEGIN -- Check if tablespace already exists SELECT COUNT(*) INTO v_tablespace_exists FROM dba_tablespaces WHERE tablespace_name = UPPER('&1'); IF v_tablespace_exists > 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Tablespace ' || UPPER('&1') || ' already exists'); END IF; -- Validate size parameters IF v_initial_size < 1 THEN RAISE_APPLICATION_ERROR(-20002, 'Initial size must be at least 1 MB'); END IF; IF v_autoextend_size < 0 THEN RAISE_APPLICATION_ERROR(-20003, 'Autoextend size cannot be negative'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful'); EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter provided'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- TABLESPACE CREATION: Create tablespace with specified parameters-- -----------------------------------------------------------------------------------
PROMPT PROMPT =============================================================================PROMPT STEP 7: TABLESPACE CREATION: Create tablespace with specified parametersPROMPT =============================================================================
PROMPT Creating tablespace &1...
-- Check if we have all 4 parametersDECLARE v_param1 VARCHAR2(100) := '&1'; v_param2 VARCHAR2(100) := '&2'; v_param3 VARCHAR2(100) := '&3'; v_param4 VARCHAR2(100) := '&4'; v_missing_count NUMBER := 0;BEGIN -- Count missing parameters IF v_param1 IS NULL OR v_param1 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param2 IS NULL OR v_param2 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param3 IS NULL OR v_param3 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param4 IS NULL OR v_param4 = '' THEN v_missing_count := v_missing_count + 1; END IF; -- If all parameters are missing, display usage and exit IF v_missing_count = 4 THEN DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('TABLESPACE CREATION SCRIPT'); DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('Usage: @script_name <tablespace_name> <datafile_path> <initial_size_MB> <autoextend_size_MB>'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Parameters:'); DBMS_OUTPUT.PUT_LINE(' 1. Tablespace Name : Name of the tablespace to create'); DBMS_OUTPUT.PUT_LINE(' 2. Datafile Path : Full path for the datafile'); DBMS_OUTPUT.PUT_LINE(' 3. Initial Size : Initial size in MB (numeric)'); DBMS_OUTPUT.PUT_LINE(' 4. Autoextend Size : Autoextend size in MB (numeric, use 0 for no autoextend)'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Examples:'); DBMS_OUTPUT.PUT_LINE(' Create with autoextend:'); DBMS_OUTPUT.PUT_LINE(' @create_tbs.sql MY_TBS /u01/oradata/MYDB/mytbs01.dbf 100 50'); DBMS_OUTPUT.PUT_LINE(' Create without autoextend:'); DBMS_OUTPUT.PUT_LINE(' @create_tbs.sql MY_TBS2 /u01/oradata/MYDB/mytbs02.dbf 500 0'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Note: You must have CREATE TABLESPACE system privilege.'); DBMS_OUTPUT.PUT_LINE('=========================================='); RETURN; -- Exit gracefully without error END IF; -- Individual parameter validation IF v_param1 IS NULL OR v_param1 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Tablespace name parameter is required'); ELSIF v_param2 IS NULL OR v_param2 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Datafile path parameter is required'); ELSIF v_param3 IS NULL OR v_param3 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Initial size parameter is required'); ELSIF v_param4 IS NULL OR v_param4 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Autoextend size parameter is required (use 0 for no autoextend)'); END IF; DBMS_OUTPUT.PUT_LINE('Creating tablespace with parameters:'); DBMS_OUTPUT.PUT_LINE(' Tablespace Name: ' || v_param1); DBMS_OUTPUT.PUT_LINE(' Datafile Path: ' || v_param2); DBMS_OUTPUT.PUT_LINE(' Initial Size: ' || v_param3 || ' MB'); DBMS_OUTPUT.PUT_LINE(' Autoextend Size: ' || v_param4 || ' MB'); DBMS_OUTPUT.PUT_LINE('');END;/
PROMPT PROMPT =============================================================================PROMPT STEP 8: Now create the tablespacePROMPT =============================================================================
-- Now create the tablespace-- Check if we have all 4 parametersDECLARE v_param1 VARCHAR2(100) := '&1'; v_param2 VARCHAR2(100) := '&2'; v_param3 VARCHAR2(100) := '&3'; v_param4 VARCHAR2(100) := '&4'; v_missing_count NUMBER := 0; v_all_missing BOOLEAN := FALSE;BEGIN -- Count missing parameters IF v_param1 IS NULL OR v_param1 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param2 IS NULL OR v_param2 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param3 IS NULL OR v_param3 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param4 IS NULL OR v_param4 = '' THEN v_missing_count := v_missing_count + 1; END IF; -- If all parameters are missing, display usage and exit IF v_missing_count = 4 THEN v_all_missing := TRUE; DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('TABLESPACE CREATION SCRIPT'); DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('Usage: @script_name <tablespace_name> <datafile_path> <initial_size_MB> <autoextend_size_MB>'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Parameters:'); DBMS_OUTPUT.PUT_LINE(' 1. Tablespace Name : Name of the tablespace to create'); DBMS_OUTPUT.PUT_LINE(' 2. Datafile Path : Full path for the datafile'); DBMS_OUTPUT.PUT_LINE(' 3. Initial Size : Initial size in MB (numeric)'); DBMS_OUTPUT.PUT_LINE(' 4. Autoextend Size : Autoextend size in MB (numeric, use 0 for no autoextend)'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Examples:'); DBMS_OUTPUT.PUT_LINE(' Create with autoextend:'); DBMS_OUTPUT.PUT_LINE(' @create_tbs.sql MY_TBS /u01/oradata/MYDB/mytbs01.dbf 100 50'); DBMS_OUTPUT.PUT_LINE(' Create without autoextend:'); DBMS_OUTPUT.PUT_LINE(' @create_tbs.sql MY_TBS2 /u01/oradata/MYDB/mytbs02.dbf 500 0'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Note: You must have CREATE TABLESPACE system privilege.'); DBMS_OUTPUT.PUT_LINE('=========================================='); END IF; -- Individual parameter validation (only if not all missing) IF NOT v_all_missing THEN IF v_param1 IS NULL OR v_param1 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Tablespace name parameter is required'); ELSIF v_param2 IS NULL OR v_param2 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Datafile path parameter is required'); ELSIF v_param3 IS NULL OR v_param3 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Initial size parameter is required'); ELSIF v_param4 IS NULL OR v_param4 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Autoextend size parameter is required (use 0 for no autoextend)'); END IF; DBMS_OUTPUT.PUT_LINE('Creating tablespace with parameters:'); DBMS_OUTPUT.PUT_LINE(' Tablespace Name: ' || v_param1); DBMS_OUTPUT.PUT_LINE(' Datafile Path: ' || v_param2); DBMS_OUTPUT.PUT_LINE(' Initial Size: ' || v_param3 || ' MB'); DBMS_OUTPUT.PUT_LINE(' Autoextend Size: ' || v_param4 || ' MB'); DBMS_OUTPUT.PUT_LINE(''); END IF; -- Exit if all parameters were missing IF v_all_missing THEN RETURN; END IF;END;/
PROMPT PROMPT =============================================================================PROMPT STEP 9: Now create the tablespace (only execute if we have parameters)PROMPT =============================================================================
-- Now create the tablespace (only execute if we have parameters)DECLARE v_sql VARCHAR2(1000); v_initial_size NUMBER; v_autoextend_size NUMBER; v_param1 VARCHAR2(100) := '&1'; v_param3 VARCHAR2(100) := '&3'; v_param4 VARCHAR2(100) := '&4';BEGIN -- Skip if first parameter is empty (means all were missing) IF v_param1 IS NULL OR v_param1 = '' THEN RETURN; END IF; -- Convert parameters to numbers (will raise error if not numeric) BEGIN v_initial_size := TO_NUMBER('&3'); v_autoextend_size := TO_NUMBER('&4'); EXCEPTION WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR(-20001, 'Initial size and autoextend size must be numeric values'); END; -- Validate numeric parameters IF v_initial_size <= 0 THEN RAISE_APPLICATION_ERROR(-20002, 'Initial size must be greater than 0'); END IF; IF v_autoextend_size < 0 THEN RAISE_APPLICATION_ERROR(-20003, 'Autoextend size cannot be negative'); END IF; -- Construct dynamic SQL for tablespace creation v_sql := 'CREATE TABLESPACE &1 ' || 'DATAFILE ''&2'' ' || 'SIZE ' || v_initial_size || 'M '; -- Add autoextend clause if specified IF v_autoextend_size > 0 THEN v_sql := v_sql || 'AUTOEXTEND ON NEXT ' || v_autoextend_size || 'M '; ELSE v_sql := v_sql || 'AUTOEXTEND OFF '; END IF; v_sql := v_sql || 'EXTENT MANAGEMENT LOCAL ' || 'SEGMENT SPACE MANAGEMENT AUTO'; -- Execute the tablespace creation DBMS_OUTPUT.PUT_LINE('Executing: ' || REPLACE(v_sql, CHR(10), ' ')); EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Tablespace &1 created successfully'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error creating tablespace: ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('SQL: ' || REPLACE(v_sql, CHR(10), ' ')); RAISE;END;/
-- ------------------------------------------------------------------------------------- VERIFICATION: Confirm tablespace creation and display details-- -----------------------------------------------------------------------------------
PROMPT PROMPT =============================================================================PROMPT STEP 10: VERIFICATION: Confirm tablespace creation and display detailsPROMPT =============================================================================
PROMPT Verifying tablespace creation...
COLUMN tablespace_name FORMAT A20COLUMN file_name FORMAT A50COLUMN bytes FORMAT 999,999,999,999COLUMN autoextensible FORMAT A5COLUMN status FORMAT A10
SELECT dt.tablespace_name, df.file_name, df.bytes, df.autoextensible, df.increment_by, dt.statusFROM dba_tablespaces dtJOIN dba_data_files df ON dt.tablespace_name = df.tablespace_nameWHERE dt.tablespace_name = UPPER('&1');
-- ------------------------------------------------------------------------------------- MONITORING: Display tablespace usage information-- -----------------------------------------------------------------------------------
PROMPT PROMPT =============================================================================PROMPT STEP 11: MONITORING: Display tablespace usage informationPROMPT =============================================================================
PROMPT PROMPT Tablespace Usage Information:
COLUMN tablespace_name FORMAT A20COLUMN used_mb FORMAT 999,999,999COLUMN free_mb FORMAT 999,999,999COLUMN total_mb FORMAT 999,999,999COLUMN pct_used FORMAT 999.99
SELECT df.tablespace_name, ROUND(SUM(df.bytes) / 1024 / 1024) AS total_mb, ROUND(SUM(df.bytes) / 1024 / 1024 - NVL(SUM(fs.bytes) / 1024 / 1024, 0)) AS used_mb, ROUND(NVL(SUM(fs.bytes) / 1024 / 1024, 0)) AS free_mb, ROUND((SUM(df.bytes) / 1024 / 1024 - NVL(SUM(fs.bytes) / 1024 / 1024, 0)) / (SUM(df.bytes) / 1024 / 1024) * 100, 2) AS pct_usedFROM dba_data_files dfLEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_idWHERE df.tablespace_name = UPPER('&1')GROUP BY df.tablespace_name;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion message-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT Tablespace Management Script CompletedPROMPT ========================================PROMPT Tablespace &1 has been created successfullyPROMPT Location: &2PROMPT Initial Size: &3 MBPROMPT Autoextend: &4 MBPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
PROMPT Output saved to: &output_filename
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set SQL*Plus environment-- -----------------------------------------------------------------------------------
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
CLEAR SCREEN
-- ------------------------------------------------------------------------------------- DECISION LOGIC: Check if parameters are provided-- -----------------------------------------------------------------------------------
PROMPT Tablespace Management UtilityPROMPT ==============================PROMPT
-- Check if first parameter existsCOLUMN param_count NEW_VALUE param_count NOPRINTSELECT COUNT(*) AS param_count FROM ( SELECT '&1' FROM DUAL WHERE '&1' IS NOT NULL AND '&1' != '');
DEFINE param_count = ¶m_count
-- Generate output filename based on whether we're creating or just queryingCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT CASE WHEN '&1' IS NOT NULL AND '&1' != '' THEN 'F:\DBA\Scripts\AI\Reports\DB_tablespace_management_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_CREATE_' || '&1' || '.log' ELSE 'F:\DBA\Scripts\AI\Reports\DB_tablespace_management_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_QUERY_ALL.log' END AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
-- Start spoolingSPOOL &output_filename
-- ------------------------------------------------------------------------------------- MODE 1: NO PARAMETERS - Display all tablespaces-- -----------------------------------------------------------------------------------
PROMPT STEP 1: MODE 1: NO PARAMETERS - Display all tablespaces
WHENEVER SQLERROR EXIT SQL.SQLCODE
DECLARE v_param_count NUMBER := ¶m_count;BEGIN IF v_param_count = 0 THEN DBMS_OUTPUT.PUT_LINE('No parameters provided. Displaying all tablespaces...'); DBMS_OUTPUT.PUT_LINE('To create a tablespace, provide all 4 parameters:'); DBMS_OUTPUT.PUT_LINE(' tablespace_name datafile_path initial_size_MB autoextend_size_MB'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Example: @tablespace_management.sql MY_TS /u01/app/oracle/oradata/MYDB/my_ts.dbf 100 50'); DBMS_OUTPUT.PUT_LINE(''); END IF;END;/
-- If no parameters, display all tablespaces and exitWHENEVER SQLERROR CONTINUE
SELECT 'MODE: QUERY ALL TABLESPACES' AS script_modeFROM DUALWHERE ¶m_count = 0;
-- Display all tablespaces summaryPROMPT PROMPT =============================================================================PROMPT STEP 2: ALL TABLESPACES SUMMARYPROMPT =============================================================================
COLUMN tablespace_name FORMAT A35COLUMN status FORMAT A10COLUMN contents FORMAT A10COLUMN extent_management FORMAT A8 HEADING "EXT_MGMT"COLUMN allocation_type FORMAT A12 HEADING "ALLOC_TYPE"COLUMN segment_space_mgmt FORMAT A6 HEADING "SEG_MGMT"COLUMN bigfile FORMAT A3 HEADING "BIG"
SELECT tablespace_name, status, contents, extent_management, allocation_type, segment_space_management AS segment_space_mgmt, bigfileFROM dba_tablespacesORDER BY tablespace_name;
-- Display tablespace sizes and usagePROMPT PROMPT =============================================================================PROMPT STEP 3: TABLESPACE STORAGE DETAILSPROMPT =============================================================================
COLUMN tablespace_name FORMAT A35COLUMN file_name FORMAT A65COLUMN total_mb FORMAT 999,999,999COLUMN used_mb FORMAT 999,999,999COLUMN free_mb FORMAT 999,999,999COLUMN pct_used FORMAT 990.99 HEADING "% USED"COLUMN max_mb FORMAT 999,999,999COLUMN autoextensible FORMAT A5 HEADING "AUTO"COLUMN increment_by FORMAT 999,999 HEADING "INC_MB"
WITH ts_usage AS ( SELECT df.tablespace_name, df.file_name, ROUND(df.bytes / 1024 / 1024) AS total_mb, ROUND((df.bytes - NVL(fs.bytes, 0)) / 1024 / 1024) AS used_mb, ROUND(NVL(fs.bytes, 0) / 1024 / 1024) AS free_mb, ROUND((df.bytes - NVL(fs.bytes, 0)) / df.bytes * 100, 2) AS pct_used, ROUND(df.maxbytes / 1024 / 1024) AS max_mb, df.autoextensible, ROUND(df.increment_by * 8 / 1024) AS increment_by -- Convert blocks to MB (assuming 8K blocks) FROM dba_data_files df LEFT JOIN ( SELECT file_id, SUM(bytes) AS bytes FROM dba_free_space GROUP BY file_id ) fs ON df.file_id = fs.file_id)SELECT tablespace_name, file_name, total_mb, used_mb, free_mb, pct_used, max_mb, autoextensible, increment_byFROM ts_usageORDER BY tablespace_name, file_name;
-- Summary statisticsPROMPT PROMPT =============================================================================PROMPT STEP 4: TABLESPACE USAGE SUMMARYPROMPT =============================================================================
COLUMN tablespace_name FORMAT A35COLUMN total_mb FORMAT 999,999,999COLUMN used_mb FORMAT 999,999,999COLUMN free_mb FORMAT 999,999,999COLUMN pct_used FORMAT 990.99 HEADING "% USED"COLUMN pct_free FORMAT 990.99 HEADING "% FREE"COLUMN status FORMAT A10
SELECT dt.tablespace_name, dt.status, ROUND(SUM(df.bytes) / 1024 / 1024) AS total_mb, ROUND((SUM(df.bytes) - SUM(NVL(fs.bytes, 0))) / 1024 / 1024) AS used_mb, ROUND(SUM(NVL(fs.bytes, 0)) / 1024 / 1024) AS free_mb, ROUND((SUM(df.bytes) - SUM(NVL(fs.bytes, 0))) / SUM(df.bytes) * 100, 2) AS pct_used, ROUND(SUM(NVL(fs.bytes, 0)) / SUM(df.bytes) * 100, 2) AS pct_freeFROM dba_tablespaces dtJOIN dba_data_files df ON dt.tablespace_name = df.tablespace_nameLEFT JOIN dba_free_space fs ON df.file_id = fs.file_idGROUP BY dt.tablespace_name, dt.statusORDER BY dt.tablespace_name;
-- Exit if no parameters were providedWHENEVER SQLERROR EXIT SQL.SQLCODE
BEGIN IF ¶m_count = 0 THEN DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Query completed successfully.'); DBMS_OUTPUT.PUT_LINE('Output saved to: &output_filename'); -- Clean exit for query mode return; END IF;END;/
-- ------------------------------------------------------------------------------------- MODE 2: CREATE TABLESPACE - All parameters provided-- -----------------------------------------------------------------------------------
PROMPT PROMPT =============================================================================PROMPT STEP 5: CREATE TABLESPACE MODEPROMPT =============================================================================
-- Check if we have all 4 parameters-- Check if we have all 4 parametersDECLARE v_param1 VARCHAR2(100) := '&1'; v_param2 VARCHAR2(100) := '&2'; v_param3 VARCHAR2(100) := '&3'; v_param4 VARCHAR2(100) := '&4'; v_missing_count NUMBER := 0;BEGIN -- Count missing parameters IF v_param1 IS NULL OR v_param1 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param2 IS NULL OR v_param2 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param3 IS NULL OR v_param3 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param4 IS NULL OR v_param4 = '' THEN v_missing_count := v_missing_count + 1; END IF; -- If all parameters are missing IF v_missing_count = 4 THEN DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('TABLESPACE CREATION SCRIPT'); DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('Usage: @script_name <tablespace_name> <datafile_path> <initial_size_MB> <autoextend_size_MB>'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Parameters:'); DBMS_OUTPUT.PUT_LINE(' 1. Tablespace Name : Name of the tablespace to create'); DBMS_OUTPUT.PUT_LINE(' 2. Datafile Path : Full path for the datafile'); DBMS_OUTPUT.PUT_LINE(' 3. Initial Size : Initial size in MB'); DBMS_OUTPUT.PUT_LINE(' 4. Autoextend Size : Autoextend size in MB (use 0 for no autoextend)'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Example:'); DBMS_OUTPUT.PUT_LINE(' @create_tablespace.sql MY_TBS /u01/oradata/MYDB/mytbs01.dbf 100 50'); DBMS_OUTPUT.PUT_LINE('=========================================='); RETURN; -- Exit without error END IF; -- Individual parameter validation IF v_param1 IS NULL OR v_param1 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Tablespace name parameter is required'); ELSIF v_param2 IS NULL OR v_param2 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Datafile path parameter is required'); ELSIF v_param3 IS NULL OR v_param3 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Initial size parameter is required'); ELSIF v_param4 IS NULL OR v_param4 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Autoextend size parameter is required (use 0 for no autoextend)'); END IF; DBMS_OUTPUT.PUT_LINE('Creating tablespace with parameters:'); DBMS_OUTPUT.PUT_LINE(' Tablespace Name: ' || v_param1); DBMS_OUTPUT.PUT_LINE(' Datafile Path: ' || v_param2); DBMS_OUTPUT.PUT_LINE(' Initial Size: ' || v_param3 || ' MB'); DBMS_OUTPUT.PUT_LINE(' Autoextend Size: ' || v_param4 || ' MB'); DBMS_OUTPUT.PUT_LINE('');END;/
-- Display parameter valuesPROMPT Parameters Received:PROMPT Tablespace Name: &1PROMPT Datafile Path: &2PROMPT Initial Size: &3 MBPROMPT Autoextend Size: &4 MBPROMPT
-- ------------------------------------------------------------------------------------- VALIDATION: Verify parameters and check for existing tablespace-- -----------------------------------------------------------------------------------
PROMPT PROMPT =============================================================================PROMPT STEP 6: VALIDATION: Verify parameters and check for existing tablespacePROMPT =============================================================================
PROMPT Validating parameters and checking for existing tablespace...
DECLARE v_tablespace_exists NUMBER; v_initial_size NUMBER := TO_NUMBER('&3'); v_autoextend_size NUMBER := TO_NUMBER('&4');BEGIN -- Check if tablespace already exists SELECT COUNT(*) INTO v_tablespace_exists FROM dba_tablespaces WHERE tablespace_name = UPPER('&1'); IF v_tablespace_exists > 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Tablespace ' || UPPER('&1') || ' already exists'); END IF; -- Validate size parameters IF v_initial_size < 1 THEN RAISE_APPLICATION_ERROR(-20002, 'Initial size must be at least 1 MB'); END IF; IF v_autoextend_size < 0 THEN RAISE_APPLICATION_ERROR(-20003, 'Autoextend size cannot be negative'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful'); EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter provided'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- TABLESPACE CREATION: Create tablespace with specified parameters-- -----------------------------------------------------------------------------------
PROMPT PROMPT =============================================================================PROMPT STEP 7: TABLESPACE CREATION: Create tablespace with specified parametersPROMPT =============================================================================
PROMPT Creating tablespace &1...
-- Check if we have all 4 parametersDECLARE v_param1 VARCHAR2(100) := '&1'; v_param2 VARCHAR2(100) := '&2'; v_param3 VARCHAR2(100) := '&3'; v_param4 VARCHAR2(100) := '&4'; v_missing_count NUMBER := 0;BEGIN -- Count missing parameters IF v_param1 IS NULL OR v_param1 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param2 IS NULL OR v_param2 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param3 IS NULL OR v_param3 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param4 IS NULL OR v_param4 = '' THEN v_missing_count := v_missing_count + 1; END IF; -- If all parameters are missing, display usage and exit IF v_missing_count = 4 THEN DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('TABLESPACE CREATION SCRIPT'); DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('Usage: @script_name <tablespace_name> <datafile_path> <initial_size_MB> <autoextend_size_MB>'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Parameters:'); DBMS_OUTPUT.PUT_LINE(' 1. Tablespace Name : Name of the tablespace to create'); DBMS_OUTPUT.PUT_LINE(' 2. Datafile Path : Full path for the datafile'); DBMS_OUTPUT.PUT_LINE(' 3. Initial Size : Initial size in MB (numeric)'); DBMS_OUTPUT.PUT_LINE(' 4. Autoextend Size : Autoextend size in MB (numeric, use 0 for no autoextend)'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Examples:'); DBMS_OUTPUT.PUT_LINE(' Create with autoextend:'); DBMS_OUTPUT.PUT_LINE(' @create_tbs.sql MY_TBS /u01/oradata/MYDB/mytbs01.dbf 100 50'); DBMS_OUTPUT.PUT_LINE(' Create without autoextend:'); DBMS_OUTPUT.PUT_LINE(' @create_tbs.sql MY_TBS2 /u01/oradata/MYDB/mytbs02.dbf 500 0'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Note: You must have CREATE TABLESPACE system privilege.'); DBMS_OUTPUT.PUT_LINE('=========================================='); RETURN; -- Exit gracefully without error END IF; -- Individual parameter validation IF v_param1 IS NULL OR v_param1 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Tablespace name parameter is required'); ELSIF v_param2 IS NULL OR v_param2 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Datafile path parameter is required'); ELSIF v_param3 IS NULL OR v_param3 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Initial size parameter is required'); ELSIF v_param4 IS NULL OR v_param4 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Autoextend size parameter is required (use 0 for no autoextend)'); END IF; DBMS_OUTPUT.PUT_LINE('Creating tablespace with parameters:'); DBMS_OUTPUT.PUT_LINE(' Tablespace Name: ' || v_param1); DBMS_OUTPUT.PUT_LINE(' Datafile Path: ' || v_param2); DBMS_OUTPUT.PUT_LINE(' Initial Size: ' || v_param3 || ' MB'); DBMS_OUTPUT.PUT_LINE(' Autoextend Size: ' || v_param4 || ' MB'); DBMS_OUTPUT.PUT_LINE('');END;/
PROMPT PROMPT =============================================================================PROMPT STEP 8: Now create the tablespacePROMPT =============================================================================
-- Now create the tablespace-- Check if we have all 4 parametersDECLARE v_param1 VARCHAR2(100) := '&1'; v_param2 VARCHAR2(100) := '&2'; v_param3 VARCHAR2(100) := '&3'; v_param4 VARCHAR2(100) := '&4'; v_missing_count NUMBER := 0; v_all_missing BOOLEAN := FALSE;BEGIN -- Count missing parameters IF v_param1 IS NULL OR v_param1 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param2 IS NULL OR v_param2 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param3 IS NULL OR v_param3 = '' THEN v_missing_count := v_missing_count + 1; END IF; IF v_param4 IS NULL OR v_param4 = '' THEN v_missing_count := v_missing_count + 1; END IF; -- If all parameters are missing, display usage and exit IF v_missing_count = 4 THEN v_all_missing := TRUE; DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('TABLESPACE CREATION SCRIPT'); DBMS_OUTPUT.PUT_LINE('=========================================='); DBMS_OUTPUT.PUT_LINE('Usage: @script_name <tablespace_name> <datafile_path> <initial_size_MB> <autoextend_size_MB>'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Parameters:'); DBMS_OUTPUT.PUT_LINE(' 1. Tablespace Name : Name of the tablespace to create'); DBMS_OUTPUT.PUT_LINE(' 2. Datafile Path : Full path for the datafile'); DBMS_OUTPUT.PUT_LINE(' 3. Initial Size : Initial size in MB (numeric)'); DBMS_OUTPUT.PUT_LINE(' 4. Autoextend Size : Autoextend size in MB (numeric, use 0 for no autoextend)'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Examples:'); DBMS_OUTPUT.PUT_LINE(' Create with autoextend:'); DBMS_OUTPUT.PUT_LINE(' @create_tbs.sql MY_TBS /u01/oradata/MYDB/mytbs01.dbf 100 50'); DBMS_OUTPUT.PUT_LINE(' Create without autoextend:'); DBMS_OUTPUT.PUT_LINE(' @create_tbs.sql MY_TBS2 /u01/oradata/MYDB/mytbs02.dbf 500 0'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('Note: You must have CREATE TABLESPACE system privilege.'); DBMS_OUTPUT.PUT_LINE('=========================================='); END IF; -- Individual parameter validation (only if not all missing) IF NOT v_all_missing THEN IF v_param1 IS NULL OR v_param1 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Tablespace name parameter is required'); ELSIF v_param2 IS NULL OR v_param2 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Datafile path parameter is required'); ELSIF v_param3 IS NULL OR v_param3 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Initial size parameter is required'); ELSIF v_param4 IS NULL OR v_param4 = '' THEN RAISE_APPLICATION_ERROR(-20000, 'Autoextend size parameter is required (use 0 for no autoextend)'); END IF; DBMS_OUTPUT.PUT_LINE('Creating tablespace with parameters:'); DBMS_OUTPUT.PUT_LINE(' Tablespace Name: ' || v_param1); DBMS_OUTPUT.PUT_LINE(' Datafile Path: ' || v_param2); DBMS_OUTPUT.PUT_LINE(' Initial Size: ' || v_param3 || ' MB'); DBMS_OUTPUT.PUT_LINE(' Autoextend Size: ' || v_param4 || ' MB'); DBMS_OUTPUT.PUT_LINE(''); END IF; -- Exit if all parameters were missing IF v_all_missing THEN RETURN; END IF;END;/
PROMPT PROMPT =============================================================================PROMPT STEP 9: Now create the tablespace (only execute if we have parameters)PROMPT =============================================================================
-- Now create the tablespace (only execute if we have parameters)DECLARE v_sql VARCHAR2(1000); v_initial_size NUMBER; v_autoextend_size NUMBER; v_param1 VARCHAR2(100) := '&1'; v_param3 VARCHAR2(100) := '&3'; v_param4 VARCHAR2(100) := '&4';BEGIN -- Skip if first parameter is empty (means all were missing) IF v_param1 IS NULL OR v_param1 = '' THEN RETURN; END IF; -- Convert parameters to numbers (will raise error if not numeric) BEGIN v_initial_size := TO_NUMBER('&3'); v_autoextend_size := TO_NUMBER('&4'); EXCEPTION WHEN VALUE_ERROR THEN RAISE_APPLICATION_ERROR(-20001, 'Initial size and autoextend size must be numeric values'); END; -- Validate numeric parameters IF v_initial_size <= 0 THEN RAISE_APPLICATION_ERROR(-20002, 'Initial size must be greater than 0'); END IF; IF v_autoextend_size < 0 THEN RAISE_APPLICATION_ERROR(-20003, 'Autoextend size cannot be negative'); END IF; -- Construct dynamic SQL for tablespace creation v_sql := 'CREATE TABLESPACE &1 ' || 'DATAFILE ''&2'' ' || 'SIZE ' || v_initial_size || 'M '; -- Add autoextend clause if specified IF v_autoextend_size > 0 THEN v_sql := v_sql || 'AUTOEXTEND ON NEXT ' || v_autoextend_size || 'M '; ELSE v_sql := v_sql || 'AUTOEXTEND OFF '; END IF; v_sql := v_sql || 'EXTENT MANAGEMENT LOCAL ' || 'SEGMENT SPACE MANAGEMENT AUTO'; -- Execute the tablespace creation DBMS_OUTPUT.PUT_LINE('Executing: ' || REPLACE(v_sql, CHR(10), ' ')); EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE('Tablespace &1 created successfully'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error creating tablespace: ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('SQL: ' || REPLACE(v_sql, CHR(10), ' ')); RAISE;END;/
-- ------------------------------------------------------------------------------------- VERIFICATION: Confirm tablespace creation and display details-- -----------------------------------------------------------------------------------
PROMPT PROMPT =============================================================================PROMPT STEP 10: VERIFICATION: Confirm tablespace creation and display detailsPROMPT =============================================================================
PROMPT Verifying tablespace creation...
COLUMN tablespace_name FORMAT A20COLUMN file_name FORMAT A50COLUMN bytes FORMAT 999,999,999,999COLUMN autoextensible FORMAT A5COLUMN status FORMAT A10
SELECT dt.tablespace_name, df.file_name, df.bytes, df.autoextensible, df.increment_by, dt.statusFROM dba_tablespaces dtJOIN dba_data_files df ON dt.tablespace_name = df.tablespace_nameWHERE dt.tablespace_name = UPPER('&1');
-- ------------------------------------------------------------------------------------- MONITORING: Display tablespace usage information-- -----------------------------------------------------------------------------------
PROMPT PROMPT =============================================================================PROMPT STEP 11: MONITORING: Display tablespace usage informationPROMPT =============================================================================
PROMPT PROMPT Tablespace Usage Information:
COLUMN tablespace_name FORMAT A20COLUMN used_mb FORMAT 999,999,999COLUMN free_mb FORMAT 999,999,999COLUMN total_mb FORMAT 999,999,999COLUMN pct_used FORMAT 999.99
SELECT df.tablespace_name, ROUND(SUM(df.bytes) / 1024 / 1024) AS total_mb, ROUND(SUM(df.bytes) / 1024 / 1024 - NVL(SUM(fs.bytes) / 1024 / 1024, 0)) AS used_mb, ROUND(NVL(SUM(fs.bytes) / 1024 / 1024, 0)) AS free_mb, ROUND((SUM(df.bytes) / 1024 / 1024 - NVL(SUM(fs.bytes) / 1024 / 1024, 0)) / (SUM(df.bytes) / 1024 / 1024) * 100, 2) AS pct_usedFROM dba_data_files dfLEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_idWHERE df.tablespace_name = UPPER('&1')GROUP BY df.tablespace_name;
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion message-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT Tablespace Management Script CompletedPROMPT ========================================PROMPT Tablespace &1 has been created successfullyPROMPT Location: &2PROMPT Initial Size: &3 MBPROMPT Autoextend: &4 MBPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
PROMPT Output saved to: &output_filename
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------