SELECT owner, table_name
FROM dba_tables
This is assuming that you have access to the DBA_TABLES
data dictionary view. 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). 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.
Alternatively, 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
FROM all_tables
Although, 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 are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES
:
SELECT table_name
FROM user_tables
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.
Oracle also has a number of legacy data dictionary views-- TAB
, DICT
, TABS
, and CAT
for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB
and CAT
views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES
views all filter those out. CAT
also shows information about materialized view logs with a TABLE_TYPE
of "TABLE" which is unlikely to be what you really want. DICT
combines tables and synonyms and doesn't tell you who owns the object.
You can use a subquery for this like
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.
Update:
To limit the result with both lower and upper bounds things get a bit more bloated with
select * from
( select a.*, ROWNUM rnum from
( <your_query_goes_here, with order by> ) a
where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
(Copied from specified AskTom-article)
Update 2:
Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
See this answer for more examples. Thanks to Krumia for the hint.
Best Answer
As nobody from disinterested parties haven't left any comments yet, we'll try to post as neutral comment as possible.
Devart has longer EF support history - since Aug 30th, 2007. During these two years we have taken into account numerous bug reports and user requests. We also have created and ship with our products Entity Developer - a powerful design time tool.
We cannot call our Entity Framework support for Oracle an ideal one - this ORM was initially designed for MS SQL Server, so the possibility to take into account the marvels of other DBMSs is significantly limited. It is enough to mention only the CROSS APPLY and OUTER APPLY problem.
But, in spite of these problems, most of our users are capable of working with Entity Framework successfully and comfortably.
That will be sufficient to say, but you have mentioned "critical enterprise allpications". In this case we recommend you to take a look at our Oracle-specific LINQ to SQL implementation - LINQ to Oracle.
LINQ to SQL does not pretend to build cross-database solutions and hence allows to take into consideration peculiarities of a separate DBMS, Oracle in particular. Unlike Entity Framework, where we have only partial control over the generated SQL queries, in the LINQ to Oracle case we have full control over the process. This fact gives us an opportunity to generate fast and valid Oracle-specific queries and also speeds up bug fixing and improvement process.
In case of legacy Oracle databases EF usually is hard to apply, unlike LINQ to Oracle.
Design time work with LINQ to Oracle model is also performed using Entity Developer.