My Experiments with Oracle & Others- By $riram $anka

All About Issues, Errors,News & Latest Findings etc

  • Moderator at

  • Member at

  • Categories

  • Archives

  • May 2011
    M T W T F S S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  
  • Blogs I Follow

Archive for May, 2011

ORA-00059: maximum number of DB_FILES exceeded

Posted by Sriram Sanka on May 27, 2011


This is all about the Relation B/W  MAXDATAFILES and DB_FILES In Oralce Database.

–This will Give Us db_files value. max no of data files that we can add.

select value from v$parameter where name = ‘db_files’;

This will give the MAXDATAFILES specified at Control file level while creating Database.

select records_total from v$controlfile_record_section where type = ‘DATAFILE’;

Lets Start with a small case. I have Created My database with DB_FILES   as 10.

C:\Windows\System32>sqlplus system/tejajun20

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 27 20:20:19 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
10

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

Let us have a look at the no of data files I have.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
D:\APP1\SRIRAM\ORADATA\ORAFAQ\USERS01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\UNDOTBS01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSAUX01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSTEM01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\EXAMPLE01.DBF</pre>

Let me add some data files to users tables

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User2.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User3.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User4.dbf' size 100m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
         8

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User5.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User6.dbf' size 100m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
        10

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
10

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

As I have already reached the max limit 10,Adding one more file will raise an Error.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;
alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

ORA-00059:

maximum number of DB_FILES exceeded
Cause: The value of the DB_FILES initialization parameter was exceeded.
Action: Increase the value of the DB_FILES parameter and warm start.

Lets Try to increase the value to a small number.

SQL> alter system set db_files=20 scope=spfile;

System altered.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  401743872 bytes
Fixed Size                  1374892 bytes
Variable Size             268436820 bytes
Database Buffers          125829120 bytes
Redo Buffers                6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
20

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;

Tablespace altered.

SQL>

See now it allowing us to add datafiles.what about Exceeding MAXDATAFILES? (i.e more than 100 data files).

Lets do that And verify what `ll happen.


SQL> alter system set db_files=150 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  401743872 bytes
Fixed Size                  1374892 bytes
Variable Size             268436820 bytes
Database Buffers          125829120 bytes
Redo Buffers                6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User8.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User9.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User10.dbf' size 1m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
        14
--- In another window I am adding datafiles upto the limit "100"
SQL> /

  COUNT(*)
----------
        48

SQL> /

  COUNT(*)
----------
        55

SQL> /

  COUNT(*)
----------
        82

SQL> /

  COUNT(*)
----------
        98

SQL> /

  COUNT(*)
----------
        98

SQL> /

  COUNT(*)
----------
        98

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User95.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User96.dbf' size 1m ;

Tablespace altered.

SQL>
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User97.dbf' size 1m ;

Tablespace altered.

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          200

----Observe it Automatically changed..

SQL>

As Per Oracle Documents :

Consider Possible Limitations When Adding Datafiles to a Tablespace:

  1. You can add datafiles to traditional smallfile tablespaces, subject to the following limitations:
  2. Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.
  3. Operating systems impose limits on the number and size of datafiles.
  4. The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.

You cannot exceed the number of datafiles specified by the DB_FILES initialization parameter.

When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However,if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

DB_FILES specifies the maximum number of database files that can be opened for this database.The maximum valid value is the maximum number of files, subject to operating system constraint,that will ever be specified for the database, including files to be added by ADD DATAFILE statements.If you increase the value of DB_FILES, then you must shut down and restart all instances accessing the database before the new value can take effect. If you have a primary and standby database,then they should have the same value for this parameter.

For More Information Please Read:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343

Posted in Oracle Server Administration | 98 Comments »

Spell number In Indian Currency System Using Function (Number into words)

Posted by Sriram Sanka on May 20, 2011


As we all know We can convert number into words Using ‘Jsp’ format string in to_char function.

ind> select to_char(to_date(‘250′,’J’),’JSP’) from dual;

TO_CHAR(TO_DATE(‘
—————–
TWO HUNDRED FIFTY

1 row selected.

ind> select to_char(to_date(‘259′,’J’),’JSP’) from dual;

TO_CHAR(TO_DATE(‘259’,
———————-
TWO HUNDRED FIFTY-NINE

1 row selected.

ind> select to_char(to_date(‘9999′,’J’),’JSP’) from dual;

TO_CHAR(TO_DATE(‘9999′,’J’),’JSP’)
————————————–
NINE THOUSAND NINE HUNDRED NINETY-NINE

1 row selected.

But It has some limitations….See
ind> select to_char(to_date(‘250.25′,’J’),’JSP’) from dual;
select to_char(to_date(‘250.25′,’J’),’JSP’) from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

ind> select to_char(to_date(‘9999999999.99′,’J’),’JSP’) from dual;
select to_char(to_date(‘9999999999.99′,’J’),’JSP’) from dual
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484

We can avoid this By creating one Function .
This is similar to Ask Tom Spell number

ind> select spell_number(‘9999999999.99’) from dual;

SPELL_NUMBER(‘9999999999.99’)
——————————————————————————–
nine billion nine hundred ninety-nine million nine hundred ninety-nine thousand
nine hundred ninety-nine

1 row selected.

ind> select into_rupees(‘9999999999.99’) from dual;

INTO_RUPEES(‘9999999999.99’)
——————————————————————————–
Nine Arab Ninety-Nine Crore Ninety-Nine Lakh Ninety-Nine Thousand Nine Hundred N
inety-Nine and Paise Ninety-Nine.

You can find this into_rupees script at Indian Rupees

Thank you 🙂

Posted in Functions In oracle | 7 Comments »

#1 Recovery from Loss of Non system datafile(In Archive log Mode)

Posted by Sriram Sanka on May 14, 2011


In  Order to Perform/Test this Scenario…..We should delete/Drop One Non system Datafile.

But This not possible to drop/Delete files when Database is Up and  running ……

“The process cannot access the file because it is being used by another process.”

here I `ll  copy/Replace User datafile with an Old datafile.

In shutdown mode I will manually  copy old file in using “cp” command,and try to Open DB.

C:\Windows\System32>sqlplus  sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 12 09:36:36 2011

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from emp_cp;
select count(*) from emp_cp
                     *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS
ORA-01110: data file 4: 'D:\APP1\SRIRAM\ORADATA\SRIHARSHA\USERS01.DBF'

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
         4 ONLINE  ONLINE
UNKNOWN ERROR                                                         996306
12-MAY-11

SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database recover automatic datafile 4;

Database altered.

SQL> alter database datafile 4 online;

Database altered.

SQL> alter database open;

Database altered.

SQL> select count(*) from scott.emp_cp;

  COUNT(*)
----------
       112

SQL> select * from v$recover_file;

no rows selected

SQL>

If archive log not enabled the you may receive 

SQL> alter database datafile 4 offline;
alter database datafile 4 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

🙂

Posted in Nothing But BackUp | 8 Comments »

How to read Oracle Explain Plan

Posted by Sriram Sanka on May 10, 2011


The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer.A statement’s execution plan is the sequence of operations Oracle performs to run the statement.execution plans are read inside-out.if there are two statements at the same level, the first statement is executed first.

You can use “Explain plan statement for this.” which should be explained first.
The basic syntax to get explain plan is explain plan set statement_id= for

For Ex:
ind> explain plan set statement_id=’ORAFAQ’ for
2 select
3 a.empno,
4 a.ename,
5 b.dname
6 from
7 emp_sriram a,
8 dept b
9 where
10 a.deptno=b.deptno
11 /

Explained.
Now the sequential steps of this sql statement is stored in a Table called PLAN_TABLE.

Lets have a look at the table.

ind> select LPad(‘ ‘, 2*(Level-1)) || Level || ‘.’ || Nvl(Position,0)|| ‘ ‘ ||
2 Operation || ‘ ‘ || Options || ‘ ‘ || Object_Name || ‘ ‘ || Object_Type
3 || ‘ ‘ || Decode(id, 0, Statement_Id ||’ Cost = ‘ || Position) || cost
4 || ‘ ‘ || Object_Node “Query Plan”
5 from plan_table
6 start with id = 0 And statement_id=’ORAFAQ’
7 connect by prior id = parent_id
8 and statement_id=’ORAFAQ’
9 /

Query Plan
———————————————————————————————–
1.4 SELECT STATEMENT ORAFAQ Cost = 44
2.1 NESTED LOOPS 4
3.1 TABLE ACCESS FULL EMP_SRIRAM TABLE 3
3.2 TABLE ACCESS BY INDEX ROWID DEPT TABLE 1
4.1 INDEX UNIQUE SCAN PK_DEPT INDEX (UNIQUE) 0

5 rows selected.

You can Also Use DBMS_XPLAN.DISPLAY.
ind> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’ORAFAQ’,’BASIC’));

PLAN_TABLE_OUTPUT
———————————————————————————-
Plan hash value: 2868360194

—————————————————
| Id | Operation | Name |
—————————————————
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | EMP_SRIRAM |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 4 | INDEX UNIQUE SCAN | PK_DEPT |
—————————————————

11 rows selected.

The basic structure of this package is
dbms_xplan.display(
table_name IN VARCHAR2 DEFAULT ‘PLAN_TABLE’,
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’);

Format choices are
BASIC ….. displays minimum information
TYPICAL … displays most relevant information
SERIAL …. like TYPICAL but without parallel information
ALL ……. displays all information

SQL plus autotrace also produce the explain  plan …..

set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics
Setting autotrace allows to display some statistics and/or an query execution plan for DML statements.
set autotrace on:	Shows the execution plan as well as statistics of the statement.
set autotrace on explain:	Displays the execution plan only.
set autotrace on statistics:	Displays the statistics only.
set autotrace traceonly:	Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
set autotrace off:	Disables all autotrace

Now lets have a look at execution plan order ….

ind> select LPAD(‘ ‘,2*(LEVEL-1))||operation “OPERATION”, options “OPTIONS”,
2 DECODE(TO_CHAR(id),’0’,’COST = ‘ || NVL(TO_CHAR(position),’n/a’),
3 object_name) “OBJECTNAME”, id ||’-‘|| NVL(parent_id, 0)||’-‘||
4 NVL(position, 0) “ORDER”, SUBSTR(optimizer,1,6) “OPT”
5 from plan_table
6 start with id = 0
7 and statement_id=’ORAFAQ’
8 connect by prior id = parent_id
9 and statement_id=’ORAFAQ’;

OPERATION OPTIONS OBJECTNAME ORDER OPT
—————————— —————————— —————————— ——-
SELECT STATEMENT COST = 4 0-0-4 ALL_RO
NESTED LOOPS 1-0-1
TABLE ACCESS FULL EMP_SRIRAM 2-1-1 ANALYZ
TABLE ACCESS BY INDEX ROWID DEPT 3-1-2 ANALYZ
INDEX UNIQUE SCAN PK_DEPT 4-3-1 ANALYZ

5 rows selected.

ind> select LPAD(‘ ‘,2*(LEVEL-1))||operation “OPERATION”, options “OPTIONS”,

2 DECODE(TO_CHAR(id),’0’,’COST = ‘ || NVL(TO_CHAR(position),’n/a’),

3 object_name) “OBJECTNAME”, id ||’-‘|| NVL(parent_id, 0)||’-‘||

4 NVL(position, 0) “ORDER”

5 from plan_table

6 start with id = 0

7 and statement_id=’ORAFAQ’

8 connect by prior id = parent_id

9 and statement_id=’ORAFAQ’;

OPERATION OPTIONS OBJECTNAME ORDER

—————————— —————————— —————————— ——-

SELECT STATEMENT COST = 4                      0-0-4

NESTED LOOPS                                                      1-0-1

TABLE ACCESS FULL EMP_SRIRAM             2-1-1

TABLE ACCESS BY INDEX ROWID DEPT      3-1-2

INDEX UNIQUE SCAN PK_DEPT                        4-3-1

5 rows selected.

On  0-0-4  it is in an order like ID and parent id nad its position.

Like wise if we can order them

00
10
21
31
43
here Level 1 has 2 childs(2,3) and  3 has 1 child(4).
So the execution steps sequential order will be 2,4,3,1
i.e
TABLE ACCESS               FULL                           EMP_SRIRAM  
INDEX                    UNIQUE SCAN                    PK_DEPT
TABLE ACCESS               BY INDEX ROWID                 DEPT  
Produce the result
SELECT STATEMENT Indicates that its a "SELECT STATEMENT"

Here First it will read data from emp_sriram then a unique Index scan  on dept (step 4) 
which gives the rowids and based the rowid input it goes to the step 1
(we can say for understanding) and produce the result.

For More Information Please have a look at Oracle documents.

Posted in Oracle Server Administration | 2 Comments »

Change In “Wrap” methods Of oracle Versions

Posted by Sriram Sanka on May 4, 2011


As we all know ,

We can wrap the source code using wrapper utility.In the same way Oracle has wrapped some objects in Oracle Database By default.

But  there is some change in its wrapping methods.

For Example..
In Oracle 10g

CREATE OR REPLACE FUNCTION “SYSMAN”.”DECRYPT” wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
8
9000000
1
4
0
15
2 :e:
1FUNCTION:
1DECRYPT:
1CIPHER_TEXT:
1VARCHAR2:
1RETURN:
1RAW_TEXT:
1RAW:
132767:
1DBMS_CRYPTO:
1SRC:
1HEXTORAW:
1TYP:
1ENCRYPT_3DES:
1+:
1CHAIN_CBC:
1PAD_PKCS5:
1KEY:
1GETEMKEY:
1UTL_I18N:
1RAW_TO_CHAR:
1AL32UTF8:
0

0
0
48
2
0 a0 8d 8f a0 b0 3d b4
:2 a0 2c 6a a3 a0 51 a5 1c
81 b0 :3 a0 6b :3 a0 a5 b e
:3 a0 6b 7e :2 a0 6b b4 2e 7e
:2 a0 6b b4 2e e :2 a0 b4 2e
e a5 b d :3 a0 6b a0 6e
a5 b 65 b7 a4 b1 11 68
4f 1d 17 b5
48
2
0 3 7 23 1f 1e 2b 1b
30 34 38 3c 59 44 48 4b
4c 54 43 60 64 68 40 6c
70 74 78 79 7b 7d 81 85
89 8c 8f 93 97 9a 9b a0
a3 a7 ab ae af b4 b6 ba
be bf c4 c6 c7 c9 cd d1
d5 d9 dc e0 e5 e6 e8 ec
ee f2 f4 100 104 106 107 110
48
2
0 1 a 12 21 :2 12 11 b
12 :2 1 7 10 14 13 :2 10 7
5 11 :2 1d 16 1b 24 :2 1b :2 16
1b :2 27 33 34 :2 40 :2 1b 49 4a
:2 56 :2 1b :2 16 :3 1b 16 :2 11 :2 5 c
:2 15 21 2b :2 c 5 :9 1
48
4
0 :7 1 :2 2 :2 1
:7 3 :4 5 :6 6 :11 7
:5 8 :3 5 :9 a :2 4
:7 1
112
4
:3 0 1 :3 0 2
:a 0 43 1 :7 0
5 :2 0 3 4
:3 0 3 :7 0 5
4 :3 0 5 :3 0
4 :3 0 7 9
0 43 2 a
:2 0 14 15 0
9 7 :3 0 8
:2 0 7 d f
:6 0 12 10 0
41 0 6 :6 0
6 :3 0 9 :3 0
2 :3 0 a :3 0
b :3 0 3 :3 0
b 18 1a 17
1b c :3 0 9
:3 0 d :3 0 1e
1f 0 e :2 0
9 :3 0 f :3 0
22 23 0 d
21 25 :3 0 e
:2 0 9 :3 0 10
:3 0 28 29 0
10 27 2b :3 0
1d 2c 11 :3 0
12 :4 0 2f 30
:3 0 2e 31 13
16 33 13 34
0 3f 5 :3 0
13 :3 0 14 :3 0
37 38 0 6
:3 0 15 :4 0 17
39 3c 3d :2 0
3f 1a 42 :3 0
42 1d 42 41
3f 40 :6 0 43
:2 0 2 a 42
46 :3 0 45 43
47 :8 0
1f
4
:3 0 1 3 1
6 1 e 1
c 1 19 2
20 24 2 26
2a 3 1c 2d
32 2 3a 3b
2 35 3e 1
11
1
4
0
46
0
1
14
1
3
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0
3 1 0
c 1 0
2 0 1
0

where as in Oracle 11.2.0 the same function wrapped as

CREATE OR REPLACE FUNCTION “SYSMAN”.”DECRYPT” wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
1d9 16d
b7I22qBmrA5hngZ8PfHmiTb8O+UwgzJpNdxqfC9dbuqKO2FNZvfno/Ns8QsgqwomCzHLyLco
3aSo0eQcxmPI6Euw98YDgtrXDlO37aycscH+csQoFWNIszAHrssarFvJMNlZYLbOiNZIfJ2O
fPguqzMwjjlnm37HxGvpCYbc5c4137hhVayrRctxbcWTikHjZiJR0zVUFPpYrxC9n6AmTSg8
x4InsbDh78p8DHv99jCTDTUFru7JrX2SIDf9MpNiLmnpj8c3MyOhQN8amzSLH1/JFR4AxMS1
MM1SVgqke3o2ohYAkBJWC4PfU//ZEzvyC0TtT+xJsHs8S+BMQbLiMeKs38MrcRD7gl5SzA==

seems its more difficult than 10g.
for Examples see these lines from 10g code.

1FUNCTION:
1DECRYPT:
1CIPHER_TEXT:
1VARCHAR2:
1RETURN:
1RAW_TEXT:
1RAW:
132767:
1DBMS_CRYPTO:
1SRC:
1HEXTORAW:
1TYP:
1ENCRYPT_3DES:
1+:
1CHAIN_CBC:
1PAD_PKCS5:
1KEY:
1GETEMKEY:
1UTL_I18N:
1RAW_TO_CHAR:
1AL32UTF8:

which can give us some clues to re write it as below

create or replace FUNCTION DECRYPT(CIPHER_TEXT IN VARCHAR2)
RETURN VARCHAR2 AS
RAW_TEXT RAW(32767);
BEGIN
IF CIPHER_TEXT IS NULL THEN
RETURN NULL;
END IF;

RAW_TEXT := SYS.DBMS_CRYPTO.DECRYPT(

SRC=>HEXTORAW(CIPHER_TEXT),
TYP=>SYS.DBMS_CRYPTO.ENCRYPT_3DES+SYS.DBMS_CRYPTO.CHAIN_CBC+SYS.DBMS_CRYPTO.PAD_PKCS5,
KEY=>GETEMKEY());

RETURN SYS.UTL_I18N.RAW_TO_CHAR(RAW_TEXT, ‘AL32UTF8‘);
END;

Similarly some other methods.

Hope you Enjoyed it. 😉

Posted in Functions In oracle | 3 Comments »

All about the DUAL

Posted by Sriram Sanka on May 4, 2011


The Magic table DUAL is owned by User SYS as its an dictionary object.

ind> select object_name,owner,object_type
2 from dba_objects
3 where object_name like ‘%DUAL%’;

OBJECT_NAME OWNER OBJECT_TYPE
———— ———————- ——————-
DUAL SYS TABLE
DUAL PUBLIC SYNONYM

2 rows selected.

‘Dual’ is a special table with 1X1 (1 row and 1 column) owned by sys which can be used to know the result I can say :).

ind> desc dual;
Name Null? Type
—————————————– ——– ————-
DUMMY VARCHAR2(1)

ind> desc x$dual
Name Null? Type
—————————————– ——– ———————-
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
DUMMY VARCHAR2(1)

In specific cases like whenever Database is not fully up(recovery process),at that situation oracle reads the table dual from a fixed table/view

ind> select * from dual;

D

X

1 row selected.

ind> select * from x$dual;

ADDR INDX INST_ID D
——– ———- ———- –
0366CD54 0 1 X

1 row selected.

Never ever try to play with dual/dictionary objects which will ruin you DB.

Posted in Oracle Server Administration | Leave a Comment »

Oracle Fixed(/Dynamic) views

Posted by Sriram Sanka on May 4, 2011


“Oracle maintains a set of virtual tables that record current database activity. These tables are called dynamic performance tables.”

Why the term “Fixed”?

Because They are fixed ..One cannot perform any DML , DDL or any operation other than “SELECT“,thats why these views/Tables are known as Fixed.

ind> select banner from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

5 rows selected.

ind> alter table x$ksmsp add sriram varchar2(30);
alter table x$ksmsp add sriram varchar2(30)
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views.

Oracle maintains some dynamic performance views which are dynamically created based on the “X$” tables.

Those are basically start with ‘V$’ and ‘GV$’ with one extra column “Instance_id”.

ind> desc v$fixed_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 TYPE                                               VARCHAR2(5)
 TABLE_NUM                                          NUMBER

ind> desc v$fixed_view_definition
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME                                          VARCHAR2(30)
 VIEW_DEFINITION                                    VARCHAR2(4000)

We can query these two views …to get more Info on these views

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='V$SESSION'
  3  ;

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
V$SESSION                      select  SADDR , SID , SERIAL# , AUDSID , PADDR , U
                               SER# , USERNAME , COMMAND , OWNERID, TADDR , LOCKW
                               AIT , STATUS , SERVER , SCHEMA# , SCHEMANAME ,OSUS
                               ER , PROCESS , MACHINE , TERMINAL , PROGRAM , TYPE
                                , SQL_ADDRESS , SQL_HASH_VALUE, SQL_ID, SQL_CHILD
                               _NUMBER , PREV_SQL_ADDR , PREV_HASH_VALUE , PREV_S
                               QL_ID, PREV_CHILD_NUMBER , MODULE , MODULE_HASH ,
                               ACTION , ACTION_HASH , CLIENT_INFO , FIXED_TABLE_S
                               EQUENCE , ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAI
                               T_BLOCK# , ROW_WAIT_ROW# , LOGON_TIME , LAST_CALL_
                               ET , PDML_ENABLED , FAILOVER_TYPE , FAILOVER_METHO
                               D , FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STA
                               TUS, PDDL_STATUS, PQ_STATUS, CURRENT_QUEUE_DURATIO
                               N, CLIENT_IDENTIFIER, BLOCKING_SESSION_STATUS, BLO
                               CKING_INSTANCE,BLOCKING_SESSION,SEQ#, EVENT#,EVENT
                               ,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW, P3TEXT,P3,P3RAW,
                               WAIT_CLASS_ID, WAIT_CLASS#,WAIT_CLASS,WAIT_TIME, S
                               ECONDS_IN_WAIT,STATE,SERVICE_NAME, SQL_TRACE, SQL_
                               TRACE_WAITS, SQL_TRACE_BINDS from GV$SESSION where
                                inst_id = USERENV('Instance')

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='GV$SESSION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
GV$SESSION                     select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuuds
                               es,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.k
                               susesow, decode(s.ksusetrn,hextoraw('00'),null,s.k
                               susetrn),decode(s.ksqpswat,hextoraw('00'),null,s.k
                               sqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0
                               ,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACH
                               ED'),2,'SNIPED',3,'SNIPED', 'KILLED'),decode(s.kss
                               patyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'),
                                 s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid,s.ks
                               usemnm,s.ksusetid,s.ksusepnm, decode(bitand(s.ksus
                               eflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?
                               '), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.k
                               susesch, 65535, to_number(null), s.ksusesch),  s.k
                               susepsq, s.ksusepha, s.ksusepsi,  decode(s.ksusepc
                               h, 65535, to_number(null), s.ksusepch),  s.ksuseap
                               p, s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli,
                                s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s
                               .ksuseslt, s.ksuseltm, s.ksusectm,decode(bitand(s.
                               ksusepxopt, 12),0,'NO','YES'),decode(s.ksuseft, 2,
                               'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),de
                               code(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARS
                               E','NONE'),decode(s.ksusefs, 1, 'YES', 'NO'),s.ksu
                               segrp,decode(bitand(s.ksusepxopt,4),4,'ENABLED',de
                               code(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')
                               ),decode(bitand(s.ksusepxopt,2),2,'FORCED',decode(
                               bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),de
                               code(bitand(s.ksusepxopt,32),32,'FORCED',decode(bi
                               tand(s.ksusepxopt,16),16,'DISABLED','ENABLED')),
                               s.ksusecqd, s.ksuseclid, decode(s.ksuseblocker,429
                               4967295,'UNKNOWN',  4294967294, 'UNKNOWN',42949672
                               93,'UNKNOWN',4294967292,'NO HOLDER',  4294967291,'
                               NOT IN WAIT','VALID'),decode(s.ksuseblocker, 42949
                               67295,to_number(null),4294967294,to_number(null),
                               4294967293,to_number(null), 4294967292,to_number(n
                               ull),4294967291,  to_number(null),bitand(s.ksusebl
                               ocker, 2147418112)/65536),decode(s.ksuseblocker, 4
                               294967295,to_number(null),4294967294,to_number(nul
                               l), 4294967293,to_number(null), 4294967292,to_numb
                               er(null),4294967291,  to_number(null),bitand(s.ksu
                               seblocker, 65535)),s.ksuseseq, s.ksuseopc,e.ksledn
                               am, e.ksledp1, s.ksusep1,s.ksusep1r,e.ksledp2, s.k
                               susep2,s.ksusep2r,e.ksledp3,s.ksusep3,s.ksusep3r,e
                               .ksledclassid,  e.ksledclass#, e.ksledclass, decod
                               e(s.ksusetim,0,0,-1,-1,-2,-2, decode(round(s.ksuse
                               tim/10000),0,-1,round(s.ksusetim/10000))), s.ksuse
                               wtm,decode(s.ksusetim, 0, 'WAITING', -2, 'WAITED U
                               NKNOWN TIME',  -1, 'WAITED SHORT TIME',   decode(r
                               ound(s.ksusetim/10000),0,'WAITED SHORT TIME','WAIT
                               ED KNOWN TIME')),s.ksusesvc, decode(bitand(s.ksuse
                               flg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.
                               ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.k
                               suseflg2,128),128,'TRUE','FALSE')from x$ksuse s, x
                               $ksled e where bitand(s.ksspaflg,1)!=0 and bitand(
                               s.ksuseflg,1)!=0 and s.ksuseopc=e.indx

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2  where view_name='V$FIXED_VIEW_DEFINITION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
V$FIXED_VIEW_DEFINITION        select  VIEW_NAME , VIEW_DEFINITION from GV$FIXED_
                               VIEW_DEFINITION where inst_id = USERENV('Instance'
                               )

1 row selected.

ind> select * from V$FIXED_VIEW_DEFINITION
  2   where view_name='GV$FIXED_VIEW_DEFINITION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ --------------------------------------------------
GV$FIXED_VIEW_DEFINITION       select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i,
                                x$kqfvt t where i.indx = t.indx



Conclusion:
The regular Dynamic views('V$') are created  based on 'Gv$' and these 'Gv$' views are based on 'X$' views
These are permanent tables/views.The X$ tables are generated when ever you instance started.
These tables are accessible to the sys User only.
These 'X$' are not even modifiable By the Super User 'SYS' 
Those are "FIXED"
Never ever try to expertise on these view on your production environment 
which will ruin you DB  complete. ;)

Posted in Oracle Server Administration | 16 Comments »

 
Notes On Oracle

by Mehmet Eser

Oracle Diagnostician

Performance troubleshooting as exact science

deveshdba

get sum oracle stuffs

Know-Oracle

Oracle Cloud ERP Blog by Team CLT

Data Warehousing with Oracle

Dani Schnider's Blog

ORASteps

Oracle DBA's Daily Work

DBAspaceblog.com

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

DBAtricksWorld.com

Sharing Knowledge is ultimate key to Gaining knowledge...

Neil Chandler's DB Blog

A resource for Database Professionals

%d bloggers like this: