Friday, December 30, 2011

Oracle 11g new features

›
Automatic Memory Tuning - Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all m...

Oracle 11g PLSQL Native Compilation

›
Machine code is sometimes called native code when referring to platform-dependent parts of language features or libraries. Change...
Thursday, December 29, 2011

Oracle Auditing

›
select name,value from v$parameter where name='audit_trail'; ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE; Shutdown startup -- selec...

Database Hardening

›
Following are the general guidelines used for DB hardening: Complete server hardening checklist. Ideally, run on latest supported version (...
Wednesday, December 28, 2011

ORA-01652 unable to extend temp segment by 64 in tablespace USR

›
select srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks, a.sid, a.serial#, a.username, a.osuser, a.status fr...

ORA-1653: unable to extend table by 4096 in tablespace USR

›
[Microsoft][ODBC driver for Oracle][Oracle]ORA-20014: isincode INE683A01023Stock Details could not be Inserted. ~-1653~ORA-01653: unable to ...
Saturday, December 24, 2011

ORA-01114: IO error writing block to file 201 (block # 763489) ORA-27072: I/O error Linux Error: 28: No space left on device

›
Event Insert data into table Error ERROR at line 25: ORA-01114: IO error writing block to file 201 (block # 763489) ORA-27072: File I/O erro...

Create / Clear Temporary tablespace

›
1) CREATE TEMPORARY TABLESPACE temp2 TEMPFILE 'E:\SNSD1011\TEMP02.ORA' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE unlimited EXTENT ...

TNS-12518: TNS:listener could not hand off client connection TNS-12560 TNS-00530 32-bit Windows Error: 2: No such file or directory

›
Event: When more users are making connection, Connection is breaking Error: TNS-12518: TNS:listener could not hand off client connection TN...
Friday, December 23, 2011

Transparent Data Encryption (TDE)

›
seLect * from dict where table_name like '%WALLET%'; seLect * from dict where table_name like '%ENCRYPT%'; CREATE TABLE ENC...
Thursday, December 22, 2011

Find and Delete duplicate check Constraints

›
-------------------------------------------------------------------------------- -----------------------------------------------------------...

Oracle Connection taking long time to establish / tnsping taking too long

›
1) Check listener.log size At 10g, D:\oracle\product\10.2.0\db_1\NETWORK\log At 11g, D:\app\Administrator\diag\tnslsnr\apex11-PC\listener\t...

Running SQL query

›
------------old set heading off select aa from ( select distinct a.sql_id,a.piece,a.sql_text aa,b.first_load_time from v$sqltext a,v$sqlarea...

Microsoft ODBC driver for Oracle on 64 bit Machine

›
oracle(tm) client and networking components were not found. these components are supplied by oracle corporation and are part of the oracle v...

Table Defragmentation / Table Reorganization / Table Rebuilding

›
Tables in Oracle database become fragmented after mass deletion, or after so many delete and/or insert operations. If you are running a 24×7...
Wednesday, December 21, 2011

Change Snapshot Setting

›
select * from dba_hist_wr_control; BEGIN DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( retention => 66240, -- = 46 D...
Tuesday, December 20, 2011

Detect Row Chaining, Migrated Row and Avoid it

›
--------detecting chained row----- This query will show how many chained (and migrated) rows each table has: SELECT owner, table_name, chain...
Saturday, December 17, 2011

Move segments from one Tablespace to another

›
Move Tables of user PROD_USER like this: Tables + indexes of tables EMP,PRODUCTS,CUSTOMERS into tablespace TBS1. All the other tables + inde...
Thursday, December 15, 2011

EMAIL NOTIFICATION changes in init.ora parameters

›
Auditing changes to init.ora parameters (via pfile or spfile) is an important DBA task. Sometimes, users which have “ alter system ” priv...

How to fix - ORA-12514

›
This simple two part procedure will help to diagnose and fix the most common sqlnet and tnsnames configuration problems. 1. Test communicati...