Once the result is printed with a table prefixed with desc you can execute all of them in bulk to get results. If you want to get all the tables across all the schema you can use all_tables instead of user_tables select 'desc '||table_name If you prefer to do that you can use the following SQL snippet to get all your tables in your schema select 'desc '||table_name to get the list tables and adding DESC before it and executing it later. I had a similar requirement and I came to know that DESC does not work within PLSQL. It is not so efficient to use DESC for all the tables one after another. This will show you a slightly more complex query along with predefined column definitions commonly used in SQL*Plus.At times, while we want to get a list of tables and their columns along with their data types from Oracle.
To know what query it runs in the background, enter alias list tables2 MANAGE_EMPLYEE 0 0 0 Disabled 0 0 0 > MonthĪLL_TAB_X78EHRYFK 0 0 0 Disabled 0 0 0 > Month TABLE_NAME NUM_ROWS BLOCKS UNFORMATTED_SIZE COMPRESSION INDEX_COUNT CONSTRAINT_COUNT PART_COUNT LAST_ANALYZED SQL> tables_schema HR OWNER TABLE_NAME LAST_ANALYZEDĪ more sophisticated pre-defined alias is known as Tables2, which displays several other columns. Thereafter you may simply pass schema name as an argument SQL> alias tables_schema = select owner, table_name, last_analyzed from all_tables where owner = :ownr If you want to list tables from a specific schema, using a new user-defined alias and passing schema name as a bind argument with only a set of columns being displayed, you may do so using You don't have to define this alias as it comes by default under SQLcl. Select table_name "TABLES" from user_tables Tables - tables - show tables from schema To know what the tables alias is referring to, you may simply use alias list SQL> alias list tables SQL> set sqlformat ansiconsole - resizes the columns to the width of the It is recommended to enter this sqlcl specific command before running any other commands or queries which display data. First, connect to a sql command line ( sql.exe in windows) session. Here are few examples showing the usage and additional aspects of the feature.
#Query to list all tables in oracle database free#
DICT combines tables and synonyms and doesn't tell you who owns the object.Ī new feature available in SQLcl( which is a free command line interface for Oracle Database) is CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the _TABLES views all filter those out. Oracle has not changed these views in a long time so they often have problems with newer types of objects. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle also has a number of legacy data dictionary views- TAB, DICT, TABS, and CAT for example- that could be used. Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you.
If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES: SELECT table_name Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don't care about.Īlternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view: SELECT owner, table_nameĪlthough, that may be a subset of the tables available in the database ( ALL_TABLES shows you the information for all the tables that your user has been granted access to). If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary table). This is assuming that you have access to the DBA_TABLES data dictionary view.
0 kommentar(er)
