Teymur Hajiyev's Oracle blog

ORACLE Database Firewall

2011-05-05T13:51:00.004+05:00

Oracle Database Firewall provides a Next Generation technology for securing and protecting data in databases. Database Firewall uses a very different approach, which offers greater levels of automation, simplicity and security than traditionally data security software vendors. The core technology interprets the grammar of SQL and works by analyzing the meaning of the statements that database

Oracle 11g: Easy way getting current session's trace file name

2011-02-02T10:10:00.002+04:00

As you know, sometimes we need to trace session, in order to troubleshoot application performance and etc. Tracing session generates server side file with name as: InstanceName_ora_SessionProcessID.trc. To get these values and location of trace file, especially Process_ID, we had to join v$process with v$session. However, in Oracle 11g, now it is possible to get current session's trace file

Oracle 11g new feature: new data dictionary view for listing users with default password - DBA_USERS_WITH_DEFPWD

2010-12-10T16:47:00.001+04:00

As you know, after creation of new database, there will be some DB users whose password should be changed because of security issues. Before 11g, we had to read documentation and check which users are in this list. However, starting from 11g, we can easily find out that user - using DBA_USERS_WITH_DEFPWD SQL> desc DBA_USERS_WITH_DEFPWD Name Null? Type

Oracle 11g new feature: PRIMARY_DB_UNIQUE_NAME column in V$DATABASE

2010-12-03T20:17:00.005+04:00

You can now find the DB_UNIQUE_NAME of the primary database from the standby database by querying the new PRIMARY_DB_UNIQUE_NAME column in the V$DATABASE view. Also, Oracle Data Guard release 11g ensures each database's DB_UNIQUE_NAME is different. It means, after upgrading to 11g, any databases with the same DB_UNIQUE_NAME will not be able to communicate with each other. SQL> desc v$

Oracle 11g new feature in DataPump Export: Overwrite existing dump file

2010-11-25T12:51:00.004+04:00

As you know, until 11g, normally, Data Pump Export will return an error if you specify a dump file name that already exists. Starting with 11g, the REUSE_DUMPFILES parameter allows you to override that behavior and reuse a dump file name. Example: SQL> create table myt1 as select * from all_objects where rownum<11; Table created. SQL> create directory mydir1 as 'c:\oradump'; Directory

Video Tutorial: Oracle 11g Active Standby Database Automatic Block Corruption Repair

2010-11-02T17:01:00.003+04:00

Video tutorial: Oracle 11g and RMAN substitution variables

2010-08-26T17:46:00.000+05:00

Video tutorial: Oracle 11g Streams: Two-Database Replication Environment with Synchronous Captures

2010-08-25T14:37:00.000+05:00

Video tutorial: Oracle 11g Replay Workload using Enterprise Manager

2010-08-19T12:10:00.001+05:00

Video tutorial: Oracle 11g Enterprise Manager and LogMiner Interface

2010-08-16T16:53:00.005+05:00

A new thing I learned today: Truncating a table makes an unusable index valid

2010-07-29T16:57:00.002+05:00

Today I was rereading 11gR2 new Features regarding unusable indexes. I met with following in that document: 11GR2 doc Unusable indexes An unusable index is ignored by the optimizer and is not maintained by DML. One reason to make an index unusable is if you want to improve the performance of bulk loads. (Bulk loads go more quickly if the database does not need to maintain indexes when

ORA-15063: ASM discovered an insufficient number of disks for diskgroup

2010-07-13T14:47:00.003+05:00

After sudden restart of DB Server, interesting issue happened with ASM instance today. OS: Win2003 32 bit Oracle: 10.2.0.1 SQL> startup ASM instance started ... ORA-15032: not all alterations performed ORA-15063: ASM discovered an insufficient number of disks for diskgroup "RECO" ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA" Error description from doc: ORA-

ORA-08106: cannot create journal table ... and ORA-00600

2010-06-22T12:03:00.001+05:00

Environment: OS: Win2003 32 bit Oracle: 10.2.0.1 Problem description: My colleague told me that he can not rebuild index online. Index partition datafile was lost. Trying to rebuild index in online mode, Oracle raised error ORA-08106: cannot create journal table XXX.SYS_JOURNAL_123473. Solution: bash-2.05$ oerr ora 08106 08106, 00000, "can not create journal table %s.%s" // *Cause: The

Standby database: ORA-00600: internal error code, arguments: [3020]

2010-06-03T12:32:00.004+05:00

Today I received error in Standby database after adding new datafile into UNDO tablespace at primary site. This is Oracle SE 9.2.0.5 on Sun box which is used by Vendor Application: Media Recovery Log /oracle/arch/dbint_0000002811.log.ora Thu Jun 3 08:58:39 2010 Errors in file /oracle/home92/admin/dump/dbint_ora_27204.trc: ORA-00600: internal error code, arguments: [3020], [20971527], [1], [

Real world example what it can cause using hidden parameters without Oracle Support’s direct control

2010-05-17T12:19:00.003+05:00

As we know, there are many undocumented initialization parameters in Oracle Database and sometimes it is called "hidden parameters". These parameters is critical tool for Oracle Support Engineers in case of emergency problems relating customers At the same time, Oracle strongly recommends using these parameters only and only under Oracle Support’s direct control and confirmation. Otherwise,

How to get list of hidden parameters in ORACLE?

2010-05-12T17:44:00.004+05:00

Hidden parameters should be used only and only under Oracle Support control. Otherwise you can be left with your corrupted database. In the near future I will post real world example what it caused using it without Oracle Support's confirmation. However, I was interested in how many and which hidden parameters available in Oracle. Below is script: SELECT a.ksppinm "Parameter", b.ksppstvl "

How to become an Oracle Certified Master (OCM)

2010-05-07T11:54:00.002+05:00

I became an Oracle 10g OCM, everybody else wanted to know how to become one. Let me show you the way to become an Oracle Certified Master (OCM). 1) You have to get OCP (Oracle Certified Professional) 2) Following that, you will need to attend 2 advanced trainings from Oracle University. You should attend RAC and Backup/Recovery. Refresh your knowledge with Oracle backup and recovery principles

Relation between Oracle Direct Path Insert, NOLOGGING, Unusable Indexes

2010-05-07T11:52:00.005+05:00

Sometimes lots of us confused as mistakenly thinking DIRECT PATH INSERT (using APPEND hint) will not generate or generate LESS redo. In reality, it is true, but there are other conditions which must be true: I - Database is in archivelog mode: Case 1 - Table is in logging mode, conventional insert and no index: SQL> set autotrace traceonly statistics SQL> create table myt1 as select * from

DBA_FREE_SPACE - Misunderstanding how Oracle populates it

2010-05-07T11:50:00.002+05:00

One of my colleagues asked why some tablespaces are missing from DBA_FREE_SPACE. You have to be sure that, below cases is not true for your tablespace, if it does not appear in DBA_FREE_SPACE: 1) The tablespace is a temporary tablespace. 2) The tablespace is offline. 3) The tablespace contains no free space. The minimum space required for the tablespace to be reported in the DBA_FREE_SPACE is

Oracle sequence access method in 11g

2010-05-07T11:49:00.001+05:00

Before Oracle 11g we could access sequence's current and next value using below method: select sequence_name.CURRVAL from dual; However, starting with 11g we can access it directly using seqence name. Lets have look at example: In 10G: SQL> create sequence myseq1 start with 1 increment by 1; Sequence created. SQL> declare 2 n1 number; 3 begin 4 n1:=myseq1.nextval; 5 dbms_output.put_line(

Relationship between Table monitoring and STATISTICS_LEVEL parameter in 10g

2010-05-07T11:47:00.001+05:00

Before Oracle 10g , we have to enable DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement.In 10G, the MONITORING and NOMONITORING keywords have been deprecated. If you do specify these keywords, they are ignored. In 10g, table-monitoring feature is controlled by the STATISTICS_LEVEL parameter. When

11g new feature - PL/SQL Function Result Cache

2010-05-06T16:49:00.004+05:00

Starting with Oracle 11g, there is very useful feature related to caching results which returned by PL/SQL Function. If the cache contains the result from a previous call to the function with the same parameter values, the system returns the cached result to the invoker and does not re-execute the function body. If the cache does not contain the result, the system executes the function body and

11G new feature - Invisible Indexes

2010-05-06T16:44:00.001+05:00

Sometimes we need to predict what will cause dropping any index on a table or creating new index on it. That is where 11G Invisible Index makes life easier: SQL> create table myt1 as select * from dba_objects; Table created. SQL> select count(*) from myt1; COUNT(*) ---------- 72046 SQL> set autotrace traceonly explain SQL> select count(*) from myt1 where object_id > 100; Execution Plan --

11G new feature - Advanced Table Compression

2010-05-06T16:41:00.001+05:00