Monthly Archives: March 2014

Flashback database


[oracle@test]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 18 10:21:38 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 4.0353E+10 bytes Fixed Size 2235176 bytes Variable Size 2.1072E+10 bytes Database Buffers 1.9193E+10 bytes Redo Buffers 85233664 […]

Örneklerle Oracle 11G


Geçen Sene Anar kardeşimle beraber çıkardığımız “Örneklerle Oracle 11G” kitabını Azerbaycanda ücretsiz dağıtıyoruz.Amaç Türkçe kaynakların yaygınlaşması. Kitabı isteyen arkadaşlara imzalı 🙂 olarak, kargo ile gönderebilirim.  (http://emrahuysal.com/yayinlar/)  

How to enable Oracle Database Vault in Binary mode


Step1: Shutdow database SQL> shut imemdiate Step2: Stop dbconsole $ emctl stop dbconsole Step3: Stop Listener $ lsnrctl stop listener Step4: Enable database vault in binary mode cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk dv_on lbac_on ioracle

Oracle DBA Important Tables


v$database v$controlfile dba_data_files v$datafile dba_temp_files v$tempfile v$tempspace_header dba_users dba_db_links v$link dba_objects dba_tables dba_indexees dba_ind_columns dba_tab_columns dba_sequences dba_synonyms dba_source dba_views dba_errors dba_triggers dba_roles dba_role_privs session_roles dba_tablespaces dba_tab_privs role_tab_privs session_privs dba_ts_quotas dba_registry dba_rollback_segs dba_properties

Metalink Note


Database ******** NOTE.1012933.6 : General Information : Alert Logs and Trace Files NOTE.209870.1 : How to Reload the JVM in 9.2.0.X NOTE.175472.1 : How to Reload the JVM in 8.1.7.X NOTE.159143.1 : Separating Multiple 8i or 9i Oracle Versions to Avoid NOTE.307349.1 : OUI tips for Oracle RDBMS 10.1.X on OpenVMS NOTE.130814.1 : How to […]

 If you create Oracle Database Vault in the first place is worth checking out:


Level1: /var/opt/oracle/oratab should be under $ ORACLE_HOME. If not,Configure option will be passive (Database Configure Assitance)level2: Default profile PASSWORD VERIFY FUNCTION / should be NULL. If not so we will get out in error ORA-29504. Solution MOS Doc ID 1509963.1Level3: If configuration can be reached at 85% then maybe we will get error ORA-29504. If […]

Manually purge scheduler jobs logs


From sqlplus as sys or system run : exec DBMS_SCHEDULER.PURGE_LOG();

Setting db_file_multiblock_read_count


DECLARE l_count PLS_INTEGER; l_time PLS_INTEGER; l_starting_time PLS_INTEGER; l_ending_time PLS_INTEGER; BEGIN dbms_output.put_line(‘dbfmbrc seconds’); FOR l_dbfmbrc IN 1..32 LOOP EXECUTE IMMEDIATE ‘ALTER SESSION SET db_file_multiblock_read_count=’||l_dbfmbrc; l_starting_time := dbms_utility.get_time(); SELECT /*+ full(t) */ count(*) INTO l_count FROM big_table t; l_ending_time := dbms_utility.get_time(); l_time := round((l_ending_time-l_starting_time)/100); dbms_output.put_line(l_dbfmbrc||’ ‘||l_time); END LOOP; END; /

Disks


Disk groups are created using the CREATE DISKGROUP statement. This statement allows you to specify the level of redundancy: 1. NORMAL REDUNDANCY – Two-way mirroring, requiring two failure groups. 2. HIGH REDUNDANCY – Three-way mirroring, requiring three failure groups. 3. EXTERNAL REDUNDANCY – No mirroring for disks that are already protected using hardware mirroring or […]

Find out Application Names (Products) in Oracle Apps?


set lines 200 set pagesize 300 col APPLICATION_NAME for a70 select a.APPLICATION_NAME, b.APPLICATION_SHORT_NAME from apps.fnd_application b, apps.fnd_application_tl a  where a.APPLICATION_ID=b.APPLICATION_ID; 

I’m an Oracle ACE Associate Award Winner now !


I’m pleased to inform you all that I have received an email from Oracle ACE Program Committee that I have been nominated for the prominent Oracle ACE Associate Award.  Moreover, I want to thank to Kamran (DBA), Talip Hakan Öztürk(DBA)and Mr.Dominic (Oracle Product Manager)for submitting my nomination.

ORA-01102: cannot mount database in EXCLUSIVE mode


First check smon using the command ps -eaf | grep smon Then shutdown database… shutdown immediate Then logout of SQL and again grep for smon… Kill that particular process using kill -9 <PID> Then login to SQL and type startup The database should surely startup now.

Email Generation Shell Scripts for Temporary Tablespace Monitor


#!/bin/sh . /d01/oracle/VIS/db/tech_st/11.1.0/oracle.env cat /home/oracle/OCM/tablespace.log sqlplus ‘/ as sysdba’ << EOF set feedback off echo off set linesize 100 set pagesize 200 set colsep | SET TRIMSPOOL ON spool /home/oracle/OCM/tablespace.log SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM […]

Meeting with young ORACLE users in Caucasus University


WARNING: Oracle executable binary mismatch detected.


WARNING: Oracle executable binary mismatch detected. Binary of new process does not match binary which started instance issue alter system set “_disable_image_check” = true to disable these messages Wed Mar 12 14:46:08 2014 WARNING: Oracle executable binary mismatch detected. Binary of new process does not match binary which started instance issue alter system set “_disable_image_check” […]

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report


C:\Users\Administrator>sqlplus / as sysdba; SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 10 18:12:15 2014 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> SQL> create user DB_TEST identified by TEST; create user DB_TEST […]

EXTERNAL TABLE


External table geçici olarak tablo yaratma işlemlerinde kullanılır. SQL > CREATE DIRECTORY emp_dir AS ’/flat_files’ ; SQL > CREATE TABLE oldemp ( empno NUMBER, empname CHAR(20), birthdate DATE) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY emp_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE BADFILE ’bad_emp’ LOGFILE ’log_emp’ FIELDS TERMINATED BY ’,’ (empno CHAR, empname CHAR, birthdate CHAR […]

EXIST OPERATÖRÜ


Subqery tarafını kontrol ederek rowların durumuna bakmaktadır.True yada false olarak subquery den değerler dönmektedir. Aşağıdaki sorgu herhangi bir yöneticisi olan kişileri listelemektedir. SQL > SELECT employee_id, last_name, job_id, department_id FROM employees outer WHERE EXISTS ( SELECT ’X’ FROM employees WHERE manager_id = outer.employee_id); Yukarıdaki sorgunun alternatifi aşağıdaki gibidir. SQL > SELECT employee_id,last_name,job_id,department_id FROM employees WHERE […]

Recreate temp tablespace Oracle 10g


SQL> create temporary tablespace temp1 tempfile ‘/DATA/oradata/OCM/temp1.dbf’ size 100M; Tablespace created. SQL> alter database default temporary tablespace temp1; Database altered. SQL> drop tablespace temp including contents and datafiles; Tablespace dropped. SQL> CREATE SMALLFILE TEMPORARY TABLESPACE TEMP 2 TEMPFILE ‘/DATA/oradata/OCM/temp01.dbf’ SIZE 1000M 3 AUTOEXTEND ON NEXT 150M MAXSIZE 32767M 4 ,’/DATA/oradata/OCM/temp02.dbf’ SIZE 1000M 5 AUTOEXTEND ON […]

How to drop and recreate TEMP Tablespace


1. Create Temporary Tablespace Temp CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M’;  2. Move Default Database temp tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; 3. Make sure No sessions are using your Old Temp tablespace    a.  Find Session Number from V$SORT_USAGE:         SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;     b.  […]