An equivalence of ".schema sqlite_master" in Oracle

Question

I'm trying to do the same exercise for both SQLite and Oracle. In SQLite, there is a table sqlite_master containing a description of all of the other tables, indexes, triggers, and views that are contained within the database. I can see the query to generate sqlite_master with .schema sqlite_master.

In Oracle, the data dictionary is presented to us in the form of a number of views (DBA, ALL or USER). Let's take the table USER_TABLES as an example. We can do query on USER_TABLES, for example

SELECT table_name
FROM USER_TABLES;

Is there anyway to get the query used to create the table USER_TABLES in Oracle? I tried

SELECT dbms_metadata.get_ddl('TABLE', 'USER_TABLES')
FROM dual;

but it does not work.

Answer 1

I am not sure what are you looking for but if you want the definition of any table in oracle you can use :

Describe TableName

Or if you want to have list of tables, views or columns you can use below queries: For Tables :

select * from select * from all_tables 

For Columns :

select * from all_tab_columns

For Views :

select * from select * from all_views 

To get all the column information of a table: select *

 from all_tab_columns
 where upper(table_name) = upper('Test')
 order by column_id

Typically oracle stores table_name in uppercase so I used upper() or you can just type 'TEST'