Owner: ORACLE: DBA's day out URL:http://shaharear.blogspot.com/ Join Date: Sun, 18 May 2008 05:20:27 -0500 Rating:0 Site Description: Hi, I am Hasan Shaharear, working on ORACLE 10g as a DBA since 2007. The objective of writing this blog is to share my little learnings & experience with your. Hope you also share yours learnings with me. Site statistics:Click here
Oracle Tips And Tricks Part#1 2008-05-17 23:24:00 Tips 3: Do not use LONG datatype.
LONG datatype is used to store variable length character strings. Oracle
has advised, since 8i, that the LONG datatype no longer be used, it has placed in dtatatype set only for backward compatibility. The limitation of LONG datatype are:
There is a limit of one column of type LONG per table.Tables containing a LONG cannot be partitioned.LONG datatypes
High-water mark (HWM) 2008-05-15 01:04:00 High-water
mark is an indicator or pointer up to which table or index has ever contain data. Suppose we create an empty table , the high-water mark would be at the beginning of the table segment
Unused block or extent occupied by the table segment on initial creation.
After inserting data in the table segment , the HWM moves and point the position up to
Place multiple row in a single column 2008-05-11 04:12:00 Some times we need to Place
data which are in multiple row, into a single
column. suppose we have a table EMP which contains following data
SQL> select JOB,ENAME from emp order by job;
JOB ENAME
--------- ----------
ANALYST SCOTT
ANALYST FORD
CLERK MILLER
CLERK JAMES
CLERK SMITH
CLERK ADAMS
MANAGER BLAKE
MANAGER JONES
MANAGER CLARK
PRESIDENT KING
SALESMAN TURN
External Tables 2008-05-08 01:01:00 External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process Read more:External
, Tables
Online Segment Shrink 2008-05-06 23:42:00 <!-- google_ad_section_start -->
Based on the recommendations from the segment advisor you can recover space from specific objects using one of the variations of the ALTER TABLE ... SHRINK SPACE command:
-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;
-- Recover space, but don't am Read more:Segment
Bind variables 2008-05-05 01:36:00 <!-- google_ad_section_start -->
If you've been developing applications on Oracle for a while, you've no doubt come across the concept of «Bind Variables». Bind variables are one of those Oracle concepts that experts frequently cite as being key to application performance, but it's often not all that easy to pin down exactly what they are and how you need to alter your programmin
Oracle Session Resource Profiler 2008-05-03 23:43:00 Introduction
In short, OraSRP is a profiler. It parses extended SQL trace files and creates report which allows you to learn where your session spent its time and why. OraSRP may generate reports either in html or in text format.
Features
Builds complete and detailed session profile (which includes such pseudo-events like 'PARSE/EXEC/FETCH calls' and 'unaccounted-for time').Display results Read more:Oracle
, Resource
Eliminate FULL TABLE ACCESS 2008-05-01 02:48:00 In SQL quarry tuning one of the major goals is to eliminate FULL TABLE ACCESS. Using index we can achieve the goal. let see how it can be achieve
Suppose we have the following table
SQL> desc emp;
Name Type
---------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL
Oracle Database Cloning using DBCA 2008-04-29 06:18:00 The "Template Management" section of the Database
Configuration Assistant (DBCA) can be used to clone databases. The following method creates a clone of an existing database including both the structure and the data:
Start the Database Configuration Assistant (DBCA).On the "Welcome" screen click the "Next" button.On the "Operations" screen select the " Read more:Oracle
, Cloning
Force logging/nologging mode 2008-04-29 04:00:00 You can create tables and indexes specifying that the database create them with the NOLOGGING option. When you create a table or index as NOLOGGING, the database does not generate redo log records for the operation. Thus, you cannot recover objects created with NOLOGGING, even if you are running in ARCHIVELOG mode. With respect to the NOLOGGING option, you can get three benefits:
1. Space is Read more:Force
, logging
Changing Archive Log Destination 2008-04-28 03:56:00 In this article you will learn how to change the destination for archived redo log files. Sometime the location where archive redo log is full and you can not access the database.there are two way to this:
1.Temporarily Changing
the Destination
Using SQL*Plus
If you are automatically archiving, you can use the following command to override the destination specified by the LOG_ARCHIVE_DEST. Read more:Archive
Flashback Part #2 2008-04-28 03:16:00 After Flashback Part #1 Continued .........
Flashback Transaction Query
Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view like:
SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
Flashback Part #1 2008-04-28 02:01:00 (New Features and Enhancements in Oracle Database 10g)
Oracle9i introduced the DBMS_FLASHBACK package to allow queries to reference older versions of the database. Oracle 10g has taken this technology a step further making it simpler to use and much more flexible.
Note: Internally Oracle uses SCNs (System Change Number) to track changes so any flashback operation that uses a timestamp must be
Automatic Shared Memory Management 2008-04-27 04:06:00 Automatic Shared MemoryManagement
puts Oracle in control of allocating memory within the SGA. The SGA_TARGET parameter sets the amount of memory available to the SGA. This parameter can be altered dynamically up to a maximum of the SGA_MAX_SIZE parameter value. Provided the STATISTICS_LEVEL is set to TYPICAL or ALL and the SGA_TARGET is set to a value other than "0" Oracle will control
Use Multiple blocksizes in Oracle 2008-04-23 01:58:00 The introduction of Oracle
9i brought an amazing amount of complexity to the Oracle database engine. Oracle introduced many new internal features, including bitmap free lists, redo log based replication, dynamic SGA, and perhaps the most important feature of all, the ability to support multiple block sizes.
<!-- google_ad_section_start -->
Multiple
blocksizes are indeed beneficial
Gather Database Statistics 2008-04-20 01:47:00 Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:
Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.Cost Based Read more:Database
, Statistics
Enabling ARCHIVELOG Mode 2008-04-17 00:33:00 OVERVIEW
Most of the High Availability features of Oracle require you to enable ARCHIVELOG mode for your database.When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system. Archive logs are utilized by RMAN, Data Guard, Flashback and many others.
Changing Character Sets Of Database 2008-04-15 03:45:00 When computer systems process characters, they use numeric codes instead of the graphical representation of the character. For example, when the database stores the letter A, it actually stores a numeric code that is interpreted by software as the letter. These numeric codes are especially important in a global environment because of the potential need to convert data between different character Read more:Database
, Changing
, Character
Manually configure the Oracle 10g EM dbconsole 2008-04-13 03:33:00 Overview
When you choose to create a preconfigure
d database during the Oracle
10g installation, you can select the Oracle Enterprise Manager (OEM) interface that you want to use to manage the database. The following options are available:
Database Grid Control
This option is available only if an Oracle Management Agent is installed on the system. When the Installer detects an Oracle Management
Renaming / Moving Data Files, Control Files, and Online Redo Logs 2008-04-13 03:02:00 Renaming / Moving
Data Files, Control
Files, and Online Redo Logs
Overview
Once data files, online redo log files and control files has been created in the
database, it may be necessary to move them in order to better manage, size or I/O requirements. It also necessary when the file system (drive) has reached 85%-95% used. There are several methods used by DBAs for moving datafiles, online
Avoid Sorting done by 'ORDER BY' clause 2008-04-10 03:56:00 Sorting is mostly expensive, if you have an index, you can use it to avoid sorting ! Is NOT NULL constraint required? The following TEST CASES will answer your question.
TASTCASE 1:
Step1: Create a table EMP as
SQL> conn system/password as sysdba;
Connected.
SQL> CREATE TABLE HASAN.EMP AS SELECT * FROM SCOTT.EMP;
Table created.
SQL> conn hasan/password
Connected.
Step2: Query the EMP Read more:Avoid
, Sorting
Enable Autotrace 2007-06-06 06:06:00 Autotrace is a very useful feature that used to trace the cost of a sql quarry and execution plane oracle used for that quarry.
Any session can run the autotrace by SET AUTOTRACE ON at SQL*PLUS. But you will get the following error if it is not enabled.
sql :> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cann
Restrict performing DDL in a Schema 2007-05-06 05:36:00 To restrict Schema owner/user to perform any kind of DDL, the following TRIGGER can be useful:
CREATE OR REPLACE TRIGGER trigger_name
BEFORE ALTER OR CREATE OR DROP ON SCHEMA
RAISE_APPLICATION_ERROR(-20001,' give your custom message ');
END;
/
To disable/enable the TRIGGER :
ALTER TRIGGER trigger_name { ENABLE | DISABLE }; Read more:Restrict
Brought back deleted datafile 2007-04-28 05:42:00 It is possible to brought back deleted datafile. To see how :
SQL> CREATE TABLESPACE test DATAFILE '/backup2/test_01.dbf'
SIZE 10M;
SQL> ALTER TABLESPACE test ADD DATAFILE '/backup2/test_02.dbf'
SIZE 800M ;
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='TEST';
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------
ORA-01502 2007-04-24 00:03:00 ORA-01502: index "string.string" or partition of such index is in unusable state
Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation
Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition
Managing Indexes Part #3 2008-05-19 04:05:00 After Managing Indexes
Part #2
Creating an Index Associated with a Constraint
Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled. No action is required by you when you issue the CREATE TABLE or ALTER TABLE statement to
Managing Indexes Part #2 2008-05-19 03:45:00 Continued after Managing Indexes
Part #1
Order Index Columns for Performance
The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first. If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and
Managing Indexes Part #1 2008-05-19 01:46:00 Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. An Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but the execution time increase.
Oracle Database provides several indexing schemes that provide complementary
Read more:Indexes
Oracle Tips And Tricks Part#1 2008-05-18 23:24:00 Tips 4 : Join small table first.
Suppose we have three tables A,B,C which can be join each other by any combinations (i,e a join b join c, c join a join b, ... etc).
A has 10 columns and 100 rows, that means 10*100=1000 cells
B has 5 columns and 2000 rows, that means 5*2000=10000 cells
and C has 20 columns had 30 rows, that means 20*30=600 cells
Now When you apply "Left
Read more:Oracle
High-water mark (HWM) 2008-05-15 01:04:00 High-water
mark is an indicator or pointer up to which table or index has ever contain data. Suppose we create an empty table , the high-water mark would be at the beginning of the table segment
Unused block or extent occupied by the table segment on initial creation.
After inserting data in the table segment , the HWM moves and point the position up to
Recommend Site To A Friend
