Hello Oracle community:
I am working with an Autonomous Database and I have a script that performs various cleanup functions on the database. These actions include truncating tables, creating temporary tables, and then performing a DROP of these.
The script works correctly in terms of connecting to the database and performing some tasks. However, the problem arises when trying to execute certain actions that require specific permissions, such as creating temporary tables or performing a DROP. I am using the user ADMIN, but this does not have the necessary privileges for such operations, and the script fails.
#!/bin/bash # Variables TABLE_OWNER="SCHEMA_NAME" # Replace with the actual schema name TABLE_NAME="TABLE_NAME" # Replace with the actual table name LOG_FILE="/home/oracle/SCRIPTS/logs/purge_${TABLE_NAME}_$(date +%Y%m%d.%M%S).log" TEMP_TABLESPACE="TEMP_TABLESPACE_$(date +%s)" WALLET_PATH="/path/to/wallet" # Update with the actual wallet path DB_USER="DB_USER" # Replace with the database user DB_PASSWORD="DB_PASSWORD" # Do not share the actual password DB_CONNECTION_STRING="DB_ALIAS" # Connection alias to the database # Environment configuration export ORACLE_HOME="/path/to/oracle_home" # Update with the actual Oracle Home path export PATH=$ORACLE_HOME/bin:$PATH export TNS_ADMIN=$WALLET_PATH # Function to log messages log_message() { local message=$1 echo "$(date '+%Y-%m-%d %H:%M:%S') - $message" >> $LOG_FILE } log_space_usage() { local phase=$1 sqlplus -s "${DB_USER}/${DB_PASSWORD}@${DB_CONNECTION_STRING}" <<EOF >> $LOG_FILE SET FEEDBACK OFF SET HEADING OFF SET PAGESIZE 0 COLUMN space FORMAT A20 SELECT '${phase}: ' || ROUND(SUM(BYTES)/1024/1024,2) || ' MB' AS space FROM DBA_SEGMENTS WHERE OWNER='${TABLE_OWNER}' AND SEGMENT_NAME='${TABLE_NAME}'; EOF } log_lob_space_usage() { sqlplus -s "${DB_USER}/${DB_PASSWORD}@${DB_CONNECTION_STRING}" <<EOF >> $LOG_FILE SET FEEDBACK OFF SET HEADING OFF SET PAGESIZE 0 COLUMN space FORMAT A20 SELECT 'AFTER: ' || ROUND(SUM(BYTES)/1024/1024,2) || ' MB LOB' AS space FROM DBA_SEGMENTS WHERE OWNER='${TABLE_OWNER}' AND SEGMENT_NAME='${TABLE_NAME}' AND SEGMENT_TYPE='LOBSEGMENT'; EOF } log_tablespace_space_usage() { local tablespace=$1 sqlplus -s "${DB_USER}/${DB_PASSWORD}@${DB_CONNECTION_STRING}" <<EOF >> $LOG_FILE SET FEEDBACK OFF SET HEADING OFF SET PAGESIZE 0 COLUMN space FORMAT A20 SELECT 'AFTER: ' || ROUND(SUM(BYTES)/1024/1024,2) || ' MB' AS space FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='${tablespace}'; EOF } # Log start log_message "Starting reorganization for ${TABLE_OWNER}.${TABLE_NAME}" log_message "BEFORE: Space used by table ${TABLE_OWNER}.${TABLE_NAME}" log_space_usage "BEFORE" # Calculate required size for the temporary tablespace and create it sqlplus -s "${DB_USER}/${DB_PASSWORD}@${DB_CONNECTION_STRING}" <<EOF >> $LOG_FILE DECLARE v_table_size_mb NUMBER; v_temp_datafile VARCHAR2(512); BEGIN SELECT ROUND(SUM(BYTES)/1024/1024) INTO v_table_size_mb FROM DBA_SEGMENTS WHERE OWNER = '${TABLE_OWNER}' AND SEGMENT_NAME = '${TABLE_NAME}'; -- Ensure the size is at least 1GB IF v_table_size_mb < 1024 THEN v_table_size_mb := 1024; END IF; v_temp_datafile := '+DATA'; EXECUTE IMMEDIATE 'CREATE TABLESPACE ${TEMP_TABLESPACE} DATAFILE ''' || v_temp_datafile || ''' SIZE ' || v_table_size_mb || 'M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED'; END; / EOF # Grant necessary privileges sqlplus -s "${DB_USER}/${DB_PASSWORD}@${DB_CONNECTION_STRING}" <<EOF >> $LOG_FILE BEGIN FOR rec IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = '${TABLE_OWNER}') LOOP EXECUTE IMMEDIATE 'ALTER USER ' || rec.USERNAME || ' QUOTA UNLIMITED ON ${TEMP_TABLESPACE}'; END LOOP; END; / EOF # Truncate the table sqlplus -s "${DB_USER}/${DB_PASSWORD}@${DB_CONNECTION_STRING}" <<EOF >> $LOG_FILE -- Truncate the table to remove all data BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE ${TABLE_OWNER}.${TABLE_NAME}'; END; / EOF # Move LOBs to the temporary tablespace sqlplus -s "${DB_USER}/${DB_PASSWORD}@${DB_CONNECTION_STRING}" <<EOF >> $LOG_FILE DECLARE v_original_tablespace VARCHAR2(30); BEGIN SELECT TABLESPACE_NAME INTO v_original_tablespace FROM DBA_TABLES WHERE OWNER = '${TABLE_OWNER}' AND TABLE_NAME = '${TABLE_NAME}'; FOR rec IN (SELECT COLUMN_NAME FROM DBA_LOBS WHERE TABLE_NAME = '${TABLE_NAME}' AND OWNER = '${TABLE_OWNER}') LOOP EXECUTE IMMEDIATE 'ALTER TABLE ${TABLE_OWNER}.${TABLE_NAME} MOVE LOB (' || rec.COLUMN_NAME || ') STORE AS (TABLESPACE ${TEMP_TABLESPACE})'; END LOOP; END; / EOF # Move LOBs back to the original tablespace sqlplus -s "${DB_USER}/${DB_PASSWORD}@${DB_CONNECTION_STRING}" <<EOF >> $LOG_FILE DECLARE v_original_tablespace VARCHAR2(30); BEGIN SELECT TABLESPACE_NAME INTO v_original_tablespace FROM DBA_TABLES WHERE OWNER = '${TABLE_OWNER}' AND TABLE_NAME = '${TABLE_NAME}'; FOR rec IN (SELECT COLUMN_NAME FROM DBA_LOBS WHERE TABLE_NAME = '${TABLE_NAME}' AND OWNER = '${TABLE_OWNER}') LOOP EXECUTE IMMEDIATE 'ALTER TABLE ${TABLE_OWNER}.${TABLE_NAME} MOVE LOB (' || rec.COLUMN_NAME || ') STORE AS (TABLESPACE ' || v_original_tablespace || ')'; END LOOP; END; / EOF # Drop the temporary tablespace sqlplus -s "${DB_USER}/${DB_PASSWORD}@${DB_CONNECTION_STRING}" <<EOF >> $LOG_FILE BEGIN EXECUTE IMMEDIATE 'DROP TABLESPACE ${TEMP_TABLESPACE} INCLUDING CONTENTS AND DATAFILES'; END; / EOF # Log end log_message "AFTER: Space used by LOB segments for ${TABLE_OWNER}.${TABLE_NAME}" log_lob_space_usage log_message "AFTER: Space used in temporary tablespace ${TEMP_TABLESPACE}" log_tablespace_space_usage "${TEMP_TABLESPACE}" log_message "Reorganization of LOBs completed for ${TABLE_OWNER}.${TABLE_NAME}"
My question is:
- How can I grant or manage the necessary privileges so that the
ADMIN user can execute these actions within an Autonomous Database?
- Is it possible to configure these permissions without compromising the security or design of the autonomous database?
I would appreciate any guidance or recommendation to solve this issue.