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 statement then you can get more precise result.
•ORA_ROWSCN can't be used while querying view.
•It is not supported for external tables and also not supported for Flashback Query..
Example:
----------------
The get a rough idea of the rows of table a when the rows were created as well as SCN query as,
SQL> select scn_to_timestamp(ORA_ROWSCN), ORA_ROWSCN , a from a;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ORA_ROWSCN A
--------------------------------------------------------------------------- ---------- ----------
09-JUL-08 06.53.59.000000000 AM 9020925 5
13-JUL-08 06.40.33.000000000 AM 9791113 1
13-JUL-08 06.40.33.000000000 AM 9791113 9
Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts
Monday, January 5, 2009
Arithmatic Operators in Oracle
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:
------------------------------------------------
•The * operator is used to multiply operands and / is used for divide.
•Both are binary operators.
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:
------------------------------------------------
•The * operator is used to multiply operands and / is used for divide.
•Both are binary operators.
when a row is created (ORA_ROWSCN Pseudocolumn)
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 statement then you can get more precise result.
•ORA_ROWSCN can't be used while querying view.
•It is not supported for external tables and also not supported for Flashback Query..
Example:
----------------
The get a rough idea of the rows of table a when the rows were created as well as SCN query as,
SQL> select scn_to_timestamp(ORA_ROWSCN), ORA_ROWSCN , a from a;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ORA_ROWSCN A
--------------------------------------------------------------------------- ---------- ----------
09-JUL-08 06.53.59.000000000 AM 9020925 5
13-JUL-08 06.40.33.000000000 AM 9791113 1
13-JUL-08 06.40.33.000000000 AM 9791113 9
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 statement then you can get more precise result.
•ORA_ROWSCN can't be used while querying view.
•It is not supported for external tables and also not supported for Flashback Query..
Example:
----------------
The get a rough idea of the rows of table a when the rows were created as well as SCN query as,
SQL> select scn_to_timestamp(ORA_ROWSCN), ORA_ROWSCN , a from a;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ORA_ROWSCN A
--------------------------------------------------------------------------- ---------- ----------
09-JUL-08 06.53.59.000000000 AM 9020925 5
13-JUL-08 06.40.33.000000000 AM 9791113 1
13-JUL-08 06.40.33.000000000 AM 9791113 9
Arithmatic Operators in Oracle
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:
------------------------------------------------
•The * operator is used to multiply operands and / is used for divide.
•Both are binary operators.
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:
------------------------------------------------
•The * operator is used to multiply operands and / is used for divide.
•Both are binary operators.
ROWNUM Pseudocolumn in Oracle
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<=10;
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
---------- ----------
1 1
2 2
3 3
SQL> delete from rownum_test where A=2;
1 row deleted.
SQL> insert into rownum_test values(4);
1 row created.
SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3
SQL> insert into rownum_test values(2);
1 row created.
SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3
2 4
To use rownum and return top 4 roes in a ascending order use it as,
SQL> select * from (select * from rownum_test order by a) where rownum<=4;
A
----------
1
2
3
4
Always remember conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows,
SQL> select * from rownum_test where rownum>1;
no rows selected
This is because the first row fetched is assigned a ROWNUM of 1 and makes the condition false as 1is not grater than 1. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and again makes the condition false. Thus all rows subsequently fail to satisfy the condition, so no rows are returned.
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<=10;
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
---------- ----------
1 1
2 2
3 3
SQL> delete from rownum_test where A=2;
1 row deleted.
SQL> insert into rownum_test values(4);
1 row created.
SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3
SQL> insert into rownum_test values(2);
1 row created.
SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3
2 4
To use rownum and return top 4 roes in a ascending order use it as,
SQL> select * from (select * from rownum_test order by a) where rownum<=4;
A
----------
1
2
3
4
Always remember conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows,
SQL> select * from rownum_test where rownum>1;
no rows selected
This is because the first row fetched is assigned a ROWNUM of 1 and makes the condition false as 1is not grater than 1. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and again makes the condition false. Thus all rows subsequently fail to satisfy the condition, so no rows are returned.
ROWNUM Pseudocolumn in Oracle
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<=10;
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
---------- ----------
1 1
2 2
3 3
SQL> delete from rownum_test where A=2;
1 row deleted.
SQL> insert into rownum_test values(4);
1 row created.
SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3
SQL> insert into rownum_test values(2);
1 row created.
SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3
2 4
To use rownum and return top 4 roes in a ascending order use it as,
SQL> select * from (select * from rownum_test order by a) where rownum<=4;
A
----------
1
2
3
4
Always remember conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows,
SQL> select * from rownum_test where rownum>1;
no rows selected
This is because the first row fetched is assigned a ROWNUM of 1 and makes the condition false as 1is not grater than 1. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and again makes the condition false. Thus all rows subsequently fail to satisfy the condition, so no rows are returned.
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<=10;
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
---------- ----------
1 1
2 2
3 3
SQL> delete from rownum_test where A=2;
1 row deleted.
SQL> insert into rownum_test values(4);
1 row created.
SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3
SQL> insert into rownum_test values(2);
1 row created.
SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3
2 4
To use rownum and return top 4 roes in a ascending order use it as,
SQL> select * from (select * from rownum_test order by a) where rownum<=4;
A
----------
1
2
3
4
Always remember conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows,
SQL> select * from rownum_test where rownum>1;
no rows selected
This is because the first row fetched is assigned a ROWNUM of 1 and makes the condition false as 1is not grater than 1. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and again makes the condition false. Thus all rows subsequently fail to satisfy the condition, so no rows are returned.
Concatenation Operator in Oracle
•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), col2 varchar2(30));
Table created.
SQL> insert into test_concat values('Shaik','China India Usa');
1 row created.
SQL> select concat(col1,col2) from test_concat;
CONCAT(COL1,COL2)
----------------------------------------
Shaik china India Usa
SQL> select col1||col2 from test_concat;
COL1||COL2
----------------------------------------
Shaik china India Usa
SQL> select col1||'-'||'Three Countries: '||col2 from test_concat;
COL1||'-'||'THREECOUNTRIES:'||COL2
----------------------------------------------------------
Shaik-Three Countries: Shaik china India Usa
•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), col2 varchar2(30));
Table created.
SQL> insert into test_concat values('Shaik','China India Usa');
1 row created.
SQL> select concat(col1,col2) from test_concat;
CONCAT(COL1,COL2)
----------------------------------------
Shaik china India Usa
SQL> select col1||col2 from test_concat;
COL1||COL2
----------------------------------------
Shaik china India Usa
SQL> select col1||'-'||'Three Countries: '||col2 from test_concat;
COL1||'-'||'THREECOUNTRIES:'||COL2
----------------------------------------------------------
Shaik-Three Countries: Shaik china India Usa
Concatenation Operator in Oracle
•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), col2 varchar2(30));
Table created.
SQL> insert into test_concat values('Shaik','China India Usa');
1 row created.
SQL> select concat(col1,col2) from test_concat;
CONCAT(COL1,COL2)
----------------------------------------
Shaik china India Usa
SQL> select col1||col2 from test_concat;
COL1||COL2
----------------------------------------
Shaik china India Usa
SQL> select col1||'-'||'Three Countries: '||col2 from test_concat;
COL1||'-'||'THREECOUNTRIES:'||COL2
----------------------------------------------------------
Shaik-Three Countries: Shaik china India Usa
•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), col2 varchar2(30));
Table created.
SQL> insert into test_concat values('Shaik','China India Usa');
1 row created.
SQL> select concat(col1,col2) from test_concat;
CONCAT(COL1,COL2)
----------------------------------------
Shaik china India Usa
SQL> select col1||col2 from test_concat;
COL1||COL2
----------------------------------------
Shaik china India Usa
SQL> select col1||'-'||'Three Countries: '||col2 from test_concat;
COL1||'-'||'THREECOUNTRIES:'||COL2
----------------------------------------------------------
Shaik-Three Countries: Shaik china India Usa
Types of SQL function in Oracle
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, which are references to objects of specified object types. A detail explanation of object reference function is found in
E)Model Functions: Model functions can be used only in the model_clause of the SELECT statement. A details explanation of model functions are discussed in
F)User Defined Functions:
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, which are references to objects of specified object types. A detail explanation of object reference function is found in
E)Model Functions: Model functions can be used only in the model_clause of the SELECT statement. A details explanation of model functions are discussed in
F)User Defined Functions:
Types of SQL function in Oracle
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, which are references to objects of specified object types. A detail explanation of object reference function is found in
E)Model Functions: Model functions can be used only in the model_clause of the SELECT statement. A details explanation of model functions are discussed in
F)User Defined Functions:
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, which are references to objects of specified object types. A detail explanation of object reference function is found in
E)Model Functions: Model functions can be used only in the model_clause of the SELECT statement. A details explanation of model functions are discussed in
F)User Defined Functions:
Friday, January 2, 2009
Do not invoke SQL*Plus with a password On UNIX and Linux platforms.
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
Enter user-name: apps@proddb
Enter password: ****
Or
$ sqlplus /nolog
SQL> connect apps@proddb
Enter password: ****
And also, do not use the password while invoking Export/Import Utility using exp/imp command line, and for any other command line utilities which you think the password will be exposed to others.
On Microsoft Windows, the command recall feature (the Up arrow) remembers user input across command invocations.
For example, if you use the CONNECT APPS/password notation in SQL*Plus, exit, and then press the Up arrow to repeat the CONNECT command, the command recall feature discloses the connect string and shows the password. So, it is advice *NOT* to pass the password while connecting to SQL * Plus on windows as well.
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
Enter user-name: apps@proddb
Enter password: ****
Or
$ sqlplus /nolog
SQL> connect apps@proddb
Enter password: ****
And also, do not use the password while invoking Export/Import Utility using exp/imp command line, and for any other command line utilities which you think the password will be exposed to others.
On Microsoft Windows, the command recall feature (the Up arrow) remembers user input across command invocations.
For example, if you use the CONNECT APPS/password notation in SQL*Plus, exit, and then press the Up arrow to repeat the CONNECT command, the command recall feature discloses the connect string and shows the password. So, it is advice *NOT* to pass the password while connecting to SQL * Plus on windows as well.
Do not invoke SQL*Plus with a password On UNIX and Linux platforms.
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
Enter user-name: apps@proddb
Enter password: ****
Or
$ sqlplus /nolog
SQL> connect apps@proddb
Enter password: ****
And also, do not use the password while invoking Export/Import Utility using exp/imp command line, and for any other command line utilities which you think the password will be exposed to others.
On Microsoft Windows, the command recall feature (the Up arrow) remembers user input across command invocations.
For example, if you use the CONNECT APPS/password notation in SQL*Plus, exit, and then press the Up arrow to repeat the CONNECT command, the command recall feature discloses the connect string and shows the password. So, it is advice *NOT* to pass the password while connecting to SQL * Plus on windows as well.
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
Enter user-name: apps@proddb
Enter password: ****
Or
$ sqlplus /nolog
SQL> connect apps@proddb
Enter password: ****
And also, do not use the password while invoking Export/Import Utility using exp/imp command line, and for any other command line utilities which you think the password will be exposed to others.
On Microsoft Windows, the command recall feature (the Up arrow) remembers user input across command invocations.
For example, if you use the CONNECT APPS/password notation in SQL*Plus, exit, and then press the Up arrow to repeat the CONNECT command, the command recall feature discloses the connect string and shows the password. So, it is advice *NOT* to pass the password while connecting to SQL * Plus on windows as well.
Thursday, January 1, 2009
What is SQL?
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.
"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.
What is SQL?
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.
"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.
History of SQL
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.
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.
History of SQL
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.
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.
Subscribe to:
Posts (Atom)
