Find a constraint in ORACLE Database

I am working in Oracle11g.
How can I retrieve the constraints associated with tables in Oracle.

select * from user_constraints;

From 'user_constraints' table I got constraint type and name (ALL_CONSTRAINTS), But that is not enough for me. I need
* The primary key field name
* Foreign key field name,reference table name and referencing field name in reference table. 
There are two tables I need to retrieve the constraints and the constraint attribute.
These two tables are

user_constraints / dba_constraints
user_cons_columns / dba_cons_columns

The below query will retrieve the information that I need.

select   a.table_name
        , a.constraint_name
        , a.constraint_type
        , b.table_name
        , b.column_name
        , b.position
from     user_constraints a
        , user_cons_columns b
where    a.owner = b.owner
and      a.constraint_name = b.constraint_name
and      a.constraint_type = 'P'
union all
select   a.table_name
        , a.constraint_name
        , a.constraint_type
        , b.table_name
        , b.column_name
        , b.position
from     user_constraints a
        , user_cons_columns b
where    a.owner = b.owner
and      a.r_constraint_name = b.constraint_name
and      a.CONSTRAINT_TYPE = 'R'
order by 1, 2, 3, 4, 5;

how to retrieve total row count for each oracle table

Counting  all of the rows in a schema can require code that actually counts the table rows, and it's hard because rows are constantly being added and deleted from the schema. So, how do you count up all of the rows for all tables in a schema? There are two sources of row counts, both of which can become stale:

• Counts as of time last analyzed:   The num_rows column in dba_tables, current only to the date-time of the last analyze with dbms_stats.
• Row count at SQL execution time:  The "real" current row count, which requires that you actually issue SQL to count the rows in all of the tables (time consuming).

oracle ace laurent schneider has a more elegant solution for counting tables, using dbms_xmlgen to store the row counts for multiple tables in a single sql query list::

select
    table_name,
    to_number(
      extractvalue(
        xmltype(
     dbms_xmlgen.getxml('select count(*) c from '||table_name))
        ,'/ROWSET/ROW/C')) ROWS_COUNT
  FROM USER_TABLES
  ORDER BY ROWS_COUNT DESC;

16 SQL commands

SQL Language

SQL is defined, developed, and controlled by international bodies. Oracle Corporation does not have to conform to the SQL standard but chooses to do so. The language itself can be thought as being very simple (there are only 16 commands), but in practice SQL coding can be phenomenally complicated. That is why a whole book is needed to cover the bare fundamentals.

SQL Standards

Structured Query Language (SQL) was first invented by an IBM research group in the ’70s, but in fact Oracle Corporation (then trading as Relational Software, Inc.) claims to have beaten IBM to market by a few weeks with the first commercial implementation: Oracle 2, released in 1979. Since then the language has evolved enormously and is no longer driven by any one organization. SQL is now an international standard. It is managed by committees from ISO and ANSI. ISO is the Organisation Internationale de Normalisation, based in Geneva; ANSI is the American National Standards Institute, based in Washington, DC. The two bodies cooperate, and their SQL standards are identical.

SQL Commands

These are the 16 SQL commands, separated into commonly used groups:

The Data Manipulation Language (DML) commands:

SELECT
INSERT
UPDATE
DELETE
MERGE

The Data Definition Language (DDL) commands:

SELECT
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT

The Data Control Language (DCL) commands:

SELECT
GRANT
REVOKE

The Transaction Control Language (TCL) commands:

SELECT
COMMIT
ROLLBACK
SAVEPOINT

According to all the docs, SELECT is a DML statement. In practice, no one includes it when they refer to DML, they talk about it as though it were a language in its own right (it almost is) and use DML to mean only the commands that change data.

A Set-oriented Language

Most 3GLs are procedural languages. Programmers working in procedural languages specify what to do with data, one row at a time. Programmers working in a setoriented language say what they want to do to a group (a “set”) of rows and let the database work out how to do it to however many rows are in the set. Procedural languages are usually less efficient than set-oriented languages at managing data, as regards both development and execution. A procedural routine for looping through a group of rows and updating them one by one will involve many lines of code, where SQL might do the whole operation with one command: programmers’ productivity increases. During program execution, procedural code gives the database no options; it must run the code as it has been written. With SQL, the programmer states what he or she wants to do but not how to do it: the database has the freedom to work out how best to carry out the operation. This will usually give better results.

Users and Schemas

First, two definitions. In Oracle parlance, a database user is a person who can log on to the database. A database schema is all the objects in the database owned by one user. The two terms can often be used interchangeably, as there is a one-to-one relationship between users and schemas. Note that while there is in fact a CREATE SCHEMA command, this does not actually create a schema—it is only a quick way of  creating objects in a schema. A schema is initially created empty, when a user is created with the CREATE USER command.

Schemas are used for storing objects. These may be data objects such as tables or programmatic objects such as PL/SQL stored procedures. User logons are used to connect to the database and access these objects. By default, users have access to the objects in their own schema and to no others, but most applications change this. Typically, one schema may be used for storing data that is accessed by other users who have been given permission to use the objects, even though they do not own them. In practice, very few users will ever have objects in their own schema, or permission to create them: they will have access rights (which will be strictly controlled) only to objects in another schema. These objects will be used by all users who run the application whose data that schema stores. Conversely, the users who own the data-storing schemas may never in fact log on: the only purpose of their schemas is to contain data used by others. 

It is impossible for a data object to exist independently of a schema. Or in other words, all tables must have  an owner. The owner is the user in whose schema the table resides. The unique identifier for a table (or any other schema object) is the username, followed by the object name. It follows that it is not possible for two  tables with the same name to exist in the same schema, but that two tables with the same name (though possibly different structures or contents) can exist in different schemas. If an object does not exist in one’s own schema, to access it one must qualify its name with the name of the schema in which it resides. For example, HR.EMPLOYEES is the table called EMPLOYEES in user HR’s schema. Only a user connected as HR could get to the table by referring to EMPLOYEES without a schema name qualifier.

HR Schema

The HR demonstration schema consists of seven tables, linked by primary key to foreign key relationships.

The tables are:

• REGIONS has rows for major geographical areas.
• COUNTRIES has rows for each country, which are optionally assigned to a region.
• LOCATIONS includes individual addresses, which are optionally assigned to a country.
• DEPARTMENTS has a row for each department, optionally assigned to a location and optionally with a manager (who must exist as an employee).
• EMPLOYEES has a row for every employee, each of whom must be assigned to a job and optionally to a department and to a manager. The managers must themselves be employees.
• JOBS lists all possible jobs in the organization. It is possible for many employees to have the same job.
• JOB_HISTORY lists previous jobs held by employees, uniquely identified by employee_id and start_date; it is not possible for an employee to hold two jobs concurrently. Each job history record will refer to one employee, who will have had one job at that time and may have been a member of one department.

These commands, which could be issued from SQL*Plus or SQL Developer, will make it possible to log on as users HR and OE using the passwords HR:

alter user hr account unlock identified by hr;

These alter user commands can only be issued when connected to the database as a user with DBA privileges, such as the user SYSTEM.

Creating an Oracle Database Connection

Database connections can be created and saved for reuse. Figure below shows the window where connections can be defined. To reach this window, click the “+” symbol visible on the Connections tab. The username and password must both be supplied, but only the username will be saved unless the Save Password check box is selected. Saving a password means that future connections can be made without any password prompt. This is convenient but highly dangerous if there is any possibility that the computer you are working on is not secure. In effect, you are delegating the authentication to your local operating system: if you can log on to that, you can log on to the database.

The Connection Type radio buttons let you choose between three options:

• Basic This prompts for the machine name of the database server, the port on which the database listener will accept connection requests, and the instance (the SID) or the service to which the connection will be made.
• TNS If a name resolution method has been configured, then an alias for the database can be entered, rather than the full details needed by the Basic option.
• Advanced This allows entry of a full JDBC (Java Database Connectivity) connect string. This is completely Oracle independent and could be used to connect to any database that conforms to the JDBC standard.

Selecting Basic requires the user to know how to connect to the database; selecting TNS requires some configuration to have been done on the client machine by the database administrator, in order that the alias can be resolved into the full connection details.

After you enter the details, the Test button will force SQL Developer to attempt a logon. If this returns an error, then either the connection details are wrong, or there is a problem on the server side. Typical server-side problems are that the database listener is not running, or that the database has not been started. Whatever the error is, it will be prefixed with an error number.

SQL Developer

SQL Developer

SQL Developer is a tool for connecting to an Oracle database (or, in fact, some non-Oracle databases too) and issuing ad hoc SQL commands. It can also manage PL/SQL objects. Unlike SQL*Plus, it is a graphical tool with wizards for commonly needed actions. SQL Developer is written in Java and requires a Java Runtime Environment (JRE) to run. Being written in Java, SQL Developer is available on all platforms that support the appropriate version of the JRE. There are no significant differences between platforms.

The general layout of the SQL Developer window is a left pane for navigation around objects, and a right pane to display and enter information. In the figure, the left-hand pane shows that a connection has been made to a database. The connection is just a label chosen when the connection was defined, but most developers will use some sort of naming convention. The branches beneath list all the possible object types that can be managed. Expanding the branches would list the objects themselves. The right-hand pane has an upper part prompting the user to enter a SQL statement and a lower part that will display the result of the statement. The layout of the panes and the tabs visible on them are highly customizable.
The menu buttons across the top menu bar give access to standard facilities:

• File A normal Windows-like file menu, from which one can save work and exit from the tool.
• Edit A normal Windows-like edit menu, from which one can undo, redo, copy, paste, find, and so on.
• View The options for customizing the SQL Developer user interface.
• Navigate Facilities for moving between panes and for moving around code that is being edited.
• Run Forces execution of the SQL statements, SQL script, or PL/SQL block that is being worked on.
• Debug Rather than running a whole block of code, steps through it line by line with breakpoints.
• Source Options for use when writing SQL and PL/SQL code, such as keyword completion and automatic indenting.
• Tools Links to external programs, including SQL*Plus.
• Migrate Tools for converting applications designed for third-party databases (Microsoft Access, SQL Server, and MySQL) to the Oracle environment.
• Help It’s pretty good.

SQL Developer can be a very useful tool, and it is very customizable. Experiment with it, read the Help, and set up the user interface the way that works best for you.

Reference:

• OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051)