Oracle in World: Oracle

Showing posts with label Oracle. Show all posts

Wednesday, November 11, 2009

When datafile bytes size greater than maxbytes

From dba_data_files we can check the size of the datafile, the maximum size of the datafile it can grows if AUTOEXTENSIBLE option is set and their status with many other fields.

Describing the dba_data_files yield following result.

SQL> desc dba_data_files;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
FILE_NAME                                          VARCHAR2(513)
FILE_ID                                            NUMBER
TABLESPACE_NAME                                    VARCHAR2(30)
BYTES                                              NUMBER
BLOCKS                                             NUMBER
STATUS                                             VARCHAR2(9)
RELATIVE_FNO                                       NUMBER
AUTOEXTENSIBLE                                     VARCHAR2(3)
MAXBYTES                                           NUMBER
MAXBLOCKS                                          NUMBER
INCREMENT_BY                                       NUMBER
USER_BYTES                                         NUMBER
USER_BLOCKS                                        NUMBER
ONLINE_STATUS                                      VARCHAR2(7)

Where,
Bytes represent the size of the data file in bytes.

Status can be AVAILABLE or INVALID (INVALID means that the file number is not in use, for example, a file in a tablespace was dropped)

Maxbytes represent maximum file size in bytes.

User_Bytes represent the actual size of the datafile that can be usable to store user data. In fact,
USER_BYTES= Actual size of the datafile - Size used to store file related metadata.

Online_status can have following values,
Online status of the file:
- SYSOFF
- SYSTEM
- OFFLINE
- ONLINE
- RECOVER

From the column description we see MAXBYTES should be greater than BYTES.

But let's have a look at the following query.

SQL> select bytes/1024/1024, maxbytes/1024/1024, user_bytes/1024/1024 from dba_data_files where tablespace_name='DATA';

BYTES/1024/1024 MAXBYTES/1024/1024 USER_BYTES/1024/1024
--------------- ------------------ --------------------
400                300             399.9375

We see in the query it shows BYTES size is greater then USER_BYTES which is acceptable. Because 400-399.9375 = 0.0625 Megabytes size is used to store metadata information. But why the MAXBYTES is greater than the size BYTES?

Well, this happens if after creating tablespace and datafile later we resize the datafile to a bigger size than the maxsize.
Following example will make you clear.

1)Create a tablespace whose datafile size is less than maxsize

SQL> create tablespace data datafile 'data01.dbf' size 40M autoextend on maxsize 300M;
Tablespace created.

2)Let's have a query to see bytes and maxbytes size from dba_data_files.

SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where tablespace_name='DATA';
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
40                300

3)Resize the datafile to a bigger size than MAXBYTES.

SQL> alter database datafile 'data01.dbf' resize 400M;

Database altered.

4)Let's have a query to see BYTES and MAXBYTES information.

SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where tablespace_name='DATA';

BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
400                300

Thursday, August 27, 2009

Oracle DBA scripts ordered by Daily, Weekly, Monthly

I have written a list of oracle DBA scripts that is essential for the DBAs. I have divided the scripts into three categories as daily, weekly and monthly basis. The scripts included almost every types of tasks that are needed for a DBA.

Scripts included various types of error checking, database health monitoring, automatic backup, recovery process, automatic performance problems findings.

Scripts are written for automatic database start up and shut down, check for backup has been carried out successfully, clear archive logs, automatic checking invalid objects, repair them, database growth checking, oracle database security settings, identify top sqls and provide necessary recommendations, tablespace growth management, various advisor to recommend necessary database changes, parameter suggestion/modifications etc.

The scripts not only included oracle scripts, there is also included shell scripts where it is necessary, so you can schedule your scripts through crontab.

All these scripts you can collect for $50.

And to collect the scripts please contact to my email address a_arju@yahoo.co.uk

Friday, October 24, 2008

Which Options are installed on your oracle database

There are various ways to know which options are installed on your oracle database. Below is some.
1)Using Oracle Universal Installer:
-Go to oracle database software installer.
-Under install folder run oracle universal installer. On windows it is oui.exe and on unix it is runIstaller.sh
-Select Installed Products.
-In the Inventory expand the selection and you can see list of options installed.

2)From V$OPTION:
From v$option view the column value's value TRUE means the corresponding option is installed/available and FALSE mean corresponding option is not installed/ not available.
SQL> set pages 100
SQL> col value for a5
SQL> set lines 120
SQL> select * from v$option;
PARAMETER VALUE
---------------------------------------------------------------- -----
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
.
.

Which indicates partition can be done or in other word partitioning feature is available but RAC is not installed or in other word RAC yet not available.

3)From DBA_REGISTRY:
In order to know which components are loaded into database and what is their current status issue,
SQL> col comp_name for a70
SQL> select comp_name, status from dba_registry;

COMP_NAME STATUS
---------------------------------------------------------------------- -----------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Workspace Manager VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle Expression Filter VALID
Oracle Data Mining VALID
Oracle Text VALID
Oracle XML Database VALID
Oracle Rules Manager VALID
Oracle interMedia VALID
OLAP Analytic Workspace VALID
Oracle OLAP API VALID
OLAP Catalog VALID
Spatial VALID
Oracle Enterprise Manager VALID

17 rows selected.

Monday, October 13, 2008

Automatic startup and shutdown oracle on linux

Oracle database server provides two scripts to configure automatic database startup and shutdown process.

The scripts are,
$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut

Now let's look at unix level script. When a unix machine boots it runs scripts beginning with Snnname in /etc/rc3.d.

-Here the number nn indicates the order in which these scripts will be run. The name just indicates the function of the script.

In the same way shutdown scripts are named as Knnname which are run from /etc/rc0.d.

If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.

The database script dbstart and dbora will be called from OS script /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.

Note that dbstart and dbshut take each SID, in turn, from the /etc/oratab file and
startup or shutdown the database.

Automate Startup/Shutdown of Oracle Database on Linux
Step 01: Be sure that oratab file is correct and complete.
Check for oratab file either in /etc/oratab or in /var/opt/oracle/oratab.
Database entries in the oratab file have the following format:
$ORACLE_SID:$ORACLE_HOME:[Y|N]
Here Y indicates that the database can be started up and shutdown using dbstart/dbshut script.

If in my database there is two database named arju and arjudup then my oratab file will contain the entry like,
arju:/var/opt/oracle/product/10.2.0/db_1:Y
arjudup:/var/opt/oracle/product/10.2.0/db_1:Y
where /var/opt/oracle/product/10.2.0/db_1 is the $ORACLE_HOME of my database.

Step 02: Create a script to call dbstart and dbshut.
In this example I will create one script that will do both startup and shutdown operation. I will name this script as dbora and will be placed in '/etc/init.d'.

a) Login as root.
b) Change directories to /etc/init.d
c) Create a file called dbora and chmod it to 750.

# touch dbora
# chmod 750 dbora
d)Edit the dbora file and make the contents of it like below.

#!/bin/bash
#
# chkconfig: 35 99 10   
# description: Starts and stops Oracle processes
#
ORA_HOME=/var/opt/oracle/product/10.2.0/db_1
ORA_OWNER=oracle

case "$1" in
'start')

# Start the TNS Listener
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
# Start the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
# Start the Intelligent Agent
if [ -f $ORA_HOME/bin/emctl ];
then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start agent"
elif [ -f $ORA_HOME/bin/agentctl ]; then

su - $ORA_OWNER -c "$ORA_HOME/bin/agentctl start"
else
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl dbsnmp_start"
fi
# Start Management Server
if [ -f $ORA_HOME/bin/emctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
elif [ -f $ORA_HOME/bin/oemctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/oemctl start oms"
fi
# Start HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl]; then
su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl start"
fi
touch /var/lock/subsys/dbora
;;
'stop')
# Stop HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl ]; then

su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl stop"
fi
# Stop the TNS Listener

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
# Stop the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
rm -f /var/lock/subsys/dbora
;;
esac
# End of script dbora

3.As root perform the following to create symbolic links:

# ln -s /etc/init.d/dbora /etc/rc3.d/S99oracle
# ln -s /etc/init.d/dbora /etc/rc0.d/K01oracle

Alternatively you can register the Service using
/sbin/chkconfig --add dbora

This action registers the service to the Linux service mechanism.

4. Test the script to see if it works.

The real test is to reboot unix box and then see whether oracle is started up automatically or not.

However to test the script created in step 2, without rebooting, do the following:

Login as root and then,
# /etc/init.d/dbora start (for startup)
# /etc/init.d/dbora stop (for shutdown)

If you restart start and stop oracle database is successful then you are almost done.

Related Documents
How to Find out or Check Linux Version Information
How to change the hostname in Linux
How to Identify OS or Oracle 64 bit or 32 bit on Unix

Thursday, October 9, 2008

Oracle database consultancy in Bangladesh

Overview
Database is always critical issue and maintain of it is also the most important in an organization. Out of the database oracle is the most popular database in terms it's functionality, performance issue, security etc.

Performance of application greatly depends on performance of database. In order to maintain database you need plan and great experience. I started consultancy in Bangladesh in oracle database field.

Offered Services
Some of the great services that I offer to service are,

•Overall database design.

•Backup and recovery plan.

•Database Replication Implementation.

•Oracle Data Guard Administration.

•Oracle RAC setup.

•Oracle performance issues.

•Emergency solutions to problems in database.

•Various oracle features and their usage.

Consultancy Rate
If you like to hire me then you can follow either any of two packages.

1)BudgetPack
Rate=3000 taka per hour.
Details
•Maximum hours per month/per week will depend on your budget. If your budget is 15,000 taka then I will be limited to 5 hours.

•I recommend a session should span 3 hours. So per session the rate is 9,000 taka.

•You are free to discuss about your budget limit during consultancy phase.

2)Valuepack
Rate=2000 taka per hour.
The company must come to an agreement of assuring a minimum of one session per week with a minimum duration of 3 hours per session, for 3 months.

My Clients

1)Maestro (Pvt.) Ltd. - Software Development company since 2000. Website: http://maestrocomms.com/

2)Eastern Link BD (Pvt.) Ltd. - ISP since 2001. Website:http://smile.com.bd/

3)Drishtee Online (Pvt.) Ltd. - Nationwide Internet and Data Connectivity Service Provider.

4)Euphoria Solution Ltd. - Export/Import & Country wide distribution, BPO since 2004.

5)Skynet Technologies Ltd. - OSS (Operation, System and Support), Group holding.

Contact Details
If you want me as a consultant you can contact me at +8801710282272
Alternatively you can mail me for more details at a_arju@yahoo.co.uk

Monday, August 18, 2008

How to Resize a Datafile

There may be situations when you need to increase or decrease your datafile size. Prior to oracle 7.2 there was no easy way to resize the datafile. Before 7.2 the solutions was to drop and recreate the tablespace with different sized datafiles, or to add more datafiles to a tablespace whenever you need more space in the tablespace.

Before 7.2 the RESIZE command will raise error,
ORA-00923: FROM keyword not found where expected

Before entering into resize datafile let's be familiar with several views related to datafile.
From V$DATAFILE.CREATION_TIME we can see the timestamp of the datafile creation time.
From V$DATAFILE.BYTES we can see the current datafile size in bytes. 0 in this fields indicate the datafile is inaccessible.
From V$DATAFILE.CREATE_BYTES we can the datafile size when it was created.
From V$DBA_DATA_FILES.MAXBYTES we can see the maximum size of the datafile.

Before going into resize I just create one tablespace containing one datafile of size 10M which can be extended up to 100M.

SQL> create tablespace test_tbs datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\test_tbs01.dbf' size 10M autoextend on maxsize 100M;
Tablespace created.

Now have a look at the current size of maximum size of this data file from dba_data_files view.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
10 100

Increase datafile size
To see the current settings of the datafile query from v$datafile view. The BYTES column shows the current size of the datafile, and the CREATE_BYTES column shows what the size was
specified when the file was created.

Current size is 10M we can increase it upto 15M by,

SQL> alter database datafile 6 resize 15M;
Database altered.

SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;

BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
15 100

SQL> select bytes/1024/1024, create_bytes/1024/1024 from v$datafile where file#=6;
BYTES/1024/1024 CREATE_BYTES/1024/1024
--------------- ----------------------
15 10

Here 12 is current size and 10 is creation time size.

Decrease Datafile size
Downsizing a datafile is more complicated than increasing the size of a datafile. Because you cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile. From DBA_FREE_SPACE we can see the free space in the datafile.

To resize our datafile to 2M issue,
SQL> alter database datafile 6 resize 2M;

If you try to resize a datafile to a size smaller than is needed to contain all the database objects in that datafile, the following error is returned,

ORA-03297: file contains blocks of data beyond requested RESIZE value
Or
ORA-03297: file contains used data beyond requested RESIZE value

Tuesday, July 29, 2008

How to determine free memory and used memory in Oracle

The summary information of the SGA is determined by,
SQL> select * from V$SGA;

NAME VALUE
------------------------------------------------------------ ----------
Fixed Size 2073376
Variable Size 1056967904
Database Buffers 1509949440
Redo Buffers 14700544

To know the current settings of the sga dynamic components ( can be set in memory by ALTER SYSTEM)
SQL> col COMPONENT format a30
SQL> select COMPONENT, CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;

COMPONENT CURRENT_SIZE
------------------------------ ------------
shared pool 989855744
large pool 16777216
java pool 16777216
streams pool 33554432
DEFAULT buffer cache 603979776
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 33554432
DEFAULT 4K buffer cache 637534208
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 67108864

COMPONENT CURRENT_SIZE
------------------------------ ------------
DEFAULT 32K buffer cache 167772160
ASM Buffer Cache 0

13 rows selected.

To know the details of free and used memory use,

col total_sga format a20
col used format a20
col free format a20
SQL> select round(sum(bytes)/1024/1024,2)||' MB' total_sga, round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))||' MB' used, round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)||' MB' free from v$sgastat;
TOTAL_SGA USED FREE
-------------------- -------------------- --------------------
2608.11 MB 2382 MB 226.12 MB

Related Documents
Memory Usage of Solaris Operating System

Tuesday, July 22, 2008

Logical Database Limits in Oracle

1)CREATE MATERIALIZED VIEW definition size Limit
Maximum size: 64K Bytes

2)GROUP BY clause size Limit
Maximum length: Must fit within a single database block.

3)Indexes Limit
Maximum per table: Unlimited
Total size of indexed column: 75% of the database block size minus some overhead

4)Columns Limit
Maximum Per table: 1000
Maximum Per index (or clustered index): 32
Maximum Per bitmapped index: 30

5)Constraints
Maximum per column: Unlimited

6)Subqueries
Maximum levels of subqueries in a SQL statement in the FORM clause of top-level query: Unlimited
Maximum levels of subqueries in a SQL statement in the WHERE clause: 255

7)Partitions
Maximum length of linear partitioning key: 4 KB - overhead
Maximum number of columns in partition key: 16 columns
Maximum number of partitions allowed per table or index: 1024K - 1

8)Rows
Maximum number per table: Unlimited

9)Stored Packages
Maximum size: Limits typically range from 2000 to 3000 lines of code.

10)Trigger Cascade Limit
Maximum value: Operating system-dependent, typically 32

11)Users and Roles
Maximum: 2,147,483,638

12)Tables
Maximum per clustered table: 32 tables
Maximum per database: Unlimited

Related Documents
Physical Database Limits in oracle
Datatype Limits in Oracle

Monday, July 7, 2008

Literals and literal Types in Oracle

The word literals in oracle just point to a fixed data value. Suppose in a table dept(id, name) we insert value as (1,'CIT') then both 1 and 'CIT' are termed as literals. Now we we want to insert just CIT data value without single quote into name column then it will generate error.

So numeric values should be entered without quota and character values should be within single quotes.
Based on the data values inserted into a table literals can be classified into four categories.

1)Text Literals
------------------------------
Use the text literal notation to specify values of strings. The format to specify text literal forms any one of the following,
-[N]{'}{character}{'}
-[N][Q]{'}{quote delimeter}{character}{quote delimeter}{'}

Note that {} /braces indicates the mandatory and brackets [] indicate optional.
In the second format of text literals,
•Q or q indicates that the alternative quoting mechanism will be used.
•C or c is any member of the user's character set. You can include quotation marks (") in the text literal made up of c characters. You can also include the quote_delimiter, as long as it is not immediately followed by a single quotation mark.

Using first format method example of valid literals are,
'testing'
N'testing'
'test''s'

Using second format method example of valid literals are,
q'!name LIKE '%ARJU_%%'!'
q'<'So,' I said 'It's going.'>'
q'{SELECT * FROM dept where name = 'ARJU';}'
q'"dept like '['"'

SQL> select q'<'So,' I said 'It's going.'>' from dual;
Q'<'SO,'ISAID'IT'SGOING.'>
--------------------------
'So,' I said 'It's going.'

2)Numeric Literals
------------------------------
•The interger literals form [+|-]{digit} format like +10,9. -100 etc.
•The number and floating point literals hold any one of the following format,
-[+|-]{digit}[.][digit][e][+|-][digit][f|d]
-[+|-][.]{digit}[digit][e][+|-][digit][f|d]
-f or F indicates that the number is a 32-bit binary floating point number (of type BINARY_FLOAT).
-d or D indicates that the number is a 64-bit binary floating point number (of type BINARY_DOUBLE)
The example of valid unmber and floating point literals are +1.23F,.123,1e-9 -1D etc.

3)Datetime Literals
------------------------------------
4)Interval Literals
-----------------------------------------
Both of these are dicussed in
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html

Null value Concept in Oracle

•A column in a row contains Null value in oracle is treated as special one which indicates the column in that row contains no value.

•Null is not same as zero as zero has a value but null represent no value.

•Setting a null value is appropriate when the actual value is not known or when a value would not be meaningful.

•Currently oracle Database treats a character value with a length of zero as null which is exampled in section below. But according to oracle this behavior may change in future.

•If we do any arithmetic operation of a NULL value then it evaluates to null.

•All operators (except concatenation) return null when given a null operand.

•All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument.

•Most aggregate functions ignore nulls. For example in a column I have 11,null,12 values. Now if I do an average of the column it will ignore the null value and will result average with other column as (11+12)/2.

SQL> select avg(sal) from sal;
AVG(SAL)
----------
11.5
•To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL.

•A null cannot be equal or unequal to any value or to another null as it represents lack of data. Though in DECODE function and two null appeared in compound function treated as same.

•If Nulls are used in Conditions the condition that evaluates to UNKNOWN acts almost like FALSE.
Like if value of a is NULL and if we use where a=NULL then it evaluates to unknown and returns no rows(act like false).

The following example will make you clear.

SQL> create table test_null(a number, b varchar2(10));
Table created.

SQL> insert into test_null (a) values(1);
1 row created.

SQL> insert into test_null values(2, null);
1 row created.

SQL> insert into test_null values(3,'');
1 row created.

SQL> select * from test_null;
A B
---------- ----------
1
2
3
So in three ways we inserted null.

SQL> select * from test_null where b=NULL;
no rows selected
Though all b columns hold null values but use of b=NULL return no rows as NULL!=NULL.

Wednesday, July 2, 2008

Schema Object Naming Rules

Saturday, June 28, 2008

Database Objects in Oracle

If we look for database objects in oracle then there comes two types of objects, one is schema objects that are associated with a particular schema or in other word they are owned by a database user. And the other is nonschema Objects that are not reside in a particular schema.

To know the object type in your database you can query,
SQL>select distinct object_type from dba_objects order by 1;

Schema Objects Lists
------------------------------------
APPLY
CAPTURE
CLUSTER
CONSTRAINT
CONSUMER GROUP
CONTEXT
DATABASE LINK
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
INDEXTYPE
JAVA CLASS
JAVA DATA
JAVA RESOURCE
JOB
JOB CLASS
LIBRARY
LOB
LOB PARTITION
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
PROGRAM
QUEUE
RESOURCE PLAN
RULE
RULE SET
SCHEDULE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW
WINDOW
WINDOW GROUP
XML SCHEMA

Non-Schema Object Lists
--------------------------------------
Parameter file (PFILE) and server parameter files (SPFILEs)
Profile
Role
Rollback segment
Tablespace
User

Monday, June 9, 2008

Create Temporary Table in Oracle

To create a table named test with column col1 type varchar2 length 10, col2 type number. col3 type clob we can use CREATE TABLE statement as,

CREATE TABLE TEST(col1 VARCHAR2(10),
col2 NUMBER,
col3 CLOB);

Now if I insert data into the table the data is visible and accessible to all users. In many cases it is needed the data inside a table will be reside temporarily. In that case we can use temporary tables. Temporary tables are useful in applications where a result set is to be buffered. To create temporary table we have to issue CREATE GLOBAL TEMPORARY clause.

Temporary table can be of two types based on ON COMMIT clause settings.

1)ON COMMIT DELETE ROWS specifies temporary table would be transaction specific. Data persist within table up to transaction ending time. If you end the transaction the database truncates the table (delete all rows). Suppose if you issue commit or run ddl then data inside the temporary table will be lost. It is by default option.

2)ON COMMIT PRESERVE ROWS specifies temporary table would be session specific. Data persist within table up to session ending time. If you end the session the database truncates the table (delete all rows). Suppose you type exit in SQL*Plus then data inside the temporary table will be lost.

1)This statement creates a temporary table that is transaction specific:

SQL>CREATE GLOBAL TEMPORARY TABLE test_temp
(col1 number,
col2 number)
ON COMMIT DELETE ROWS;
Table created.

2)Insert row in to the temporary table.
SQL> insert into test_temp values(1,2);
1 row created.

3)Look at the data in the table.
SQL> select * from test_temp;
COL1 COL2
---------- ----------
1 2
4)Issue Commit.
SQL> commit;
Commit complete.

5)Now look at the data in the temporary table. As I created transaction specific temporary table(on commit delete rows) so data is lost after commit.

SQL> select * from test_temp;
no rows selected

Example of Session Specific Temporary Tables.
----------------------------------------------------
1)Create Session Specific Temporary Table test_temp2.

CREATE GLOBAL TEMPORARY TABLE test_temp2
(col1 number,
col2 number)
ON COMMIT PRESERVE ROWS;

2)Insert data into it and look at data both before commit and after commit.
SQL> insert into test_temp2 values(3,4);
1 row created.

SQL> select * from test_temp2;
COL1 COL2
---------- ----------
3 4

SQL> commit;
Commit complete.

SQL> select * from test_temp2;
COL1 COL2
---------- ----------
3 4

3)End the Session.
SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
4)Connect in a new session and look at data again.
-bash-3.00$ sqlplus arju/a
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 10 00:06:27 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

SQL> select * from test_temp2;
no rows selected

Note: Never confuse with the GLOBAL keyword. After seeing GLOBAL keyword you may thing LOCAL keyword may have. But it is not true. To create temporary table we have to specify GLOBAL keyword. No LOCAL keyword exist there.

Related Documents:
---------------------------
Feature and Restriction of Temporary Table

Monday, June 2, 2008

Default Column Values in Oracle.

Sunday, June 1, 2008

How to check which option is not installed to your database

Based on your database version you may be restricted to use several options of your database or you may wish not to install several database options. With this options feature I have a post in ORA-00439. Now in this post I have shown how you can check or see which options is installed or which option is installed on to your system.

SQL>select ' You have installed '||parameter||' option'
from v$option
where value = 'TRUE';

you can check or see which options is not installed or which option is installed on to your system by following query,

select ' The '||parameter||' option is not installed'
from v$option
where value <> 'TRUE';

The Real Application Clusters option is not installed
The Oracle Label Security option is not installed
The Data Mining Scoring Engine option is not installed

Related Documents:
------------------------
ORA-00439

Wednesday, May 28, 2008

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

Tuesday, May 27, 2008

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

Monday, May 26, 2008

What is View in Oracle

A view in oracle is nothing but a stored sql scripts. View itself contain no data. Whenever we query on a view underlying SQL scripts is executed. Suppose I have created a view as CREATE VIEW TEST_VIEW AS SELECT NAME FROM TEST; Now whenever I query on view TEST_VIEW underlying script SELECT NAME FROM TEST is executed.

A view can be queried, and the data it represents can be changed. Data in a view can be updated or deleted, and new data inserted. These operations directly alter the tables on which the view is based and are subject to the integrity constraints and triggers of the base tables.

Workaround Example:
Connect as a dba user and grant only insert privilege to table TEST on arju schema table to india.

SQL> CONN ARJU/A
Connected.

SQL> revoke SELECT ON TEST from india;
Revoke succeeded.

SQL> grant insert ON TEST TO INDIA;
Grant succeeded.

SQL> CONN INDIA/T
Connected.
SQL> CREATE VIEW ARJU_SCHMEA_VIEW AS SELECT * FROM ARJU.TEST;
View created.

Since india has only insert privilege on the base table so he can create view but can't select any row from the base table or view.

SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
SELECT * FROM ARJU_SCHMEA_VIEW
*
ERROR at line 1:
ORA-01031: insufficient privileges
Now grant him select privilege on the base table.

SQL> CONN ARJU/A
Connected.
SQL> grant SELECT ON TEST TO INDIA;
Grant succeeded.

SQL> CONN INDIA/T
Connected.
SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
no rows selected

Now insert row in to the view.
SQL> INSERT INTO ARJU_SCHMEA_VIEW VALUES(1);
1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
A
----------
1

SQL> SELECT * FROM ARJU.TEST;
A
----------
1

So if underlying view is modified base table is affected.

Sunday, May 25, 2008

Global_names and global_name in Oracle.

There may be at first confusing to you between the two terms global_names and GLOBAL_NAME. I will try to make you understand the difference between these two in my topic. However the detail relation between these two inshallah will be discussed in future topic. In my ORA-2085 a related problem is discussed though.

Before starting you have to be clear that global_names is a PARAMETER and GLOBAL_NAME is a VIEW or SYNONYM.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'GLOBAL_NAME%';
OBJECT_NAME OBJECT_TYPE
-------------------- -------------------
GLOBAL_NAME VIEW
GLOBAL_NAME SYNONYM

SQL> DESC GLOBAL_NAME;
Name Null? Type
----------------------------------------- -------- ----------------------------
GLOBAL_NAME VARCHAR2(4000)

SQL> SELECT NAME FROM V$PARAMETER WHERE NAME LIKE 'global_name%';
NAME
--------------------------------------------------------------------------------
global_names

In order to see your database global_names parameter value just issue,
SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE

Or,
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME='global_names';
VALUE
--------------------------------------------------------------------------------
FALSE

GLOBAL_NAME value:
------------------------------------------------------
The default value of GLOBAL_NAME=DB_NAME+DB_DOMAIN
You can change the default value if you wish.
To know your database global_name issue,
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DBASE.REGRESS.RDBMS.DEV.US.ORACLE.COM

In order to rename your global_name issue,

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO DBASE.BANGLADESH.INDIA;
Database altered.

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
DBASE.BANGLADESH.INDIA

global_names Parameter:
-------------------------------------------------------

•global_names is a boolean type parameter. It can have value either true or false. The default value is false.

In order to set global_names parameter to true for the current session issue,

SQL> ALTER SESSION SET global_names=TRUE;
Session altered.

To set global_names parameter to TRUE permanently use,

SQL> ALTER SYSTEM SET global_names=TRUE;(If your database start with spfile)
Session altered.

•Setting GLOBAL_NAMES to TRUE specifies database link is required to have the same name as the database to which it connects.

•If the value of GLOBAL_NAMES is false, then no check is performed.