How to generate an entity-relationship (ER) diagram using Oracle SQL Developer

How to generate an entity-relationship (ER) diagram using Oracle SQL Developer

I want to use Oracle SQL Developer to generate an ER diagram for my DB tables but I am new to Oracle and this tool.
What is the process for creating an ER diagram in SQL Developer?

Solutions/Answers:

Solution 1:

Create a diagram for existing database schema or its subset as follows:

  1. Click File → Data Modeler → Import → Data Dictionary.
  2. Select a DB connection (add one if none).
  3. Click Next.
  4. Check one or more schema names.
  5. Click Next.
  6. Check one or more objects to import.
  7. Click Next.
  8. Click Finish.

The ERD is displayed.

Export the diagram as follows:

  1. Click File → Data Modeler → Print Diagram → To Image File.
  2. Browse to and select the export file location.
  3. Click Save.

The diagram is exported. To export in a vector format, use To PDF File, instead. This allows for simplified editing using Inkscape (or other vector image editor).

These instructions may work for SQL Developer 3.2.09.23 to 4.1.3.20.

Solution 2:

Since SQL Developer 3, it’s pretty straightforward (they could’ve made it easier).

  1. Go to «View → Data Modeler → Browser». The browser will show up as one of the tabs along the left-hand side.
  2. Click on the «Browser» tab, expand the design (probably called Untitled_1), right-click «Relational Models» and select «New Relational Model».
  3. Right click on the newly created relational model (probably Relational_1) and select «Show».
  4. Then just drag the tables you want (from e.g. the «Connections» tab) onto the model.  Note when you click on the first table in the Connections tab, SQLDeveloper opens that table in the right: select all the tables from the left, then ensure the Relational_1 tab (or whatever name) is the active one in the rhs before you drag them over, because it has probably switched to one of the tables you clicked in the lhs.

Solution 3:

The process of generating Entity-Relationship diagram in Oracle SQL Developer has been described in Oracle Magazine by Jeff Smith (link).

Excerpt:

Entity relationship diagram

Entity relationship diagram

Getting Started

To work through the example, you need an Oracle Database instance with the sample HR schema that’s available in the default database installation. You also need version 4.0 of Oracle SQL Developer, in which you access Oracle SQL Developer Data Modeler through the Data Modeler submenu […] Alternatively, you can use the standalone Oracle SQL Developer Data Modeler. The modeling functionality is identical in the two implementations, and both are available as free downloads from Oracle Technology Network.

In Oracle SQL Developer, select View -> Data Modeler –> Browser. In the Browser panel, select the Relational Models node, right-click, and select New Relational Model to open a blank model diagram panel. You’re now starting at the same place as someone who’s using the standalone Oracle SQL Developer Data Modeler.
Importing Your Data Dictionary

Importing Your Data Dictionary

A design in Oracle SQL Developer Data Modeler consists of one logical model and one or more relational and physical models. To begin the process of creating your design, you must import the schema information from your existing database. Select File -> Data Modeler -> Import -> Data Dictionary to open the Data Dictionary Import wizard.

Click Add to open the New -> Select Database Connection dialog box, and connect as the HR user. (For detailed information on creating a connection from Oracle SQL Developer, see “Making Database Connections,” in the May/June 2008 issue of Oracle Magazine.)

Select your connection, and click Next. You see a list of schemas from which you can import. Type HR in the Filter box to narrow the selection list. Select the checkbox next to HR, and click Next.

Read more…

Solution 4:

For class diagram using oracle db use following steps

File → Data Modeler → Import → Data Dictionary → select DB connection → Next → select database->select tabels -> Finish

Solution 5:

There is a companion tool called Oracle Data Modeler that you could take a look at. There are online demos available at the site that will get you started. It used to be an added cost item, but I noticed that once again it’s free.

From the Data Modeler overview page:

SQL Developer Data Modeler is a free
data modeling and design tool, proving
a full spectrum of data and database
modeling tools and utilities,
including modeling for Entity
Relationship Diagrams (ERD),
Relational (database design), Data
Type and Multi-dimensional modeling,
with forward and reverse engineering
and DDL code generation. The Data
Modeler imports from and exports to a
variety of sources and targets,
provides a variety of formatting
options and validates the models
through a predefined set of design
rules.

Solution 6:

Oracle used to have a component in SQL Developer called Data Modeler. It no longer exists in the product since at least 3.2.20.10.

It’s now a separate download that you can find here:

http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

References

How to select the nth row in a SQL database table?

How to select the nth row in a SQL database table?

I’m interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases:

SQL Server
MySQL
PostgreSQL
SQLite
Oracle

I am currently doing something like the following in SQL Server 2005, but I’d be interested in seeing other’s more agnostic approaches:
WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT *
FROM Ordered
WHERE RowNumber = 1000000

Credit for the above SQL: Firoz Ansari’s Weblog
Update: See Troels Arvin’s answer regarding the SQL standard. Troels, have you got any links we can cite?

Solutions/Answers:

Solution 1:

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

Solution 2:

The LIMIT / OFFSET syntax in PostgreSQL is:

SELECT
    *
FROM
    mytable
ORDER BY
    somefield
LIMIT 1 OFFSET 20;

This example selects the 21st row. OFFSET 20 is telling Postgres to skip the first 20 records. If you don’t specify an ORDER BY clause, there’s no guarantee which record you will get back, which is rarely useful.

Apparently the SQL standard is silent on the limit issue outside of crazy windowing functions, which is why everyone implements it differently.

Solution 3:

I’m not sure about any of the rest, but I know SQLite and MySQL don’t have any “default” row ordering. In those two dialects, at least, the following snippet grabs the 15th entry from the_table, sorting by the date/time it was added:

SELECT * FROM the_table ORDER BY added DESC LIMIT 1,15

(of course, you’d need to have an added DATETIME field, and set it to the date/time that entry was added…)

Solution 4:

SQL 2005 and above has this feature built-in. Use the ROW_NUMBER() function. It is excellent for web-pages with a << Prev and Next >> style browsing:

Syntax:

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
            *
        FROM
            Table_1
    ) sub
WHERE
    RowNum = 23

Solution 5:

I suspect this is wildly inefficient but is quite a simple approach, which worked on a small dataset that I tried it on.

select top 1 field
from table
where field in (select top 5 field from table order by field asc)
order by field desc

This would get the 5th item, change the second top number to get a different nth item

SQL server only (I think) but should work on older versions that do not support ROW_NUMBER().

Solution 6:

1 small change: n-1 instead of n.

select *
from thetable
limit n-1, 1

References

How to create id with AUTO_INCREMENT on Oracle?

How to create id with AUTO_INCREMENT on Oracle?

It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g.
How can I create a column that behaves like auto increment in Oracle 11g?

Solutions/Answers:

Solution 1:

There is no such thing as “auto_increment” or “identity” columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger:

Table definition:

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq START WITH 1;

Trigger definition:

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

UPDATE:

IDENTITY column is now available on Oracle 12c:

create table t1 (
    c1 NUMBER GENERATED by default on null as IDENTITY,
    c2 VARCHAR2(10)
    );

or specify starting and increment values, also preventing any insert into the identity column (GENERATED ALWAYS) (again, Oracle 12c+ only)

create table t1 (
    c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    c2 VARCHAR2(10)
    );

Alternatively, Oracle 12 also allows to use a sequence as a default value:

CREATE SEQUENCE dept_seq START WITH 1;

CREATE TABLE departments (
  ID           NUMBER(10)    DEFAULT dept_seq.nextval NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

Solution 2:

SYS_GUID returns a GUID– a globally unique ID. A SYS_GUID is a RAW(16). It does not generate an incrementing numeric value.

If you want to create an incrementing numeric key, you’ll want to create a sequence.

CREATE SEQUENCE name_of_sequence
  START WITH 1
  INCREMENT BY 1
  CACHE 100;

You would then either use that sequence in your INSERT statement

INSERT INTO name_of_table( primary_key_column, <<other columns>> )
  VALUES( name_of_sequence.nextval, <<other values>> );

Or you can define a trigger that automatically populates the primary key value using the sequence

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT name_of_sequence.nextval
    INTO :new.primary_key_column
    FROM dual;
END;

If you are using Oracle 11.1 or later, you can simplify the trigger a bit

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  :new.primary_key_column := name_of_sequence.nextval;
END;

If you really want to use SYS_GUID

CREATE TABLE table_name (
  primary_key_column raw(16) default sys_guid() primary key,
  <<other columns>>
)

Solution 3:

In Oracle 12c onward you could do something like,

CREATE TABLE MAPS
(
  MAP_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
  MAP_NAME VARCHAR(24) NOT NULL,
  UNIQUE (MAP_ID, MAP_NAME)
);

And in Oracle (Pre 12c).

-- create table
CREATE TABLE MAPS
(
  MAP_ID INTEGER NOT NULL ,
  MAP_NAME VARCHAR(24) NOT NULL,
  UNIQUE (MAP_ID, MAP_NAME)
);

-- create sequence
CREATE SEQUENCE MAPS_SEQ;

-- create tigger using the sequence
CREATE OR REPLACE TRIGGER MAPS_TRG 
BEFORE INSERT ON MAPS 
FOR EACH ROW
WHEN (new.MAP_ID IS NULL)
BEGIN
  SELECT MAPS_SEQ.NEXTVAL
  INTO   :new.MAP_ID
  FROM   dual;
END;
/

Solution 4:

Here are three flavors:

  1. numeric. Simple increasing numeric value, e.g. 1,2,3,….
  2. GUID. globally univeral identifier, as a RAW datatype.
  3. GUID (string). Same as above, but as a string which might be easier to handle in some languages.

x is the identity column. Substitute FOO with your table name in each of the examples.

-- numerical identity, e.g. 1,2,3...
create table FOO (
    x number primary key
);
create sequence  FOO_seq;

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  select FOO_seq.nextval into :new.x from dual;
end;
/

-- GUID identity, e.g. 7CFF0C304187716EE040488AA1F9749A
-- use the commented out lines if you prefer RAW over VARCHAR2.
create table FOO (
    x varchar(32) primary key        -- string version
    -- x raw(32) primary key         -- raw version
);

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  select cast(sys_guid() as varchar2(32)) into :new.x from dual;  -- string version
  -- select sys_guid() into :new.x from dual;                     -- raw version
end;
/

update:

Oracle 12c introduces these two variants that don’t depend on triggers:

create table mytable(id number default mysequence.nextval);
create table mytable(id number generated as identity);

The first one uses a sequence in the traditional way; the second manages the value internally.

Solution 5:

Assuming you mean a column like the SQL Server identity column?

In Oracle, you use a SEQUENCE to achieve the same functionality. I’ll see if I can find a good link and post it here.

Update: looks like you found it yourself. Here is the link anyway:
http://www.techonthenet.com/oracle/sequences.php

Solution 6:

Oracle Database 12c introduced Identity, an auto-incremental (system-generated) column.
In the previous database versions (until 11g), you usually implement an Identity by creating a Sequence and a Trigger.
From 12c onward, you can create your own Table and define the column that has to be generated as an Identity.

The following article explains how to use it:

Identity columns – A new entry in Oracle Database 12c

References

Fetch the row which has the Max value for a column

Fetch the row which has the Max value for a column

Table:
UserId, Value, Date.

I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (Preferably Oracle)
Update: Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that row where that user has the latest date.

Solutions/Answers:

Solution 1:

This will retrieve all rows for which the my_date column value is equal to the maximum value of my_date for that userid. This may retrieve multiple rows for the userid where the maximum date is on multiple rows.

select userid,
       my_date,
       ...
from
(
select userid,
       my_date,
       ...
       max(my_date) over (partition by userid) max_my_date
from   users
)
where my_date = max_my_date

“Analytic functions rock”

Edit: With regard to the first comment …

“using analytic queries and a self-join defeats the purpose of analytic queries”

There is no self-join in this code. There is instead a predicate placed on the result of the inline view that contains the analytic function — a very different matter, and completely standard practice.

“The default window in Oracle is from the first row in the partition to the current one”

The windowing clause is only applicable in the presence of the order by clause. With no order by clause, no windowing clause is applied by default and none can be explicitly specified.

The code works.

Solution 2:

I see many people use subqueries or else vendor-specific features to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it should work in any brand of RDBMS.

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;

In other words: fetch the row from t1 where no other row exists with the same UserId and a greater Date.

(I put the identifier “Date” in delimiters because it’s an SQL reserved word.)

In case if t1."Date" = t2."Date", doubling appears. Usually tables has auto_inc(seq) key, e.g. id.
To avoid doubling can be used follows:

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON t1.UserId = t2.UserId AND ((t1."Date" < t2."Date") 
         OR (t1."Date" = t2."Date" AND t1.id < t2.id))
WHERE t2.UserId IS NULL;

Re comment from @Farhan:

Here’s a more detailed explanation:

An outer join attempts to join t1 with t2. By default, all results of t1 are returned, and if there is a match in t2, it is also returned. If there is no match in t2 for a given row of t1, then the query still returns the row of t1, and uses NULL as a placeholder for all of t2‘s columns. That’s just how outer joins work in general.

The trick in this query is to design the join’s matching condition such that t2 must match the same userid, and a greater date. The idea being if a row exists in t2 that has a greater date, then the row in t1 it’s compared against can’t be the greatest date for that userid. But if there is no match — i.e. if no row exists in t2 with a greater date than the row in t1 — we know that the row in t1 was the row with the greatest date for the given userid.

In those cases (when there’s no match), the columns of t2 will be NULL — even the columns specified in the join condition. So that’s why we use WHERE t2.UserId IS NULL, because we’re searching for the cases where no row was found with a greater date for the given userid.

Solution 3:

SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
  FROM table
  GROUP BY userid

Solution 4:

I don’t know your exact columns names, but it would be something like this:

    select userid, value
      from users u1
     where date = (select max(date)
                     from users u2
                    where u1.userid = u2.userid)

Solution 5:

Not being at work, I don’t have Oracle to hand, but I seem to recall that Oracle allows multiple columns to be matched in an IN clause, which should at least avoid the options that use a correlated subquery, which is seldom a good idea.

Something like this, perhaps (can’t remember if the column list should be parenthesised or not):

SELECT * 
FROM MyTable
WHERE (User, Date) IN
  ( SELECT User, MAX(Date) FROM MyTable GROUP BY User)

EDIT: Just tried it for real:

SQL> create table MyTable (usr char(1), dt date);
SQL> insert into mytable values ('A','01-JAN-2009');
SQL> insert into mytable values ('B','01-JAN-2009');
SQL> insert into mytable values ('A', '31-DEC-2008');
SQL> insert into mytable values ('B', '31-DEC-2008');
SQL> select usr, dt from mytable
  2  where (usr, dt) in 
  3  ( select usr, max(dt) from mytable group by usr)
  4  /

U DT
- ---------
A 01-JAN-09
B 01-JAN-09

So it works, although some of the new-fangly stuff mentioned elsewhere may be more performant.

Solution 6:

I know you asked for Oracle, but in SQL 2005 we now use this:


-- Single Value
;WITH ByDate
AS (
SELECT UserId, Value, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) RowNum
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE RowNum = 1

-- Multiple values where dates match
;WITH ByDate
AS (
SELECT UserId, Value, RANK() OVER (PARTITION BY UserId ORDER BY Date DESC) Rnk
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE Rnk = 1

References

How do I limit the number of rows returned by an Oracle query after ordering?

How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause?
In MySQL, I can do this:
select *
from sometable
order by name
limit 20,10

to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically.
In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this:
select *
from sometable
where rownum <= 10 order by name will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.

Solutions/Answers:

Solution 1:

Starting from Oracle 12c R1 (12.1), there is a row limiting clause. It does not use familiar LIMIT syntax, but it can do the job better with more options. You can find the full syntax here.

To answer the original question, here’s the query:

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

(For earlier Oracle versions, please refer to other answers in this question)


Examples:

Following examples were quoted from linked page, in the hope of preventing link rot.

Setup

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

What’s in the table?

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

Get first N rows

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

Get first N rows, if Nth row has ties, get all the tied rows

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

Top x% of rows

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

Using an offset, very useful for pagination

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

You can combine offset with percentages

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

Solution 2:

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.

Solution 3:

I did some performance testing for the following approaches:

Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

Analytical

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

Short Alternative

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

Results

Table had 10 million records, sort was on an unindexed datetime row:

  • Explain plan showed same value for all three selects (323168)
  • But the winner is AskTom (with analytic following close behind)

Selecting first 10 rows took:

  • AskTom: 28-30 seconds
  • Analytical: 33-37 seconds
  • Short alternative: 110-140 seconds

Selecting rows between 100,000 and 100,010:

  • AskTom: 60 seconds
  • Analytical: 100 seconds

Selecting rows between 9,000,000 and 9,000,010:

  • AskTom: 130 seconds
  • Analytical: 150 seconds

Solution 4:

An analytic solution with only one nested query:

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

Rank() could be substituted for Row_Number() but might return more records than you are expecting if there are duplicate values for name.

Solution 5:

On Oracle 12c (see row limiting clause in SQL reference):

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Solution 6:

Pagination queries with ordering are really tricky in Oracle.

Oracle provides a ROWNUM pseudocolumn that returns a number indicating the order in which the database selects the row from a table or set of joined views.

ROWNUM is a pseudocolumn that gets many people into trouble. A ROWNUM value is not permanently assigned to a row (this is a common misunderstanding). It may be confusing when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes filter predicates of the query but before query aggregation or sorting.

What is more, a ROWNUM value is incremented only after it is assigned.

This is why the followin query returns no rows:

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1; 

The first row of the query result does not pass ROWNUM > 1 predicate, so ROWNUM does not increment to 2. For this reason, no ROWNUM value gets greater than 1, consequently, the query returns no rows.

Correctly defined query should look like this:

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1; 

Find out more about pagination queries in my articles on Vertabelo blog:

References

Get list of all tables in Oracle?

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Solutions/Answers:

Solution 1:

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.

Solution 2:

Querying user_tables and dba_tables didn’t work.
This one did:

select table_name from all_tables  

Solution 3:

Going one step further, there is another view called cols (all_tab_columns) which can be used to ascertain which tables contain a given column name.

For example:

SELECT table_name, column_name
FROM cols
WHERE table_name LIKE 'EST%'
AND column_name LIKE '%CALLREF%';

to find all tables having a name beginning with EST and columns containing CALLREF anywhere in their names.

This can help when working out what columns you want to join on, for example, depending on your table and column naming conventions.

Solution 4:

For better viewing with sqlplus

If you’re using sqlplus you may want to first set up a few parameters for nicer viewing if your columns are getting mangled (these variables should not persist after you exit your sqlplus session ):

set colsep '|'
set linesize 167
set pagesize 30
set pagesize 1000

Show All Tables

You can then use something like this to see all table names:

SELECT table_name, owner, tablespace_name FROM all_tables;

Show Tables You Own

As @Justin Cave mentions, you can use this to show only tables that you own:

SELECT table_name FROM user_tables;

Don’t Forget about Views

Keep in mind that some “tables” may actually be “views” so you can also try running something like:

SELECT view_name FROM all_views;

The Results

This should yield something that looks fairly acceptable like:

result

Solution 5:

Simple query to select the tables for the current user:

  SELECT table_name FROM user_tables;

Solution 6:

    select object_name from user_objects where object_type='TABLE';

—————-OR——————

    select * from tab;

—————-OR——————

    select table_name from user_tables;

References

Sample tnsnames.ora file for Oracle databases

Below is the sample tnsnames.ora file for Oracle databases. You can just copy and paste to yours:

DB01 =
  (DESCRIPTION =
    (SDU = 32768)
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = DB01.WORLD)
          (PROTOCOL = TCP)
          (HOST = 172.16.1.19)
          (PORT = 1521)
       )
    (CONNECT_DATA =
       (SID = DB01)
       (GLOBAL_NAME = DB01GN.WORLD)
    )
  )

ORAERR =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = ORAERR.COM)
          (PROTOCOL = TCP)
          (HOST = 192.168.1.19)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA =
       (SID = ORAERR)
        (GLOBAL_NAME = ORAERRGN.WORLD)
        )
) 

Oracle database links tutorial

This tutorial will help you understand and working with Oracle database links.

What is a database link?

database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system.

How to create database links?

You can use these commands to create new database link:

# create a public database link for current user
# connect to dblinkusername/dblinkpassword@db02

CREATE PUBLIC DATABASE LINK dblinkname CONNECT TO CURRENT_USER AUTHENTICATED BY  dblinkusername IDENTIFIED BY dblinkpassword USING db02;

# create a public shared database link for current user
# connect to dblinkusername/dblinkpassword@db02

CREATE PUBLIC SHARED DATABASE LINK dblinkname CONNECT TO CURRENT_USER AUTHENTICATED BY  dblinkusername IDENTIFIED BY dblinkpassword USING db02;
 
# Most complex case

CREATE PUBLIC DATABASE LINK dblinkname CONNECT TO user IDENTIFIED BY password AUTHENTICATED BY dblinkusername IDENTIFIED BY dblinkpassword USING db02;

Public and Shared database links

Public database links

Public database link visible to all users. You have to explicit declare in your command or it will be default PRIVATE – available to only you.

Shared database links

Specify SHARED to create a database link that can be shared by multiple sessions using a single network connection from the source database to the target database.

In a shared server configuration, shared database links can keep the number of connections into the remote database from becoming too large. Shared links are typically also public database links.

However, a shared private database link can be useful when many clients access the same local schema, and therefore use the same private database link.

How to manage database links?

How to close an active database link?

You can close an active database link with this command:

ALTER SESSION CLOSE DATABASE LINK linkname; 

To drop a database link

This command is for dropping a database link:

DROP [PUBLIC] DATABASE LINK dblink;  

To list all database links belong to one user

CONNECT user@db01;
SQL> SELECT DB_LINK FROM USER_DB_LINKS; 

To list all public database links

CONNECT sys@db01 as SYSDBA;
SQL> SELECT DB_LINK FROM USER_DB_LINKS WHERE OWNER = 'PUBLIC';

Some errors on database links

This is the list of errors that you may face while working with Oracle database links:

References

Using SQLPlus to connect to Oracle databases

SQLPlus is a very useful to help you connect to Oracle database via console terminals.

Install SQLPlus

Recently versions of Oracle client not include SQLPlus any more, so you have to download and install SQLPLus separately.

For Windows you can download here: link

Scroll down at “Tools – optional packages” section

Download SQLPlus Tools
Download SQLPlus Tools

Download the package, unzip to a directory and add this directory to the PATH environment variable.

Check your SQLPlus installation with this command:

sqlplus -v

Your result will be like this:

SQL*Plus: Release 11.2.0.1.0 Production

Use SQLPlus to connect to Oracle databases

Use this command:

sqlplus username/password@db_ip:db_port/db_servicename

if you use sys user:

sqlplus username/password@db_ip:db_port/db_servicename as sysdba

Example:

sqlplus sys/123456@172.16.1.10:1521/db10 as sysdba

Your result will be like this:

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 15 16:59:00 2019
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Default location of TNSNames.ora

By default, tnsnames.ora file will be located at: $ORACLE_HOME/network/admin folder. If you install Instant Client only, by default, Oracle Universal Installer does not include a sample tnsnames.ora file nor the Oracle Net Configuration Assistant utility normally used to create it.

Then you don’t have tnsnames.ora by default. You should follow these step to configure properly:

Method 1: Manually create and configure

Follow these steps:

Step 1:

Folow this example and create a file with sample content copied: Sample tnsnames.ora file for Oracle databases.

Save & copy file to your client home. May be different on each machine. Mine is: “C:\app\oraerr\product\11.2.0\client_1\network\admin“.

If you couldn’t found network & admin folder, just create new one and copy the tnsnames.ora into this folder.

Step 2:

Try to connect using tns declared in the tnsnames.ora fle above. For example:

sqlplus username/password@db01

Method 2: Install Oracle Network Utilities

To install Oracle Net Configuration Assistant:

  • Run Oracle Universal Installer.
  • Select the Custom installation type.
  • In the Available Product Components list, select Oracle Network Utilities and click Next.
  • In the Summary window, click Install, then click Exit and Yes to exit Oracle Universal Installer.

Then you now have Oracle create /network/admin automatically plus a sample tnsnames.ora file.

If you couldn’t find tnsnames.ora then copy a sample to the location above.

Now, try to connect using tns declared in the tnsnames.ora fle above. For example:

sqlplus username/password@db01

NOTES

REFERENCES