Oracle Schema Detection Issue

[vuongprogk]

Issue Summary

VS Code DbCode extension fails to detect default schema despite successful database connection and valid schema existence.

Environment Details

• Database Type: Oracle
• Oracle Version: 19.0.0.0.0
• Connection Mode: Thin mode
• Database Service: ORCL
• Schema/Username: SYSTEM

Error Messages

2025-10-14 10:24:48.319 [info] Oracle: Connection 'SYSTEM' will use Thin mode
2025-10-14 10:24:48.397 [info] Connection: opened 'ORCL' on 'SYSTEM' (oracle:19.0.0.0.0)
2025-10-14 10:24:48.584 [info] Oracle: Default schema 'SYSTEM' not found, using 'undefined' on 'SYSTEM'
2025-10-14 10:24:48.688 [info] Oracle: Default schema 'undefined' not found, using 'undefined' on 'SYSTEM'

[mikeburgh]

Thanks for the detail, should be fixed in 1.17.16, can you install it and let me know if it works now ?

[vuongprogk]

Now, it work but take alot of time to connect, as my case it take more than 10 minutes without result
I think the most reducing the response is query types in oracle and I do some result that query base on user and predefine some type
SELECT DISTINCT DATA_TYPE
FROM USER_TAB_COLUMNS
UNION
SELECT DISTINCT TYPE_NAME
FROM USER_TYPES
ORDER BY 1;

[mikeburgh]

Yes, something does not seem right with that timing.. lets try and narrow it down, can you run the following queries and tell me which one or ones are slow…

-- 0. Schemas
SELECT *
FROM all_users
WHERE ORACLE_MAINTAINED = 'N' OR username = USER
ORDER BY username;

-- 1. Tables
SELECT *
FROM all_tables t
LEFT JOIN all_tab_comments tc ON tc.table_name = t.table_name AND tc.owner = t.owner
WHERE t.dropped = 'NO' AND t.owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY t.table_name;

-- 2. Views
SELECT *
FROM all_views v
LEFT JOIN all_tab_comments tc ON tc.table_name = v.view_name AND tc.owner = v.owner
WHERE v.owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY v.view_name;

-- 3. Indexes
SELECT *
FROM all_indexes ai
LEFT JOIN all_constraints ac ON ac.owner = ai.owner AND ac.index_name = ai.index_name
WHERE ai.dropped = 'NO' AND ai.owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY ai.index_name;

-- 4. Index Columns
SELECT index_owner AS index_schema,
 	   table_name,
       index_name,
       column_name
FROM all_ind_columns
WHERE index_owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY column_position;

-- 5. Columns
SELECT *
FROM all_tab_cols c
LEFT JOIN all_col_comments cc ON c.owner = cc.owner AND c.table_name = cc.table_name AND c.column_name = cc.column_name
LEFT JOIN all_indexes i ON c.owner = i.table_owner AND c.table_name = i.table_name AND i.index_type = 'NORMAL' AND i.uniqueness = 'UNIQUE'
LEFT JOIN all_ind_columns ic ON ic.index_owner = i.owner AND ic.index_name = i.index_name AND ic.table_name = i.table_name AND c.column_name = ic.column_name
WHERE c.hidden_column = 'NO' AND c.owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY c.column_id;

-- 6. Foreign Keys
SELECT *
FROM all_constraints c
WHERE c.constraint_type = 'R' AND c.owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY c.constraint_name;

-- 7. Foreign Key Columns
SELECT *
FROM all_constraints con
JOIN all_cons_columns cs ON con.owner = cs.owner AND con.constraint_name = cs.constraint_name
JOIN all_constraints conr ON conr.owner = con.r_owner AND conr.constraint_name = con.r_constraint_name
JOIN all_cons_columns cr ON conr.owner = cr.owner AND conr.constraint_name = cr.constraint_name AND cs.position = cr.position
WHERE con.constraint_type = 'R' AND con.owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY cs.position;

-- 8. Constraints (Unique, Check)
SELECT *
FROM all_constraints c
WHERE c.constraint_type IN ('U','C') AND c.owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY c.constraint_name;

-- 9. Unique Constraint Columns
SELECT *
FROM all_constraints c
JOIN all_cons_columns col
  ON c.owner = col.owner
 AND c.constraint_name = col.constraint_name
WHERE c.constraint_type = 'U'
  AND c.owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY c.constraint_name;

-- 10. Triggers
SELECT *
FROM all_triggers t
WHERE t.table_owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY t.trigger_name;

-- 11. Procedures
SELECT *
FROM all_objects o
WHERE o.object_type IN ('PROCEDURE', 'FUNCTION') AND o.owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY o.object_name;

-- 12. Parameters
SELECT *
FROM all_arguments a
JOIN all_objects o ON a.object_id = o.object_id
WHERE o.object_type IN ('PROCEDURE', 'FUNCTION') AND a.owner IN (SELECT username FROM all_users WHERE ORACLE_MAINTAINED = 'N' OR username = USER)
ORDER BY a.position;

[vuongprogk]

Sys USER
-- 0. Schemas 14ms
-- 1. Tables 517ms
-- 2. Views 397ms
-- 3. Indexes 1.0s
-- 4. Index Columns 99ms
-- 5. Columns 1.2s
-- 6. Foreign Keys 209ms
-- 7. Foreign Key Columns 27ms
-- 8. Constraints (Unique, Check) 28ms
-- 9. Unique Constraint Columns 4ms
-- 10. Triggers 1.7s
-- 11. Procedures 23ms
-- 12. Parameters 47.3s

USER DMD -- I create and just adding sample table to this schema
I test this in sql developer because when access by this user it keep loading
-- 0. Schemas 14ms
-- 1. Tables 1.3s
-- 2. Views 397ms
-- 3. Indexes 1.4s
-- 4. Index Columns 99ms
-- 5. Columns 10.014s
-- 6. Foreign Keys 209ms
-- 7. Foreign Key Columns 3.07s
-- 8. Constraints (Unique, Check) 28ms
-- 9. Unique Constraint Columns 0.76s
-- 10. Triggers -> keep loading not not receive result I cancel it after 140s
-- 11. Procedures 23ms
-- 12. Parameters it like trigger

[mikeburgh]

Thanks, I have re worked them, can you give these two a try and see if they are faster now…

-- 10. Triggers
SELECT *
FROM all_triggers t
INNER JOIN all_users u ON t.table_owner = u.username
WHERE u.ORACLE_MAINTAINED = 'N' OR u.username = USER
ORDER BY t.trigger_name;



-- 12. Parameters
SELECT *
FROM all_arguments a
JOIN all_objects o ON a.object_id = o.object_id
INNER JOIN all_users u ON a.owner = u.username
WHERE o.object_type IN ('PROCEDURE', 'FUNCTION') AND (u.ORACLE_MAINTAINED = 'N' OR u.username = USER)
ORDER BY a.position;

[vuongprogk]

Still waste alot of time I think instead query trigger and params from all_ table can you query from user_ talbe I see that significantly faster than from all_ table

[mikeburgh]

Yes, but I am getting the data for all schemas, does the user_table have all the schemas in it ? or just the ones for the current user ?

How long did those queries take ? assuming they finished.