World of Oracle DBA

Arithmetic operators in oracle can be used to add, subtract, multiply, divide and negate numeric values. The + and - arithmetic operators can also be used in datetime and interval arithmetics. Based on the function we can divide the arithmetic operators in the following types. 1)Unary + and - Operator: ----------------------------------------------- •When + or - denote positive or negative expression then they are called unary operators. •To represent positive or negative expression the + an - operator appear just before operand. •Example of unary operator: Select col1 from table1 where col1>-1; Select col1 from table1 where -col1>1; Here - operator used as unary operator. 2)Binary + and - Operator: --------------------------------------------- •When + and - are used to add or subtract operands then they are called binary operators. •They appear between operands. •Example of binary operator: Select col1 from table1 where col1+col2>10; 3)Binary * and / Operator: ---------------...

To know the exact time of when a row is inserted or updated or deleted you have to use audit system or use logminer utility. But to use these two tool you have to configure your environment previously. However if you don't enable either audit option or supplemental logging (which is required for analysis in logminer) you can still get a rough idea about a row in a table about when it is modified/inserted more precisely the most recent change of a row. This is possible by using ORA_ROWSCN psedocolumn. •ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change for each row. •The resultant value of ORA_ROWSCN is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. •But a better result of the row chaging time of a table can by found by creating tables with row-level dependency tracking. This is done by CREATE TABLE ... ROWDEPENDENCIES statement. If you create your table with this ...

Arithmetic operators in oracle can be used to add, subtract, multiply, divide and negate numeric values. The + and - arithmetic operators can also be used in datetime and interval arithmetics. Based on the function we can divide the arithmetic operators in the following types. 1)Unary + and - Operator: ----------------------------------------------- •When + or - denote positive or negative expression then they are called unary operators. •To represent positive or negative expression the + an - operator appear just before operand. •Example of unary operator: Select col1 from table1 where col1>-1; Select col1 from table1 where -col1>1; Here - operator used as unary operator. 2)Binary + and - Operator: --------------------------------------------- •When + and - are used to add or subtract operands then they are called binary operators. •They appear between operands. •Example of binary operator: Select col1 from table1 where col1+col2>10; 3)Binary * and / Operator: ---------------...

To know the exact time of when a row is inserted or updated or deleted you have to use audit system or use logminer utility. But to use these two tool you have to configure your environment previously. However if you don't enable either audit option or supplemental logging (which is required for analysis in logminer) you can still get a rough idea about a row in a table about when it is modified/inserted more precisely the most recent change of a row. This is possible by using ORA_ROWSCN psedocolumn. •ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change for each row. •The resultant value of ORA_ROWSCN is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. •But a better result of the row chaging time of a table can by found by creating tables with row-level dependency tracking. This is done by CREATE TABLE ... ROWDEPENDENCIES statement. If you create your table with this ...

Oracle selects a row from a table and ROWNUM returns a number indicating the order in which Oracle selects the row from a table. Thus the first row returned by a query has ROWNUM value 1, second row returned by the query has a ROWNUM value 2 and so on. Use of ROWNUM is particularly useful whenever we want to see some rows from a big table. For example in a table there is 10000 row and we want to see the sample of 10 rows. In this case we can use rownum as , SELECT * FROM T WHERE ROWNUM One thing we need to remember that ROWNUM does not ensure any order of the row. To return a row in an order you must have to use order by keyword. The following example will make you clear. SQL> create table rownum_test( a number); Table created. SQL> insert into rownum_test values(1); 1 row created. SQL> insert into rownum_test values(2); 1 row created. SQL> insert into rownum_test values(3); 1 row created. SQL> select a, rownum from rownum_test ; A ROWNUM ---------- -------...

Oracle selects a row from a table and ROWNUM returns a number indicating the order in which Oracle selects the row from a table. Thus the first row returned by a query has ROWNUM value 1, second row returned by the query has a ROWNUM value 2 and so on. Use of ROWNUM is particularly useful whenever we want to see some rows from a big table. For example in a table there is 10000 row and we want to see the sample of 10 rows. In this case we can use rownum as , SELECT * FROM T WHERE ROWNUM One thing we need to remember that ROWNUM does not ensure any order of the row. To return a row in an order you must have to use order by keyword. The following example will make you clear. SQL> create table rownum_test( a number); Table created. SQL> insert into rownum_test values(1); 1 row created. SQL> insert into rownum_test values(2); 1 row created. SQL> insert into rownum_test values(3); 1 row created. SQL> select a, rownum from rownum_test ; A ROWNUM ---------- -------...

•The concatenation operator concatenates operands. •This operator is expressed by (double vertical lines) ||. •This operator operates on character strings and CLOB datatype. •This operator behaves as following way, If both operands are of datatype CHAR, the result holds CHAR datatype and is limited to 2000 characters. If either string has datatype VARCHAR2, the result holds datatype VARCHAR2 and is limited to 4000 characters. If either operand is has datatype CLOB, the result become temporary CLOB. •Trailing or leading blanks are preserved by this operator. Example: SQL> select ' ' || 'test' from dual; ''||'TEST' ----------------- test SQL> select length(' ' || 'test ') from dual; LENGTH(''||'TEST') ------------------ 18 •Instead of || the CONCAT function also be used for concatenation. Example: ----------------- SQL> create table test_concat(col1 varchar2(10), c...

•The concatenation operator concatenates operands. •This operator is expressed by (double vertical lines) ||. •This operator operates on character strings and CLOB datatype. •This operator behaves as following way, If both operands are of datatype CHAR, the result holds CHAR datatype and is limited to 2000 characters. If either string has datatype VARCHAR2, the result holds datatype VARCHAR2 and is limited to 4000 characters. If either operand is has datatype CLOB, the result become temporary CLOB. •Trailing or leading blanks are preserved by this operator. Example: SQL> select ' ' || 'test' from dual; ''||'TEST' ----------------- test SQL> select length(' ' || 'test ') from dual; LENGTH(''||'TEST') ------------------ 18 •Instead of || the CONCAT function also be used for concatenation. Example: ----------------- SQL> create table test_concat(col1 varchar2(10), c...

Oracle functions may be two types SQL functions and user defined functions. SQL functions are built into oracle database. And user defined functions are created by user. Based the task performed by oracle functions we can classify oracle functions in the following. A)Single-Row Functions: Single row functions return just one row for every row of a queried table or view. A detail explanation of single row function is found in B)Aggregate Functions: Aggregate function returns a single row based on a set of rows. A common use of aggregate function is with the GROUP BY clause in a SELECT statement. A detail explanation of aggregate function is found in C)Analytic Functions: Analytic functions compute an aggregate value based on a group of rows. The difference of analytic function from aggregate functions in that they return multiple rows for each group. A detail explanation of analytic function is found in D)Object Reference Functions: Object reference functions manipulate REF values, ...

Oracle functions may be two types SQL functions and user defined functions. SQL functions are built into oracle database. And user defined functions are created by user. Based the task performed by oracle functions we can classify oracle functions in the following. A)Single-Row Functions: Single row functions return just one row for every row of a queried table or view. A detail explanation of single row function is found in B)Aggregate Functions: Aggregate function returns a single row based on a set of rows. A common use of aggregate function is with the GROUP BY clause in a SELECT statement. A detail explanation of aggregate function is found in C)Analytic Functions: Analytic functions compute an aggregate value based on a group of rows. The difference of analytic function from aggregate functions in that they return multiple rows for each group. A detail explanation of analytic function is found in D)Object Reference Functions: Object reference functions manipulate REF values, ...

Most of us sometimes start SQL * Plus with a password on UNIX and Linux platforms without knowing security threat. For example, an application user connects SQL * Plus by passing username and password on Unix/Linux Server. $ sqlplus apps/apps@proddb Here the sqlplus command parameters are very much available for viewing by all operating system users on the same host computer; as a result, password entered on the command line could be exposed to other users, as below. $ ps -efgrep sqlplus oracle 14490 2190 0 16:31:53 pts/5 0:00 sqlplus apps/apps@proddb oracle 14493 14491 0 16:32:01 pts/5 0:00 grep sqlplus So, there might be a chance for an intruder to know the user id and password, and can connect to the database using that credentials. Then, following is the secure and best way of connecting SQL * Plus where the password is not exposed on the command line. $ sqlplus apps@proddb Enter password: **** Or, even not to expose the username and connecting string. $ sqlplus Ente...

Most of us sometimes start SQL * Plus with a password on UNIX and Linux platforms without knowing security threat. For example, an application user connects SQL * Plus by passing username and password on Unix/Linux Server. $ sqlplus apps/apps@proddb Here the sqlplus command parameters are very much available for viewing by all operating system users on the same host computer; as a result, password entered on the command line could be exposed to other users, as below. $ ps -efgrep sqlplus oracle 14490 2190 0 16:31:53 pts/5 0:00 sqlplus apps/apps@proddb oracle 14493 14491 0 16:32:01 pts/5 0:00 grep sqlplus So, there might be a chance for an intruder to know the user id and password, and can connect to the database using that credentials. Then, following is the secure and best way of connecting SQL * Plus where the password is not exposed on the command line. $ sqlplus apps@proddb Enter password: **** Or, even not to expose the username and connecting string. $ sqlplus Ente...

The word SQL is the abbreviated form of Structured Query Language. Many one who are new mix SQL with PL/SQL or with SQL*Plus. There is almost no relation between SQL and SQL*plus. SQL*plus is simply a tool to which sql command is written. I define SQL in following way, "An SQL can be said as a set of statements or commands through which you access database." In fact whether application programs or any tools that access database they use SQL language.

The word SQL is the abbreviated form of Structured Query Language. Many one who are new mix SQL with PL/SQL or with SQL*Plus. There is almost no relation between SQL and SQL*plus. SQL*plus is simply a tool to which sql command is written. I define SQL in following way, "An SQL can be said as a set of statements or commands through which you access database." In fact whether application programs or any tools that access database they use SQL language.

In June 1970, Edgar F. Codd published the paper "A Relational Model of Data for Large Shared Data Banks". Based on Codd's model introduced in his paper, a group at IBM's San Jose research center developed the System R relational database management system. Later on, two members of IBM named Donald D. Chamberlin and Raymond F. Boyce subsequently created the Structured English Query Language (SEQUEL) to manipulate and manage data stored in System R database. Later the name SEQUEL was changed to SQL because SEQUEL was a trademark of the UK-based Hawker Siddeley aircraft company. In 1979, Relational Software, Inc. (now Oracle Corporation) introduced the first commercially available implementation of SQL. That was the Oracle version 2 and was available for VAX computer.

In June 1970, Edgar F. Codd published the paper "A Relational Model of Data for Large Shared Data Banks". Based on Codd's model introduced in his paper, a group at IBM's San Jose research center developed the System R relational database management system. Later on, two members of IBM named Donald D. Chamberlin and Raymond F. Boyce subsequently created the Structured English Query Language (SEQUEL) to manipulate and manage data stored in System R database. Later the name SEQUEL was changed to SQL because SEQUEL was a trademark of the UK-based Hawker Siddeley aircraft company. In 1979, Relational Software, Inc. (now Oracle Corporation) introduced the first commercially available implementation of SQL. That was the Oracle version 2 and was available for VAX computer.