select 1 from dual;
def get_indexes(object_type, owner, object_name):
sql_cmd = """
SELECT ind.index_name, ind.index_type, col.column_name, col.column_position
FROM dba_indexes ind
JOIN dba_ind_columns col ON ind.index_name = col.index_name AND ind.owner = col.index_owner
WHERE ind.table_name = '{object_name}'
AND ind.owner = '{owner}'
ORDER BY ind.index_name, col.column_position
""".format(owner=owner, object_name=object_name)
print(sql_cmd)
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`.
-- Check if the virtual column exists, and drop it if it does
DECLARE
l_count NUMBER;
BEGIN
SELECT COUNT(*) INTO l_count
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'SNC_GRU_LYNX_RECON'
AND COLUMN_NAME = 'LYNX_FACILITY_PORTFOLIO_ID';
IF l_count > 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE DGLOBE.SNC_GRU_LYNX_RECON DROP COLUMN LYNX_FACILITY_PORTFOLIO_ID';
END IF;
END;
/
-- Create virtual column
ALTER TABLE DGLOBE.SNC_GRU_LYNX_RECON
ADD LYNX_FACILITY_PORTFOLIO_ID GENERATED ALWAYS AS (FACILITY_ID || '-' || PORTFOLIO_ID) VIRTUAL;
/
-- Check if the index exists, and drop it if it does
DECLARE
l_index_count NUMBER;
BEGIN
SELECT COUNT(*) INTO l_index_count
FROM USER_INDEXES
WHERE INDEX_NAME = 'IDX_SNC_GRU_LYNX_RECON_COMPOSITE';
IF l_index_count > 0 THEN
EXECUTE IMMEDIATE 'DROP INDEX IDX_SNC_GRU_LYNX_RECON_COMPOSITE';
END IF;
END;
/
-- Create index on the table
CREATE INDEX IDX_SNC_GRU_LYNX_RECON_COMPOSITE ON DGLOBE.SNC_GRU_LYNX_RECON
("LOADID", "FIELD", "FACILITY_ID", "PORTFOLIO_ID", "LYNX_FACILITY_PORTFOLIO_ID", "FACILITY_STATUS", "CREDIT_AGREEMENT_STATUS_AVAILABILITY_STATUS_DESC") LOCAL;
/
COMMIT;
BEGIN
FOR r IN (SELECT COLUMN_NAME
FROM ALL_TAB_COLS
WHERE TABLE_NAME = 'STG_SNC_3T_AGENT_INVESTOR'
AND OWNER = 'DGSTAGE'
AND COLUMN_NAME LIKE 'SYS_COL%'
AND VIRTUAL_COLUMN = 'NO')
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE DGSTAGE.STG_SNC_3T_AGENT_INVESTOR DROP COLUMN ' || r.COLUMN_NAME;
END LOOP;
END;
/
COMMIT;