SYS A "Magic" USER

SYS is default users, created with the creation of the database. Although sys have much power as it is granted the DBA role but still an ordinary users. Because SYS owns the data dictionary, it is considered a bit more special . 

Anytime, someone connects as SYSDBA, it turns out it's being SYS. That is, if SYSDBA is granted to HR and hr connects as SYSDBA and select user from dual, it reveals he's actually SYS.  SYS is also special  that it is not possible to create a trigger in the sys schema. Also, a logon trigger is not executed when sys connects to the database.

Never ever create objects in the SYS schema.The objects belonging to SYS cannot be exported.We can’t do below database work with any other  user except SYS (SYSDBA).

1.) Database Full Recovery (FULL, UNTIL CANCEL, UNTIL TIME, UNTIL SCN)

2.) Change Database Character Set 

3.) Create Database

4.) Drop Database

5.) Startup and Shutdown
Note : Sysdba privileges cannot be revoked from "sys" user.

Enjoy          J J J

Identify Your Oracle Database Software Release

To understand the release nomenclature used by Oracle, examine the following example of an Oracle Database server labeled "Release 10.1.0.1.0".

Oracle Database continues to evolve and can require maintenance, Oracle periodically produces new releases. Not all customers initially subscribe to a new release or require specific maintenance for their existing release. As a result, multiple releases of the product exist simultaneously.As many as five numbers may be required to fully identify a release. The significance of these numbers is

Release Number Format

                      

Note:

Starting with release 9.2, maintenance releases of Oracle Database are denoted by a change to the second digit of a release number. In previous releases, the third digit indicated a particular maintenance release.

Major Database Release Number :  The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Number :  The second digit represents a maintenance release level. Some new features may also be included.

Application Server Release Number :  The third digit reflects the release level of the Oracle Application Server (OracleAS) .

Component-Specific Release Number : The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform-Specific Release Number :  The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.

Checking The Current Release Number :  To identify the release of Oracle Database that is currently installed and to see the release levels of other database components we are using, query the data dictionary view product_component_version. A sample query follows.(We can also query the v$version view to see component-level information.) Other product release levels may increment independent of the database server.

SQL> select * from product_component_version;

PRODUCT                                                  VERSION     STATUS

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

NLSRTL                                                     10.2.0.1.0   Production

Oracle Database 10g Enterprise Edition           10.2.0.1.0   Production

PL/SQL                                                     10.2.0.1.0   Production

It is important to convey to Oracle the results of this query when we report problems with the software .

Enjoy          J J J

How to recreate DUAL table in Oracle after deleting it ?

We should never drop a dual table, it will have serious impact on the database functionality .If we have dropped the dual table from sys user and startup the database next time it will be not open. To open the database we have follow the below steps .Therefore it is highly recommended not to delete the dual table .

SQL> conn /as sysdba
SQL> drop table dual;
 Table dropped.

SQL> desc dba_objects

Name                                 Null?                Type

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

OWNER                                                 VARCHAR2(30)

OBJECT_NAME                                        VARCHAR2(128)

SUBOBJECT_NAME                                   VARCHAR2(30)

OBJECT_ID                                             NUMBER

DATA_OBJECT_ID                                    NUMBER

OBJECT_TYPE                                         VARCHAR2(19)

CREATED                                                DATE

LAST_DDL_TIME                                      DATE

TIMESTAMP                                            VARCHAR2(19)

STATUS                                                 VARCHAR2(7)

TEMPORARY                                            VARCHAR2(1)

GENERATED                                            VARCHAR2(1)

SECONDARY                                            VARCHAR2(1)

NAMESPACE                                            NUMBER

EDITION_NAME                                        VARCHAR2(30)

SQL> select sysdate from dual ;
select sysdate from dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms

Check the alertlog file

Errors in file d:\oracle\diag\rdbms\god\god\trace\god_j000_26455.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms
ORA-12012: error on auto execute of job 21
ORA-01775: looping chain of synonyms
ORA-4063  :encountered when generating server alert SMG-3503 

SQL> shut immediate;                               
SQL> startup;

ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 113249576 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

Checking the alert logfile

 Errors in file d:\oracle\diag\rdbms\god\god\trace\god_ora_26538.trc:
ORA-01775: looping chain of synonyms
Error 1775 happened during db open, shutting down database
USER: terminating instance due to error 1775
Instance terminated by USER, pid = 26538

ORA-1092 signalled during: ALTER DATABASE OPEN...

Now we will create pfile  and add the below parameter and startup database.Create a pfile from spfile and add the below line in the pfile .

replication_dependency_tracking= false 

Now start the database using pfile;
 SQL> startup pfile='c:\initgod.ora'; 

Now database is open but it throws the error while creating the dummt table .

SQL>create table "SYS"."DUAL" ("DUMMY"  VARCHAR2(1)) ;
 CREATE TABLE "SYS"."DUAL" ( "DUMMY" VARCHAR2(1))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms

Again Check the Alertlog file

 Errors in file d:\oracle\diag\rdbms\god\god\trace\god_j000_26583.trc:
ORA-12012: error on auto execute of job 21
ORA-01775: looping chain of synonyms

 SQL> shut immediate
 Now open the database using upgrade mode

SQL> startup upgrade pfile='c:\initgod.ora';
 ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 113249576 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.

SQL> create table  "SYS"."DUAL" ("DUMMY"    VARCHAR2(1)) ;
Table created.

SQL> Insert Into Dual Values ('X') ;
 1 row created.

SQL> commit;
 Commit complete.

SQL> Grant Select On Dual To Public;
 Grant succeeded.

SQL> column OBJECT_NAME format a20;
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED from dba_objects where object_name='DUAL';

OWNER         OBJECT_NAME     OBJECT_TYPE       CREATED     

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

SYS                 DUAL               TABLE                02-APR-10

PUBLIC              DUAL               SYNONYM          02-APR-10

Hence, Dual table is recreated.

Enjoy          J J J

What is Dual Table ?

Dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.  The reason to use the DUAL table isn't just portability, but optimization.  The owner of dual is SYS but dual can be accessed by every user.Dual is useful because it always exists, and has a single row, which is handy for select statements with constant expressions.
Example:


SQL> select 1+1  from dual ;
     1+1
----------
       2
SQL> select sysdate from dual;

SYSDATE
---------
3-MAR-11


DUAL was originally a table and the database engine would perform disk IO on the table when selecting from DUAL. This disk IO was usually logical IO (not involving physical disk access) as the disk blocks were usually already cached in memory. This resulted in a large amount of logical IO against the DUAL table.Later versions of the Oracle database have been optimized and the database no longer performs physical or logical IO on the DUAL table even though the DUAL table still actually exists.

If we drop a dual table then it will have serious impact on the database functionality. so we should never drop dual table. TOM KYTE explained about this in more details .Click the below link 

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

Also Check this link for more about dual table :
http://radiofreetooting.blogspot.com/2006/12/why-dual.html

Enjoy          J J J

Displaying Oracle background processes on Windows

We knows how to use the "ps -ef" command in UNIX to see Oracle background processes but we are not able to see the background processes in Windows ? When we view Oracle processes on Windows, all we see is one background process called oracle.exe.?

This is because in Windows, the "thread" model is used, and Oracle dispatches his own background tasks within the domain of the single process, oracle.exe. Hence, we cannot see any background processes from the Windows OS (but we can see listener process and parallel query slaves).To see details about the background processes in Windows,we need to run a dictionary query against the v$bgprocess view to see what the background processes are doing in Window .

The below sql statement is useful to view the background process in windows .

SQL> select a.sid,a.serial#, a.program, p.pid, p.spid, a.osuser, b.name, b.DESCRIPTION, p.PGA_USED_MEM   from v$session a,v$process p, v$bgprocess b where a.paddr=b.paddr 
      and    a.paddr=p.addr and p.background=1;
Output  : 

Enjoy          J J J