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)

SQL*Plus

SQL*Plus is a client-server tool for connecting to a database and issuing ad hoc SQL commands. It can also be used for creating PL/SQL code and has facilities for formatting results. It is available on all platforms to which the database has been ported—the sections that follow give some detail on using SQL*Plus on Linux and Windows. There are no significant differences with using SQL*Plus on any other platform. In terms of architecture, SQL*Plus is a user process written in C. It establishes a session against an instance and a database over the Oracle Net protocol. The platforms for the client and the server can be different. For example, there is no reason not to use SQL*Plus on a Windows PC to connect to a database running on a mainframe (or the other way round) provided that Oracle Net has been configured to make the connection.

SQL*Plus on Linux

The SQL*Plus executable file on a Linux installation is sqlplus. The location of this file will be installation specific but will typically be something like:

/u01/app/oracle/product/db_1/bin/sqlplus

Your Linux account should be set up appropriately to run SQL*Plus. There are some environment variables that will need to be set. These are:

• ORACLE_HOME
• PATH
• LD_LIBRARY_PATH

The ORACLE_HOME variable points to the Oracle Home. An Oracle Home is the Oracle software installation: the set of files and directories containing the executable code and some of the configuration files.

The PATH must include the bin directory in the Oracle Home.

The LD_LIBRARY_PATH should include the lib directory in the Oracle Home, but in practice you may get away without setting this. Figure below shows a Linux terminal window and some tests to see if the environment is correct.

In The Figure , first the echo command checks whether the three variables have been set up correctly: there is an ORACLE_HOME, and the bin and lib directories in it have been set as the first element of the PATH and LD_LIBRARY_PATH variables. Then which confirms that the SQL*Plus executable file really is available, in the PATH. Finally, SQL*Plus is launched with a username, a password, and a connect identifier passed to it on the command line. If the tests do not return acceptable results and SQL*Plus fails to launch, this should be discussed with your system administrator and your database administrator.

The format of the login string is the database username followed by a forward slash character as a delimiter, then a password followed by an @ symbol as a delimiter, and finally an Oracle Net connect identifier. Following the logon, the next lines of text display the version of SQL*Plus being used, which is 11.1.0.6.0, the version of the database to which the connection has been made (which happens to be the same as the version of the SQL*Plus tool), and which options have been installed within the database. The last line is the  prompt to the user, SQL, at which point the user can enter any SQL*Plus or SQL command. If the login does not succeed with whatever username (probably not system) you have been allocated, this should be discussed with your database administrator.

SQL*Plus on Windows

Historically, there were always two versions of SQL*Plus for Microsoft Windows: the character version and the graphical version. The character version is the executable file sqlplus.exe, and the graphical version was sqlplusw.exe. With the current release the graphical version no longer exists, but many developers will prefer to use it and the versions shipped with earlier releases are perfectly good tools for working with an 11g database. There are no problems with mixing versions: an 11g SQL*Plus client can connect to a 10g database, and a 10g SQL*Plus client can connect to an 11g database. Following a default installation of either the Oracle database or just the Oracle client on Windows, SQL*Plus will be available as a shortcut on the Windows Start menu. The navigation path will be as follows:

1. Start
2. Programs
3. Oracle—OraDB11g_home1
4. Application Development
5. SQL Plus

Note that the third part of the navigation path may vary depending on the installation. The location of the executable file launched by the shortcut will, typically, be something like the following:
D:\oracle\app\product\11.1.0\db_2\BIN\sqlplus.exe

However, the exact path will be installation specific. Figure below shows a logon to a database with SQL*Plus, launched from the shortcut. The first line of text shows the version of SQL*Plus, which is the 11.1.0.4.0 beta release, and the time the program was launched. The third line of text is a logon prompt:

Enter user-name:

followed by the logon string entered manually, which was

userName/password@serviceName

A change some people like to make to the shortcut that launches SQL*Plus is to prevent it from immediately presenting a login prompt. To do this, add the NOLOG switch to the end of the command:

sqlplus /nolog

There is no reason not to launch SQL*Plus from an operating system prompt rather than from the Start menu shortcut: simply open a command window and run it. The program will immediately prompt for a logon, unless you invoke it with the NOLOG switch described above.

Reference:

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

Procedure vs Package

Package have alot advantages over procedure, which can be summarized as following:

• Break the dependency chain

No cascading invalidation when you install a new package body, if you have procedures that call procedures then compiling one will invalidate your database.

• Support encapsulation

I will be allowed to write MODULAR, easy to understand code, rather then MONOLITHIC, non-understandable procedures.

• Increase my namespace measurably.

Package names have to be unique in a schema, but I can have many procedures across packages with the same name without colliding.

• Support overloading

• Support session variables

• Promote overall good coding techniques

Stuff that lets you write code that is modular, understandable, logically grouped together....

Anonymous PL/SQL Blocks versus Stored Procedures

A stored procedure:

Is created and stored in the database as a schema object. Once created and compiled, it is a named object that can be executed without recompiling. Additionally, dependency information is stored in the data dictionary to guarantee the validity of each stored procedure.

Anonymous PL/SQL block:

As an alternative to a stored procedure, you can create an anonymous PL/SQL block by sending an unnamed PL/SQL block to the Oracle server from an Oracle tool or an application. Oracle compiles the PL/SQL block and places the compiled version in the shared pool of the SGA, but does not store the source code or compiled version in the database for reuse beyond the current instance. Shared SQL allows anonymous PL/SQL blocks in the shared pool to be reused and shared until they are flushed out of the shared pool.

In either case, moving PL/SQL blocks out of a database application and into database procedures stored either in the database or in memory, you avoid unnecessary procedure recompilation by Oracle at run-time  improving the overall performance of the application and Oracle.

Stored Procedures, Functions and Packages

Stored Procedures and Functions

Procedures and functions are schema objects that logically group a set of SQL and other PL/SQL programming language statements together to perform a specific task. Procedures and functions are created in a user's schema and stored in a database for continued use.

Packages

A package is a group of related procedures and/or functions, together with the cursors and variables they use, stored together in the database for continued use as a unit. Similar to standalone procedures and functions, packaged procedures and functions can be called explicitly by applications or users.

Benefits of Procedures
Procedures provide advantages in the following areas.
Security

Stored procedures can help enforce data security. You can restrict the database operations that users can perform by allowing them to access data only through procedures and functions that execute with the definer's privileges. When a user invokes the procedure, the procedure executes with the privileges of the procedure's owner. Users who have only the privilege to execute the procedure (but not the privileges to query, update, or delete from the underlying tables) can invoke the procedure, but they cannot manipulate table data in any other way.

Performance

Stored procedures can improve database performance in several ways:

• The amount of information that must be sent over a network is small compared to issuing individual SQL statements or sending the text of an entire PL/SQL block to Oracle, because the information is sent only once and thereafter invoked when it is used.
• A procedure's compiled form is readily available in the database, so no compilation is required at execution time.
• If the procedure is already present in the shared pool of the SGA, retrieval from disk is not required, and execution can begin immediately.
• Memory Allocation

Because stored procedures take advantage of the shared memory capabilities of Oracle, only a single copy of the procedure needs to be loaded into memory for execution by multiple users. Sharing the same code among many users results in a substantial reduction in Oracle memory requirements for applications.

Productivity

Stored procedures increase development productivity. By designing applications around a common set of procedures, you can avoid redundant coding and increase your productivity.

Integrity

Stored procedures improve the integrity and consistency of your applications. By developing all of your applications around a common group of procedures, you can reduce the likelihood of committing coding errors.

Standalone Procedures

Stored procedures not defined within the context of a package are called standalone procedures. Procedures defined within a package are considered a part of the package.

Benefits of Packages

Packages are used to define related procedures, variables, and cursors and are often implemented to provide advantages in the following areas:

• Encapsulation of related procedures and variables

Stored packages allow you to encapsulate (group) related stored procedures, variables, datatypes, and so forth in a single named, stored unit in the database. This provides for better organization during the development process.
Encapsulation of procedural constructs in a package also makes privilege management easier. Granting the privilege to use a package makes all constructs of the package accessible to the grantee.

• Declaration of public and private procedures, variables, constants, and cursors

The methods of package definition allow you to specify which variables, cursors, and procedures are
Public:  Directly accessible to the user of a package.
Private: Hidden from the user of a package.

• Better performance

An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to execute the compiled code already in memory.

• Encapsulation

A package body can be replaced and recompiled without affecting the specification. As a result, schema objects that reference a package's constructs (always via the specification) need not be recompiled unless the package specification is also replaced. By using packages, unnecessary recompilation can be minimized, resulting in less impact on overall database performance.

References:

http://asktom.oracle.com/
http://www.csee.umbc.edu/
http://www.tutorialspoint.com/plsql/

Tom Cargill Quote

The first 90% of the code accounts for the first 90% of the development time. The remaining 10% of the code accounts for the other 90% of the development time.

Changing host name inside Oracle XE configuration file

If you change oracle machine host name, the listener can’t run due to error in configuration.
So you need to configure it again manually as:

1. Stop Database Listener and Service
2. Go to installation directory
3. Navigate:

“\app\oracle\product\<oracle version>\server\NETWORK\ADMIN”

4. Edit:“listener.ora”

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = <new_host_name>)(PORT = 1521))
)
)

5. Edit:“tnsnames.ora”

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =<new_host_name>(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

6. Start the Listener and Service again

How do I find duplicate values in a table in Oracle?

To find the simplest SQL statement that return the duplicate values for a given column and the count of their occurrences in an Oracle database table, see below

SELECT table_field, COUNT(table_field) AS dup_count
FROM table_name
GROUP BY table_field
HAVING (COUNT(table_field) > 1);

it will retrieve the feildID and it's duplication count.

Query to get list of Oracle Users ans status

SELECT username,account_status FROM dba_users;

Java labeled Break Statement

The break statement has two forms: labeled and unlabeled.
You can also use an unlabeled break to terminate a for, while, or do-while loop
Unlabeled:

for(;;){
   if(){
            break; // ends this loop
       }
}

Labeled:

outer:
for(;;){
    inner:
    for(;;){
        if(){
            break inner; // ends inner loop
        } else {
            break outer; // ends outer loop
        }
    }
}