Oracle in World: May 2008

Saturday, May 31, 2008

How does one prevent Oracle from using an Index?

Autotrace Option:
------------------------
SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY: Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

Now look explain plan of a query which will use index.
SQL> SELECT COUNT(*) FROM TEST WHERE A=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2811351645

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX UNIQUE SCAN| SYS_C005994 | 1 | 3 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Now we can prevent oracle from using index in several methods.

Method 1:Adding an expression to the indexed column:
------------------------------------------------------------------------
SQL> SELECT COUNT(*) FROM TEST WHERE A+0=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 160014765

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| SYS_C005994 | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Method 2:Specifying the FULL hint to force full table scan:
--------------------------------------------------------------------------------------
SQL> select /*+ FULL(TEST)*/ * from TEST WHERE A=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 8 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Method 3:Specifying NO_INDEX hint
------------------------------------------------------------------
SQL> select /*+ NO_INDEX(TEST) */ count(*) from test where A=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Method 4:Using a function over the indexed column
-------------------------------------------------------------------
SQL> select count(*) from test where to_number(A)=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 160014765

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| SYS_C005994 | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Hidden Parameters in Oracle. How to Change Hidden Parameter

The hidden parameters start with an "_".They can not be viewed from the output of show parameter
or querying v$parameter unless and untill they are set explicitly in init.ora.
However if you want to view all the hidden parameters and their default values the following query
could be of help,

SELECT
a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE
a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_%' escape '/'
/

In order to see the listing of all hidden parameters query,

select *
from SYS.X$KSPPI
where substr(KSPPINM,1,1) = '_';

Change Hidden Parameters in Oracle
---------------------------------------
It is never recommended to modify these hidden parameters without the assistance of Oracle Support.Changing these parameters may lead to high performance degradation and other problems in the database.
In order to change hidden parameter,
1)If you use pfile then in your initSID.ora you can entry of the hidden parameter and start the database.

2)If you want to use for the current session you can use ALTER SESSION SET ....

3)To set it permanently if you use spfile then use, ALTER SYSTEM SET ...... SCOPE=SPFILE. Since hidden parameter starts with underscore(_) to access it you have to specify within double quotes. If you use SCOPE=SPFILE then in order to take effect you need to restart database. You can use SCOPE=BOTH if parameter can be set in the session also.

How to see Explain Plan from SQL*Plus

Method 1: Autotrace Facility

When the AUTOTRACE setting of SQL*Plus is enabled, SQL*Plus will print an EXPLAIN PLAN and execution statistics after each SQL statement.
Here is an example, where AUTOT is abbreviation of AUTOTRACE


SQL>SET AUTOT ON
SQL>SELECT * FROM TEST WHERE A=1;

A B
---------- ----------
1 USA


Execution Plan
----------------------------------------------------------
Plan hash value: 2109870067

--------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
Time     |

--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |     8 |     1   (0)|
00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |     8 |     1   (0)|
00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | SYS_C005994 |     1 |       |     0   (0)|
00:00:01 |

-------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=1)


Statistics
----------------------------------------------------------
147  recursive calls
0  db block gets
26  consistent gets
6  physical reads
0  redo size
439  bytes sent via SQL*Net to client
458  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

To trace the same statement without displaying the query data, enter:

SQL>SET AUTOTRACE TRACEONLY

To off trace issue,
SET AUTOT OFF
Method 2: DBMS_XPLAN Package

SQL>EXPLAIN PLAN FOR select * from test where a=1;
Explained.

SQL>set linesize 140
SQL>SELECT * FROM TABLE( dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 2109870067

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |     8 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C005994 |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------

2 - access("A"=1)

14 rows selected.

Thursday, May 29, 2008

Change Prompt in SQL*Plus

Whenever you log on to SQL*Plus it shows you the prompt SQL>. However you can change it for temporary or for permanent.

To change SQL prompt permanently set environmental variable inside glogin.sql.
To set SQL prompt for a particular OS user set it inside login.sql.
To make it temporary set within SQL*Plus. Like,

To change your SQL*Plus prompt to display your connection identifier, enter:

SET SQLPROMPT "_CONNECT_IDENTIFIER > "
dbase >
To set the SQL*Plus command prompt to show the current user, enter

SET SQLPROMPT "_USER > "
ARJU >

To change your SQL*Plus prompt to display your the current date, the current user and the users privilege level, enter:

SET SQLPROMPT "_DATE _USER _PRIVILEGE> "
01-JUN-08 ARJU >

You can also set a variable as you wish
set SQLPROMPT "JUST For Fun>"
JUST For Fun>

Text in nested quotes is not parsed for substitution. To have a SQL*Plus prompt of your username, followed by "@", and then your connection identifier, enter:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

Related Documents:
-------------------------------------------------------
How to set Environmental variable to SQL*Plus permanently

Sql*plus Basics and Tips

Use of bind variables in Sql*plus

Say yes to SqlPlus / SqlPlus Tips

Working with Sql*plus Scripts

Controlling the Autotrace Report in sql*plus

What is the difference between $ ! and HOST?

Formatting SQL*Plus Reports Part 1

Formatting SQL*Plus Reports Part 2

How to set Environmental variable to SQL*Plus automatically

When SQL*Plus starts up, it looks for a global login script called glogin.sql in the $ORACLE_HOME/sqlplus/admin directory. If found, this script will be executed. If you want to set your SQL*Plus environmental variable like PAGESIZE or LINESIZE or SQLPROMPT then you can set within the file glogin.sql. Then all of the database users who use SQL*Plus will be affected of this site profile glogin.sql.

After checking site profile oracle checks for user profile named login.sql in the user's home. If you want to set environmental variable for a particular Oracle user then you can use user profile named login.sql. Other users of the OS will not be affected. You will create login.sql, put environmental variables inside it and place it to the user's home location.

In the following section I demonstrate the procedure with an example.

AT first I connected to database without any login.sql file. And then it displays prompt as SQL>
-bash-3.00$ sqlplus arju/a

SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 31 23:27:34 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Now I am creating login.sql and put an entry of sql prompt which will show username@database_name SQL>
SQL> !vi login.sql
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER SQL>"

SQL>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Now connect to database and see the prompt.
-bash-3.00$ sqlplus arju/a
SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 31 23:28:56 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

ARJU@dbase SQL>

Along with connection identifier you set set other environmental variables like pagesize linesize for a user or globally.

Related Documents:
-------------------------------------------------------

Sql*plus Basics and Tips

Use of bind variables in Sql*plus

Say yes to SqlPlus / SqlPlus Tips

Working with Sql*plus Scripts

Controlling the Autotrace Report in sql*plus

What is the difference between $ ! and HOST?

Formatting SQL*Plus Reports

Formatting SQLPlus Reports Part 2
Change Prompt in SQLPlus

What is the difference between $ ! and HOST?

Actually in terms of functionality there is no difference between $,! and host. They all perform the same task which is to execute Operating system command. Whenever we write any one of these inside SQL*Plus, SQL*Plus will execute operating system commands as child processes of SQL*Plus. Individually enter anyone of these like only host or ! will enter you into a OS prompt. But it does not affect the current SQL*Plus session. After you terminate the session you again enter to SQL*session.

Example:
-----------------------------------
SQL> host
bash-3.00$ hostname
neptune
bash-3.00$ exit
exit

SQL> --SQL prompt is returned

The host command is more generic. You can use it on any platform.
The ! command is for unix operating system only.
The $ command is for OS/390, VMS, and Windows OS only.

Example:
-------------------
SQL> !pwd
/export/home/oracle

Related Documents:
-------------------------------------------------------
How to set Environmental variable to SQL*Plus permanently

Sql*plus Basics and Tips

Use of bind variables in Sql*plus

Say yes to SqlPlus / SqlPlus Tips

Working with Sql*plus Scripts

Controlling the Autotrace Report in sql*plus

Formatting SQL*Plus Reports

Formatting SQLPlus Reports Part 2
Change Prompt in SQLPlus

What is the difference between & and &&?

Both & and && is used in a query in order to substitute variables.
"&" is used to create a temporary substitution variable that will prompt you for a value every time it is referenced. Example:


SQL> select * from test where b='&Country_name';
Enter value for country_name: USA
old   1: select * from test where b='&Country_name'
new   1: select * from test where b='USA'

         A B
---------- ----------
         1 USA
SQL> /
Enter value for country_name: INDIA
old   1: select * from test where b='&Country_name'
new   1: select * from test where b='INDIA'

         A B
---------- ----------
         2 INDIA

"&&" is used to create a permanent substitution variable. Once you have entered a value (defined the variable) its value will used every time the variable is referenced. Example:


SQL>  select * from test where b='&&Country_name';
Enter value for country_name: INDIA
old   1:  select * from test where b='&&Country_name'
new   1:  select * from test where b='INDIA'

         A B
---------- ----------
         2 INDIA
SQL>  select * from test where b='&&Country_name';
old   1:  select * from test where b='&&Country_name'
new   1:  select * from test where b='INDIA'

         A B
---------- ----------
         2 INDIA

SQL> /
old   1:  select * from test where b='&&Country_name'
new   1:  select * from test where b='INDIA'

         A B
---------- ----------
         2 INDIA

As we see subsequent run does not prompt for to provide country name if you use && and once we provide value.

The "&&" will actually define the variable similarly to what the DEFINE command does.

If we now invoke define command it will return output as,
SQL> define
DEFINE COUNTRY_NAME = "INDIA" (CHAR)

In stead of && we can also use DEFINE to define a variable like,


SQL> DEFINE COUNTRY_NAME    = "USA"
SQL> select * from test where b='&&Country_name'
  2  ;
old   1: select * from test where b='&&Country_name'
new   1: select * from test where b='USA'

         A B
---------- ----------
         1 USA

Wednesday, May 28, 2008

What is the difference between @ and @@ in Sql*plus?

From Sql*plus we can execute any script with START command. @ is similar to START. If you write SQL>@file.txt then the contents inside file.txt will be executed.
The command @@ is almost identical to the @ ("at" sign) command.

The only difference is @ is used to executed a script on the current location. And @@ is used to execute a script from within a script in the same directory as the script resides.

When you enter @@file_name.ext from within a script, SQL*Plus runs file_name.ext from the same directory as the script.

When you enter @@file_name.ext interactively, SQL*Plus runs file_name.ext from the current working directory or from the same url as the script from which it was called.

I will make more clear with an example.

I created two scripts. script1.sql and script2.sql. Script1 contains desc test; and script2 contains select * from test. Default location is /export/home/oracle.
A)Create Two Scripts.
SQL> !pwd
/export/home/oracle

SQL> !vi script1.sql
desc test;

SQL> !vi script2.sql
select * from test;
B)Execute Scripts by @ and @@
SQL> @script2
A
----------
1
2

SQL> @@script1
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER

At this stage @ and @@ works in the same way.

C)Now create a new script script3 which contains script2 and script1
SQL> !vi script3.sql
@script2

@@script1

SQL> @script3
A
----------
1
2
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
So it works . Edit script3 now and run then.
SQL> !vi script3.sql
@@script2

@@script1
SQL> @script3
A
----------
1
2

Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER

So there is no difference between @ and @@ up to this moment.

D)Move script3 to another location.
SQL> !mv script3.sql /oradata2/script3.sql
Now execute.
SQL> @ /oradata2/script3.sql
SP2-0310: unable to open file "/oradata2/script2.sql"
SP2-0310: unable to open file "/oradata2/script1.sql"

Since I have moved script 3 to /oradata2/script3.sql and I run so @@ search the location as the location of script3.sql but did not found.

Now edit script3 and put@ replacing @@

SQL> !vi /oradata2/script3.sql
@script2

@script1
Now execute.
SQL> @ /oradata2/script3.sql
A
----------
1
2
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER

And we see it executed fine. As @ searches file script1 and script 2 to present working directory not to the location where script script3 resides.

Related Documents:
-------------------------

How can one pass operating system parameters to SQL*Plus?

Suppose you want to pass Operating System parameter var1, var2 to Sql*plus. Then you can write commnad as,
sqlplus username/password @cmdfile.sql var1 var2
Here OS parameter var1 will be mapped to SQL*Plus variable &1 var2 will be mapped to &2.
With an example I will demonstrate the procedure. This example will take two parameter SYDATE and dual will will display current date.

1)Create a sql file.
-bash-3.00$ vi x.sql
select &1 from &2;
exit ;
2.Invoke the sqlplus with parameters.
-bash-3.00$ sqlplus arju/a @x.sql SYSDATE dual
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 02:13:58 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

old 1: select &1 from &2
new 1: select SYSDATE from dual

SYSDATE
---------
29-MAY-08

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Here sysdate is returned.

Where is my Column data? SQL*plus does not show

If you have any CLOB or NCLOB or LONG column in a table then while you query the column from SQL*plus the column data is truncated before displayed. SQL*plus by default only shows the first 80 bytes of any LONG, CLOB and NCLOB datatypes. The data is actually in the table, but since sqlplus tries not to print out too much data so it displays 80 bytes only.

We can change this behavour of SQL*plus by setting SET LONG. With an example I have demonstrate this behavior.
SQL> CREATE TABLE LONG_TEST(COL1 CLOB);
Table created.

SQL> INSERT INTO LONG_TEST VALUES('This line is more than 80 bytes. So some of the parts of this line will not be shown if I don''t set LONG');
1 row created.

SQL> SELECT * FROM LONG_TEST;

COL1
--------------------------------------------------------------------------------
This line is more than 80 bytes. So some of the parts of this line will not be s

SQL> SET LONG 500
SQL> SELECT * FROM LONG_TEST;
COL1
--------------------------------------------------------------------------------
This line is more than 80 bytes. So some of the parts of this line will not be s
hown if I don't set LONG

How to find the User who is connected to Oracle

To know about the list of users that is now connected and the time when they connect to database issue the following query.

SQL> SELECT s.username, s.program, s.logon_time
FROM v$session s, v$process p, sys.v_$sess_io si
WHERE s.paddr = p.addr(+)
AND si.sid(+) = s.sid
AND s.type = 'USER';
USERNAME PROGRAM LOGON_TIM
---------- ------------------------------------------------ ---------
ARJU sqlplus@neptune (TNS V1-V3) 28-MAY-08
oracle@neptune (J000) 28-MAY-08

To know only the current session user name you can issue the following query,

SQL> SELECT USERNAME from v$session where audsid = sys_context('userenv','sessionid');
USERNAME
------------------------------
ARJU

To know about on which schema you are connecting issue following query.

SQL> SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
ARJU

To know about the connected user user for the current session issue,
SQL> SELECT sys_context('USERENV', 'SESSION_USER') FROM dual;
SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
ARJU

If you use SQL*plus then simply issue,
SQL> SHOW USER;
USER is "ARJU"

Related Documents
Get IP Address from hostname within Oracle Database
How to find current session ID
How to know which objects are being accessed by a user

How to Rename a User Through Oracle

There is nothing straight forward command in oracle to rename a user. You have to do it through several steps. In following I have shown it.

1)Determine the user or schema which you want to rename. Suppose you want to rename ARJU user to ARJUUSA.

2)Export the objects the the desired user to a dump file. You can use either data pump export (expdp) or exp(original export). If you use data pump then export as

$expdp schemas=ARJU

If you use original export you can use as,
$exp owner=ARJU

To know more about data pump export and original export please have a look at,
Data Pump Export Import
Original Export Import

3)Create new User ARJUUSA.
SQL>CREATE USER ARJUUSA IDENTIFIED BY A;

4)Drop user Arju.
SQL>DROP USER ARJU CASCADE;

5)Import the Objects in the dumpfile from Arju to ARJUUSA.
If you used expdp then use,
$impdp REMAP_SCHEMA=ARJU:ARJUUSA
If you used exp then use,
$imp FROMUSER=ARJU TOUSER=ARJUUSA

Special Notes
Note that you may think that you can rename a user directory by updating name column from USER$ table and then changing the password of the newly updated user. But this is not supported system by oracle. So avoid it. Below is a test scenario of this method but don't use it as oracle does not support it.

SQL> conn t/t@203.88.110.25:1522/maestro
Connected.

SQL> create table test(a number primary key);
Table created.

SQL> create table test2(a number ,foreign key (a) references test);
Table created.

SQL> create or replace procedure test_p as begin dbms_output.put_line('Test'); end;
2  /
Procedure created.
SQL> col object_name for a20
SQL> select object_name, object_type from user_objects;

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
SYS_C0011082         INDEX
TEST                 TABLE
TEST2                TABLE
TEST_P               PROCEDURE

SQL> conn sys/a@203.88.110.25:1522/maestro as sysdba
Connected.

SQL> update user$ set name='T_NEWNAME' where name='T';
1 row updated.

SQL> alter user T_NEWNAME identified by a;
User altered.

SQL> conn t_newname/a@203.88.110.25:1522/maestro
Connected.

SQL> select object_name, object_type, status from user_objects;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
SYS_C0011082         INDEX               VALID
TEST                 TABLE               VALID
TEST2                TABLE               VALID
TEST_P               PROCEDURE           VALID

Related Documents
Data Pump Export Import
Original Export Import

How to Export data to a flat file

Whenever you want to move data from oracle to other software products like SQL SERVER or MYSQL or any other database software then it is needed at first to move data to a flat file. Flat file is an OS file like a text file. Also, if you don't have oracle net then for moving data from lower verion to upper version you can also use this method - first save data to a flat file and then using SQL*Loader or external table transfer data into database.

To illustrate the system I have create table test_spool and insert data into it.

A)SQL> create table test_spool( a number, b varchar2(10),c varchar2(30));
Table created.

SQL> insert into test_spool values(1,'Oracle','Bangladesh , India and USA');
1 row created.

SQL> select * from test_spool;
A B C
---------- ---------- ------------------------------
1 Oracle Bangladesh , India and USA

Now Set the following environmental variables of SQL*Plus.
B)SQL> SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
SET COLSEP " "
I used COLSEP in order to separate column inside flat file.
Now spool it and run the query.
C)SQL> spool output_to_flat_file.txt
SQL> select * from test_spool;
1 Oracle Bangladesh , India and USA
SQL> spool off
See the contents of the file now.

D)SQL> !cat output_to_flat_file.txt
SQL> select * from test_spool;
1 Oracle Bangladesh , India and USA
SQL> spool off

Use it as you like.

In order to save the query to an html file you can use as follows.

SET HEADING ON
SET MARKUP HTML ON SPOOL OFF
SPOOL /oradata2/a.html
SELECT * FROM LOGIN WHERE ROWNUM<5;
SPOOL OFF

To save it in excel file paste this output to an excel file.

What is the overall database size

An oracle database consists of data files, redo log files, control files, temporary files. Whenever you say the size of the database this actually means the summation of these files.

The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:
select sum(bytes)/1024/1024 "Meg" from dba_data_files;

To get the size of all TEMP files:
select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

To get the size of the on-line redo-logs:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

To get the size of the control files use,
SQL> select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) "MEG" from v$controlfile;

So to get the total size of the database just sum these.

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;

Related Documents

Maximum Oracle Database Size.

Choose an optimal Data block size in Oracle

How to determine OS block size for windows or unix

Estimate the dumpfile size before taking dump using data pump export

Settting maximum Size of Backup Set or Backup Piece

Special Characters in Unix

A) The UNIX shell interprets a number of characters in special ways. These characters are most often used with UNIX commands - as arguments and for other means. The following list contains most of UNIX's special characters.

NEWLINE - initiates command execution
; - separates commands on same line
( ) - groups commands or identifies a function
& - executes a command in the background
| - pipe
> - redirects standard output
>> - appends standard output
< - redirects standard input * - wildcard for any number of characters in a file name ? - wildcard for a single character in a file name \ - quotes the following character ' - quotes a string preventing all substitutions " - quotes a string allowing variable and command substitution ` - performs command substitution [ ] - denotes a character class in a file name $ - references a variable { } - command grouping within a function . - executes a command (if at beginning of line) # - begins a comment : - null command B) Examples
-------------------
1. Use the * character in file names to match any number of characters. The following command:
ls *.txt

Will match the following files:
chapter1.txt doc.txt memo.txt a.txt
Will not match the following files:
doctxt txt.memo

2. Use the ? character in file names to match any single character. The following command:

ls ???.txt

Will match the following files:
one.txt doc.txt two.txt
Will not match the following files:
chap1.txt doctxt

3. Use the [ ] characters in file names to match any character within a range of characters. The following command:

ls chapter[1-3].txt

Will match the following files:
chapter1.txt chapter2.txt chapter3.txt
Will not match the following files:
chap1.txt chapter4.txt

If you want to know the details about these special characters then have a look at,

Dot (.) in linux and shell script
Semicolon (;) and double semicolon (;;) in shell script
Hash sign (#) in shell script
Single quote, double quote and comma in shell script
Forward slash and backslash in shell script and linux
Colon (:) character in shell script and Linux
Exclamation mark in shell script and linux
Asterisk (*) in shell script and linux
Question mark (?) in linux and shell script
Dollar sign ($) in shell script

Vi Command in Unix.

A)Entering vi
---------------------------
vi filename - The filename can be the name of an existing file or the name of the file you want to create.

view filename - Starts vi in "read only" mode. Allows you to look at a file without the risk of altering its contents.

B)Exiting vi
-----------------------
:q - quit - if you have made any changes, vi will warn you of this, and you'll need to use one of the other quits.
:w - write edit buffer to disk
:w filename - write edit buffer to disk as filename
:wq - write edit buffer to disk and quit
ZZ - write edit buffer to disk and quit
:q! - quit without writing edit buffer to disk

C)Positioning within text
-----------------------------------------
By character
left arrow - left one character
right arrow - right one character
backspace - left one character
space - right one character
h - left one character
l - right one character

By word
w - beginning of next word
nw - beginning of nth next word
b - back to previous word
nb - back to nth previous word
e - end of next word
ne - end of nth next word

By line
down arrow - down one line
up arrow - up one line
j - down one line
k - up one line
+ - beginning of next line down
- - beginning of previous line up
0 - first column of current line (zero)
^ - first character of current line
$ - last character of current line

By block
( - beginning of sentence
) - end of sentence
{ - beginning of paragraph
} - end of paragraph

By screen
CTRL-f - forward 1 screen
CTRL-b - backward 1 screen
CTRL-d - down 1/2 screen
CTRL-u - up 1/2 screen
H - top line on screen
M - mid-screen
L - last line on screen

Within file
nG - line n within file
1G - first line in file
G - last line in file

D)Inserting text
--------------------------------
a - append text after cursor *
A - append text at end of line *
i - insert text before cursor *
I - insert text at beginning of line *
o - open a blank line after the current line for text input *
O - open a blank line before the current line for text input *

* Note: hit ESC (escape) key when finished inserting!

E)Deleting text
-----------------------------
x - delete character at cursor
dh - delete character before cursor
nx - delete n characters at cursor
dw - delete next word
db - delete previous word
dnw - delete n words from cursor
dnb - delete n words before cursor
d0 - delete to beginning of line
d$ - delete to end of line
D - delete to end of line
dd - delete current line
d( - delete to beginning of sentence
d) - delete to end of sentence
d{ - delete to beginning of paragraph
d} - delete to end of paragraph
ndd - delete n lines (start at current line)

F)Changing text
--------------------------------
cw - replace word with text *
cc - replace line with text *
c0 - change to beginning of line *
c$ - change to end of line *
C - change to end of line *
c( - change to beginning of sentence *
c) - change to end of sentence *
c{ - change to beginning of paragraph *
c} - change to end of paragraph *
r - overtype only 1 character
R - overtype text until ESC is hit *
J - join two lines

* Note: hit ESC (escape) key when finished changing!

G)Copying lines
-----------------------------
yy - "yank": copy 1 line into buffer
nyy - "yank": copy n lines into buffer
p - put contents of buffer after current line
P - put contents of buffer before current line

H)Moving lines (cutting and pasting)
----------------------------------------------
ndd - delete n lines (placed in buffer)
p - put contents of buffer after current line
P - put contents of buffer before current line

I)Searching / Substituting
---------------------------------------
/str - search forward for str
?str - search backward for str
n - find next occurrence of current string
N - repeat previous search in reverse direction

The substitution command requires a line range specification. If it is omitted, the default is the current line only. The examples below show how to specify line ranges.

:s/old/new - substitute new for first occurrence of old in current line
:s/old/new/g - substitute new for all occurrences of old in current line
:1,10s/old/new - substitute new for first occurrence of old in lines 1 - 10
:.,$s/old/new - substitute new for first occurrence of old in remainder of file
:.,+5s/old/new - substitute new for first occurrence of old in current line and next 5 lines
:.,-5s/old/new - substitute new for first occurrence of old in current line and previous 5 lines
:%s/old/new/g - substitute new for all occurrences of old in the entire file
:%s/old/new/gc - interactively substitute new for all occurrences of old - will prompt for y/n response for each substitution.

J)Miscellaneous commands
-----------------------------------
u - undo the last command (including undo)
. - repeat last command
xp - swap two adjacent characters
m[a-z] - set a marker (a - z)
'[a-z] - go to a previously set marker (a - z)
:!command - execute specified UNIX command
:r filename - read/insert contents of filename after current line.
:1,100!fmt - reformat the first 100 lines
:!fmt - reformat the entire file

Tuesday, May 27, 2008

ORA-39014 ORA-39029 ORA-31672 One or more workers have prematurely exited

Error Description:
-------------------------
Whenever you tried to import data into the database it failed with error as below.

Processing object type DATABASE_EXPORT/DE_SYSTEM_PROCOBJACT/DE_PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/DE_SYSTEM_PROCOBJACT/DE_POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.
Job "SYSTEM"."SYS_IMPORT_FULL_03" stopped due to fatal error at 14:13
ORA-39014: One or more workers have prematurely exited.

Cause of The Problem:
-------------------------
This problem is likely to happen as because of the parallel settings. If you have only 1 dump file in the dump file set and you give PARALLEL parameter to a higher value then this error may occur. The parallelism setting should be less than or equal to the number of dump files in the dump file set.

Solution of The Problem:
-------------------------------------
The parallelism setting (specified with the PARALLEL parameter) should be less than or equal to the number of dump files in the dump file set. If there are not enough dump files, the performance will not be optimal because multiple threads of execution will be trying to access the same dump file.

As workaround do import will PARALLEL=1.

Types of View and Rules for performing DML Operations on a view

Benefits of using views

•With views you can restrict database access in both column level and row level. Selective portion of tables can be hidden with view.

•Creating complex view and query on them allow you to make complex query easy. They allow you to make simple queries to retrieve the results from complicated queries.

•Views can be used to allow data independence for ad hoc users and application programs.

•You can present different views of the same data according to their particular criteria.

•For experiment you can just create view and query on them instead of creating temporary tables and dropping them while significantly reduce database fragmentation.

SYSDBA and SYSOPER authorized operations.

SYSDBA is the highest system privilege in oracle. But SYSOPER system privilege is limited than SYSDBA. In the following section, I listed the operation that a user can perform who is authenticated as SYSDBA and SYSOPER system privilege.

SYSDBA:
• Perform STARTUP and SHUTDOWN operations
• ALTER DATABASE: open, mount, back up, or change character set
• CREATE DATABASE
• DROP DATABASE
• CREATE SPFILE
• ALTER DATABASE ARCHIVELOG
• ALTER DATABASE RECOVER
• Includes the RESTRICTED SESSION privilege
• Allows a user to connect as user SYS.

SYSOPER
• Perform STARTUP and SHUTDOWN operations
• CREATE SPFILE
• ALTER DATABASE OPEN/MOUNT/BACKUP
• ALTER DATABASE ARCHIVELOG
• ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)
• Includes the RESTRICTED SESSION privilege
• Allows a user to perform basic operational tasks, but without the ability to look at user data.

Required users and groups while installing oracle

In this topic I will describe about the users and groups that are needed to install oracle on linux or solaris machine.

Groups Needed
-----------------------------------------------
Mandatory Group- The OSDBA Group(dba): You must create this group the first time you install Oracle Database software on the system. It identifies operating system user accounts that have database administrative privileges (the SYSDBA privilege). We later can enter as sysdba if current user is a group of OSDBA. The default name for this group is dba.

Optional Group-The OSOPER group (oper): The users of this group have a limited set of database administrative privileges (the SYSOPER privilege). This is optional group.

To know more about SYSDBA and SYSOPER authenticated operations have a look at SYSDBA and SYSOPER Authenticated Operations.

Mandatory Group-The Oracle Inventory group (oinstall): This group owns the Oracle
inventory, which is a catalog of all Oracle software installed on the system. If Oracle software is already installed on the system, then the existing Oracle Inventory group must be the primary group of the operating system user that you use to install new Oracle software.

Users Needed:
--------------------------
An unprivileged user (nobody):Verify that the unprivileged user nobody exists on the system. The nobody user must own the external jobs (extjob) executable after the installation.

The Oracle software owner user (typically, oracle): You must create this user the first time you install Oracle software on the system.This user owns all of the software installed during the installation. This user must have the Oracle Inventory group as its primary group. It must also have the OSDBA and OSOPER groups as secondary groups.

Related Documents:
----------------------------
DBA Authentication

Memory Usage of Solaris Operating System

Kernel Memory Usage:

In order to know kernel memory usage issue kstat | grep pp_kernel which will return output as 8k pages.

Example:


$kstat | grep pp_kernel
       pp_kernel                       83613

Note that the size displayed here in bytes.
On sql*plus you can convert this size into megabytes.


SQL> SELECT 83613*8/1024 "MEMORY IN MB" FROM DUAL;

MEMORY IN MB
------------
 653.226563

Current Statistics:

To know about active process statistics along with memory usage use,
# prstat -a -v

Virtual Memory Statistics

In order to get an idea about virtual memory statistics use


# vmstat 5
kthr      memory            page            disk          faults      cpu
r b w   swap  free  re  mf pi po fr de sr cd s0 -- --   in   sy   cs us sy id
0 0 0 4113216 483876 38 273 6  0  0  0  1  7 -0  0  0  569  986  384  1  1 99

Here 5 indicate after 5 seconds statistics will be shown.

Related Documents
Memory Usage in Linux OS. First part of the document
How to change/configure IP Address on Linux/ Fedora /Ubuntu
Copy files between Unix and Windows with rcp
Screen -A very useful unix tool to work with remote system
Different ways to take Screenshot on Linux
How to change the hostname in Linux

Password Verification or Complexity function in Oracle

In database you may wish to set up rules how a user password will be. You can make password complex as you like. Suppose a common rule is username and password must not be same. To do so you have to do following tasks.

1)Create a password verification function.
2)Assign this verification function to your desired profile or system default profile.
3)Assign this profile to the users to whom you want to impose rules.

Oracle itself has a sample PL/SQL scripts by which we can impose password complexity. The script UTLPWDMG.SQL sets the default profile parameters. The oracle sample password verification function ensures that the password meets the following requirements.

•Is at least four characters long
•Differs from the user name
•Has at least one alpha, one numeric, and one punctuation mark character
•Is not simple or obvious, such as welcome, account, database, or user
•Differs from the previous password by at least 3 characters

The default complexity function can be seen as $ORACLE_HOME/rdbms/admin/utlpwdmg.sql location. Just open the file with any editor and modify you need any.

Also look at the last few lines of the script. It also assign your default profile DEFAULT to a password verification function verify_function. However you can imply to verify other profile rather than DEFAULT by comment out the last paragraph of the script. Then execute the script. You must be a SYS user in order to execute password verification function.

With an example I have demonsrate the scenario.

1)Connect as a SYS user.

SQL> CONN / AS SYSDBA
Connected.

2)Execute the utlpwdmg.sql script.
SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
Function created.

3)Create the profile with this function. Like,
SQL> CREATE PROFILE TEST_PROFILE LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
Profile created.

4)Assign this profile to a user.
SQL> ALTER USER INDIA PROFILE TEST_PROFILE;
User altered.

5)Connct the user to which profile is assigned.
SQL> CONN INDIA/T
Connected.

6)Check wether password complexity function work or not by.
SQL> PASSWORD
Changing password for INDIA
Old password:
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 4

Password unchanged

Suppose I gave here 1!ab for new password and it worked ok.
SQL> passw
Changing password for INDIA
Old password:
New password:
Retype new password:
Password changed

Related Documents

http://arjudba.blogspot.com/2008/04/password-is-case-sensitive-in-oracle.html

http://arjudba.blogspot.com/2008/05/playing-with-oracle-password-identified.html

http://arjudba.blogspot.com/2008/07/solution-of-ora-28002-password-will.html

http://arjudba.blogspot.com/2008/07/ora-28003-ora-20002-while-changing.html

Monday, May 26, 2008

What is Roles in Oracle

• A role in oracle is a named groups of related privilege. Suppose the privilege CREATE TABLE,CREATE SESSION can be assigned to a role name. After assign whenever we give any access via that role the underlying privilege will be assigned.

• A role can be granted to a user or to another role.

• Within a database, each role name must be unique, different from all user names and all other
role names.

• Roles are not contained in any schema. So if the user who created the role is deleted, the role created by him remain in tact.

• Roles significantly helps to ease of administration tasks. Rather than assign a lists of privilege to a user we can create role with these privilege and just assign this role to the specified user.

• Oracle Predefined roles are,
CONNECT
RESOURCE
DBA
EXP_FULL_DATABASE
IMP_FULL_DATABASE

• In order to know all your database roles query from DBA_ROLES.

In order to know which roles are granted to each user or role in the database query from dba_role_privs view. Like,
SQL> select grantee, granted_role from dba_role_privs order by grantee;

In order to know system privileges granted to users and roles query from DBA_SYS_PRIVS.
So to know about which privileges the role CONNECT and RESOURCE contains just query,

SQL> select * from dba_sys_privs where grantee='CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

SQL> select * from dba_sys_privs where grantee='RESOURCE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

8 rows selected.

Related Documents:
Privilege required to create a view