If you've used `ALTER TABLE DROP UNUSED COLUMNS` and the columns still appear in `ALL_TAB_COLS`, it suggests that the unused columns might not have been properly marked or dropped. Here’s a comprehensive approach to ensure that the columns are completely removed from the table:
### Step-by-Step Guide
1. **Check Current Unused Columns**: Verify the current state of the columns to see if they are marked as unused.
```sql
SELECT column_name, column_id, hidden_column
FROM all_tab_cols
WHERE table_name = 'TDS_DASHBOARD_SNAPSHOT'
AND owner = 'DGLOBE'
AND column_name LIKE 'SYS_%';
```
2. **Drop Unused Columns Again**: Run the `DROP UNUSED COLUMNS` command with checkpointing to ensure that all unused columns are dropped properly.
```sql
ALTER TABLE DGLOBE.TDS_DASHBOARD_SNAPSHOT DROP UNUSED COLUMNS CHECKPOINT 1000;
```
### Alternative: Using DBMS_REDEFINITION
If the columns persist, you can use the `DBMS_REDEFINITION` package to reorganize the table, which can help remove any lingering unused columns.
#### Step 1: Start the Redefinition Process
Create an interim table that will serve as a copy of the original table during the redefinition process.
```sql
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'DGLOBE',
orig_table => 'TDS_DASHBOARD_SNAPSHOT',
int_table => 'TDS_DASHBOARD_SNAPSHOT_INT'
);
END;
/
```
#### Step 2: Copy Dependent Objects
Copy indexes, triggers, constraints, and other dependent objects from the original table to the interim table.
```sql
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => 'DGLOBE',
orig_table => 'TDS_DASHBOARD_SNAPSHOT',
int_table => 'TDS_DASHBOARD_SNAPSHOT_INT',
num_errors => num_errors,
copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => FALSE
);
END;
/
```
#### Step 3: Complete the Redefinition Process
Finish the redefinition process to switch the interim table with the original table.
```sql
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'DGLOBE',
orig_table => 'TDS_DASHBOARD_SNAPSHOT',
int_table => 'TDS_DASHBOARD_SNAPSHOT_INT'
);
END;
/
```
#### Step 4: Drop the Interim Table
If everything is correct and you no longer need the interim table, you can drop it.
```sql
DROP TABLE DGLOBE.TDS_DASHBOARD_SNAPSHOT_INT;
```
### Verify the Cleanup
After performing the above steps, verify the columns again to ensure they have been removed:
```sql
SELECT column_name, column_id, hidden_column
FROM all_tab_cols
WHERE table_name = 'TDS_DASHBOARD_SNAPSHOT'
AND owner = 'DGLOBE'
AND column_name LIKE 'SYS_%';
```
### Considerations
- **Backup**: Always ensure you have a backup before performing operations that modify the structure of your tables.
- **Permissions**: Ensure you have the necessary privileges to use `DBMS_REDEFINITION` and to alter table structures.
- **Testing**: Perform these operations in a test environment before applying them to production to ensure they work as expected.
By following these steps, you should be able to completely remove the `SYS_` columns from your table and clean up the table structure in `ALL_TAB_COLS`.