External Tables - 10g

External Tables - 10g

Until 9i external tables could only be used for read purposes.But 10g allows you to populate external tables.here is how it does:

Step 1: - Create a directory

SQL> create directory my_dir as '/usr/test';
--- Make sure Oracle OS user hasprivilege to write on this directory.

Step 2: - Grant permission to user SCOTT

SQL> grant read,write on directory my_dir to scott;

Step 3: - Create the External Table: -

SQL> CREATE TABLE scott.external_emp_dept

ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY my_dir
LOCATION ('emp_dept.exp'))
reject limit unlimited
AS select e.ename,d.dname from emp e, dept d where e.deptno=d.deptno;

SQL> select * from scott.external_emp_dept;

ENAME DNAME---------- --------------SMITH RESEARCHALLEN SALESWARD SALESJONES RESEARCHMARTIN SALESBLAKE SALESCLARK ACCOUNTINGSCOTT RESEARCHKING ACCOUNTINGTURNER SALESADAMS RESEARCHENAME DNAME---------- --------------JAMES SALESFORD RESEARCHMILLER ACCOUNTING

And you will find a file generated i.e. 'emp_dept.exp' in /usr/test directory.Now you can take this file to the target database and create an external table and associate the file with that table.

Step 4: - to be executed on the source database

SQL> set long 2000
SQL> Select dbms_metadata.get_ddl('TABLE','EXTERNAL_EMP_DEPT') from dual;
The above command will give you the DDL command to create the table at target database.

10g new background processes

10g new background processes

With 10g many new background processes were introduced.This note highlights those.

MMANMemory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.


RVWRProcess responsible for writing flashback logs which stores pre-image of data-blocks.
These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.

CTWR
Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.

MMNL
The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)

MMON
The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.M000
MMON background slave (m000) processes.

RBALRBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.ARBxThese processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.ASMBThe ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.T

ORA-14097 - when using materialized view and partition table

ORA-14097 - when using materialized view and partition table
This one was an interesting issue which came up few days back.

I spent quite sometime before I solved it.

Issue was - A developer came upto me and told that he is getting "ORA-14097 - : column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" while exchanging partitions.This guy is working on a warehouse design in which huge data loads will happen on base tables through out the day and there are mviews based on which it mines and these mviews' data should be moved fact tables at midnight 12.Now we strategize in a way that at midnight when base tables will be truncated for fresh load of next day, we exchange the partitions of these mviews with temporary partitioned tables and use "Insert /*+ append */" to move it to final fact tables. We could not have directly exchanged partitions of fact table as they might not be partitioned by day.Now the above is all background. While investigating the issue, I was perplexed for some time when he showed me the issue. He created a temporary partitioned table using "create table as select .. the mview where 1=2" and while doing exchange partition he was getting ORA-14097.Let me give you a simple test case:SQL> create table test(a number);Table created.SQL> create materialized view log on test with rowid including new values;Materialized view log created.SQL> create materialized view test_mv refresh fast on demand with rowid as select * from test;Materialized view created.SQL> insert into test values(1);1 row created.SQL> commit;Commit complete.SQL> exec dbms_mview.refresh('test_mv','F')PL/SQL procedure successfully completed.Now both table and mview have on erow each.Let's try and exchange partition of this mview with another table.SQL> select partition_name from user_tab_partitions where table_name='TEST_PART';PARTITION_NAME------------------------------SYS_P3446

SQL> alter table test_part exchange partition SYS_P3446 with table test_mv;

alter table test_part exchange partition SYS_P3446 with table test_mv*ERROR at line 1:ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITIONI used CTAS, there is no question abt data type and column order.

SQL> desc test_partName Null? Type----------------------------------------- -------- ----------------------------A NUMBERSQL> desc test_mvName Null? Type----------------------------------------- -------- ----------------------------A NUMBERAfter doing some research, i got through this metalink article: 72332.1According to that : "If a table has a FUNCTIONAL index on it there is an extra hidden column in COL$ which will cause an EXCHANGE to fail. Comparing USER_TAB_COLUMNS will not show this, nor will USER_UNUSED_COL_TABS but COL$ will show the difference.

"SQL> select col#, name2 from sys.col$3 where obj# in4 (select object_id from user_objects where object_name = 'TEST_MV');COL# NAME---------- ------------------------------0 M_ROW$$1 ANow there you go - this M_ROW$$ was creating the problem for me.Old saying - identifying is problem is 80% of tak that entails solving it.Now - i created test_mv using primary key (not using rowid) and the whole exchange process worked fine!For those who donot have PK in their tables can consider having a separate column which can be seeded using a sequence and treat that as PK to combat this issue.

How can you perform Fragmentation in tables?

Table fragmentation –
when?

If a table is only subject to inserts, there will not be any fragmentation.Fragmentation comes with when we update/delete data in table.The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.To understand it more clearly, we need to be clear on how oracle manages space for tables.“High water mark” of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.Now lets see how to identify HWM, unused (never used) space and free space (used but deleted/updated) and then take a call whether the concerned table is candidate for a reorganization or not.

SQL> create table test as select * from dba_tables;

-- Create a tableTable created.

SQL> analyze table test compute statistics;

-- Analyze itTable analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"2 from user_tables where table_name='TEST'; -- The number of blocks used/freeEver Used Never Used Total rows---------- ---------- ----------49 6 1680
SQL> delete from test where owner='SYS';
--- Im deleting almost half the number of rows.764 rows deleted.
SQL> commit;
Commit complete.

SQL> analyze table test compute statistics; -- Analyze it againTable analyzed.SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"2 from user_tables where table_name='TEST'; -- No difference in blocks usageEver Used Never Used Total rows---------- ---------- ----------49 6 916PL/SQL procedure successfully completed.Even though you deleted almost half the rows, the above shows that table HWM is up to 49 blocks, and to perform any FTS, Oracle will go up to 49 blocks to search the data. If your application is so-written that there are many FTS on this table, you may consider, reorganizing this table.Reasons to reorganizationa) Slower response time (from that table)b) High number of chained (actually migrated) rows. c) Table has grown many folds and the old space is not getting reused.Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.How to reorganize? Before Oracle 10g, there were mainly 2 ways to do the reorganization of the tablea) Export, drop, and import.b) Alter table move (to another tablespace, or same tablespace).Oracle 10g provides us a new way of reorganizing the data.Shrink command: This command is only applicable for tables which are tablespace with auto segment space management.Before using this command, you should have row movement enabled.

SQL> alter table test enable row movement;Table altered.There are 2 ways of using this command.1. Break in two parts: In first part rearrange rows and in second part reset the HWM.Part 1: Rearrange (All DML's can happen during this time)

SQL> alter table test shrink space compact;Table altered.Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)

SQL> alter table sa shrink space;Table altered.2. Do it in one go:

SQL> alter table sa shrink space; (Both rearrange and restting HWM happens in one statement)Table altered.Few advantages over the conventional methods1. Unlike "alter table move ..", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.2. Its an online operation, So you dont need downtime to do this reorg.3. It doesnot require any extra space for the process to complete.ConclusionIts a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management

What is the Difference between a schema and a user?

What is the Difference between a schema and a user?

A schema is collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.
A user owns a schema.
A user and a schema have the same name.
The CREATE USER command creates a user. It also automatically creates a schema for that user.
The CREATE SCHEMA command does not create a "schema" as it implies, it just allows you to create multiple tables and views and perform multiple grants in your own schema in a single transaction.
For all intents and purposes you can consider a user to be a schema and a schema to be a user.

What is HOT / COLD backup?

What is HOT / COLD backup?
There is two types of backup we can take for Oracle Database.
1. COLD/OFFLINE/CONSISTENT backup
What is cold backup and why we say "cold" backup?
When database is DOWN, no activity running on database, no one accessing the database. that time taken database backup called "COLD BACKUP". We can also say "OFFLINE" database backup.
In short:
COLD backup equal to OFFINE backup
COLD backup equal to CONSISTENT backup
For COLD/OFFLINE/COSISTENT database backup we must need to SHUTDOWN Oracle Database with the following option.
1. SQL>shutdown normal
2. SQL>shutdown immediate
3. SQL>shutdown transactional;
4. SQL>shutdown abort;
5. CMD>net stop OracleService
For Example of cold backup on any OS platforms.
1. conn with sysdba user.
2. shutdown oracle database
3. copy init.ora, all datafiles, all redologs files, all controlfile to backup location
4. startup oracle database
NOTE: Database doesn't require ARCHIVELOG mode for COLD backup.
2. What is HOT/ONLINE/INCONSISTENT backup?
When database is open, user accessing the database that time we taken backup is called "HOT,ONLINE, inconsistent" backup.
NOTE: Database must require ARCHIVELOG mode for HOT backup.
For HOT backup we have two options
1. RMAN Recovery Manager (Server Managed Backup)
2. User Managed Backup (User Managed backup)

Data Pump 10g

Data Pump -CONN sys/password@db10g AS SYSDBAALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;GRANT CREATE ANY DIRECTORY TO scott; -CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
Table export /import-expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log -impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
Schema Exports/Imports
The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
Database Exports/Imports
The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.logimpdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

Parallel Full Export and Import: Example
$ expdp system/manager full = y
parallel = 4
dumpfile = DATADIR1:full1%U.dat,
DATADIR2:full2%U.dat,
DATADIR3:full3%U.dat,
DATADIR4:full4%U.dat
filesize = 2G
$ impdp system/manager
directory = NET_STORAGE_1
parallel = 4
dumpfile = full1%U.dat,full2%U.dat,
full3%U.dat,full4%U.dat

Limited Schema Export: Example
$ expdp system/manager schemas = hr,oe
directory = USR_DATA
dumpfile = schema_hr_oe.dat
parfile = exp_par.txt
include = function
include = procedure
include = package
include = type
include = view:"like ’PRODUCT%’"
$ impdp system/manager directory = USR_DATA
dumpfile = schema_hr_oe.dat
sqlfile = schema_hr_oe.sql

Network Mode Import: Example
$ impdp system/manager
schemas = hr,sh,payroll
parfile = imp_par.txt
network_link = finance.hq.com
flashback_time = 2003-09-08 09:00
remap_schema = payroll:finance

Oracle Flashback Transaction Query 10g

Oracle Flashback Transaction Query
Through Flashback Transaction query we can obtain transaction information, including SQL code.Flashback Transaction query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERYWe get following information from FLASHBACK_TRANSACTION_QUERY view1. XID 2. STAR_SCN 3. START_TIMESTAMP 4. COMMIT_SCN 5. UNDO_SQLXID is represent transaction ID,
we can get XID from FLASHBACK VERSION QUERYFor example:Scott user by mistake delete one row from TAJ table and now system user want to know exact TRANSACTION (sql code) for deleted row.We can query in FLASHBACK_TRANSACTION_QUERY view and get exact transaction (sql code)---First check what TRASACTION_ID is for deleted row through FLASHBACK VERSION QUERYselect versions_xid from scott.taj versions between timestampto_timestamp ( '2008-05-17 09:18:00','YYYY-MM-DD HH24:MI:SS') andto_timestamp ( '2008-05-17 09:22:00','YYYY-MM-DD HH24:MI:SS');VERSIONS_XID ---------------- 050017007C070000SQL> select logon_user,operation,undo_sql2 from flashback_transaction_query3 where xid = HEXTORAW ('050017007C070000');LOGON_USER OPERATION UNDO_SQL---------- ---------- ----------------------------------------------SCOTT DELETE insert into "SCOTT"."Shahid"("NAME","NO") values ('Shahid','4');So above is DELETE operation performed by SCOTT user and above UNDO_SQL is generated.Suppose we don’t know about exact timing then we can use SCN (system change number) when last commit is performed.We can get SCN number through ORA_ROWSCN pseudo column, ORA_ROWSCN is represents SCN of the most recent change to given row, that is latest COMMIT operation for the row.Suppose with above example if we don’t know exact timing then row deleted in Shahid table. So we can get exact timing from ORA_ROWSCN pseudo column.SQL> select ORA_ROWSCN, taj.* from scott.taj;ORA_ROWSCN NAME NO---------- -------------------- ----------2407151 Shahid 12407151 Shahid 3Latest COMMIT is performed on Shahid table is 2407151.Now change SCN to TIMESTAMP to get exact timing when last commit was performed.
SQL> select scn_to_timestamp('2407151') from dual;SCN_TO_TIMESTAMP('2407151')---------------------------------------------------------------------------17-MAY-08 09.18.20.000000000 AM
Last COMMIT is performed 17-may-08 09:18 AM.
select undo_sqlfrom flashback_transaction_querywhereCOMMIT_TIMESTAMP >= to_timestamp('17-MAY-05 09:18:00','DD-MON-RR HH:MI:SS')and TABLE_NAME = 'Shahid';
UNDO_SQL--------------------------------------------------------------------------------insert into "SCOTT"."Shahid"("NAME","NO") values ('Shahid','4');

Oracle Flashback Query 10g

Oracle Flashback QueryWe can recover lost records which done by DML statement not DDL.Through this feature we can able to perform below task1. Recovering lost data or undoing incorrect, commit changes.2. Comparing current data with the corresponding data at some time in the past3. Checking the state of transactional data at a particular time4. Simplifying application design by removing the need to store some kinds of temporal dataExample:User Scott delete empcopy table by mistake at 7:53 pm and also delete one record from emp table at 7:25 pm and commit.After he found his doing incorrect operation on empcopy or emp table now he want to recover lost records without performing incomplete recovery or export/import method.Through Flashback query we can recover lost transaction, we can query past data with SELECT … AS OF TIMESTAMP or SCN (system change number)SQL> select empno,sal,ename from emp2 AS OF TIMESTAMP to_timestamp('2008-05-14 19:24:00','YYYY-MM-DD HH24:MI:SS')3 where empno = 7934;
EMPNO SAL ENAME---------- ---------- ----------7934 1300 MILLERInsert this record again in EMP tableSQL> insert into emp ( select * from emp AS OF TIMESTAMP to_timestamp('2008-05-14 19:24:00','YYYY-MM-DD HH24:MI:SS') where empno = 7934);1 row created. SQL> commit; Commit complete.Now recover empcopy tableSQL> insert into empcopy ( select * from empcopyAS OF TIMESTAMP to_timestamp('2008-05-14 19:54:00','YYYY-MM-DD HH24:MI:SS') );
13 rows created.
SQL> commit;
Commit complete.

Oracle Flashback Table 10g

Through flashback table we can rewind table in past stage.
Flashback table uses information in the undo tablespace rather than restored backups.
When flashback table operation occurs, rows are deleted and reinserted. The rest of database remain available which the flashback of the table is being performed.
Prerequisites for flashback table1. Must have FLASHBACK ANY TABLE or FLAHSBACK object privileges
2. Must have SELECT, INSERT, UPDATE , DELETE and ALTER privilege on the table
3. Must have SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY system privileges.
Following objects are eligible for flashback table option1. The object must not be included the following categories: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions2. The structure of the table must not have been changed between the current time and the target flash back time.The following DDL operations change the structure of a table: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).
3. Row movement must be enabled on the table, which indicates that rowids will change after the flashback occurs.4. The undo data in the undo tablespace must extend far enough back in time to satisfy the flashback target time or SCN.Example:
18:30:32 SQL> delete scott.emp;13 rows deleted.18:30:38 SQL> commit;Commit complete.
After commit user found his made delete operation on wrong table now he wants to back all deleted records.
The perform a flashback of SCOTT.EMP
1. obtain SCN information about deleted records and commit;SQL> select current_scn from v$database;CURRENT_SCN-----------2456211
2. Ensure that enough undo data exists to rewind the table to the specified target.SQL> SELECT NAME, VALUE/60 MINUTES_RETAINED2 FROM V$PARAMETER3 WHERE NAME = 'undo_retention';NAME MINUTES_RETAINED------------------------------ ----------------undo_retention 603. Ensure that row movement is enabled for all objects that you are rewinding with Flashback Table.SQL> alter table SCOTT.EMP enable row movement;Table altered.
4. Determine whether the table that you intend to flash back has dependencies on other tables. If dependencies exist, then decide whether to flash back these tables as well.SQL> SELECT other.owner, other.table_name2 FROM sys.all_constraints this, sys.all_constraints other3 WHERE this.owner = 'SCOTT'4 AND this.table_name = 'EMP'5 AND this.r_owner = other.owner6 AND this.r_constraint_name = other.constraint_name7 AND this.constraint_type='R';OWNER TABLE_NAME------------------------------ ------------------------------SCOTT DEPT
5. Execute a FLASHBACK TABLE statement for the objects that you want to flash back.SQL> flashback table SCOTT.EMP2 to timestamp to_timestamp ('2008-05-17 18:30:31','YYYY-MM-DD HH24:MI:SS');Flashback complete.
NOTE: User performed delete operation at 18:30:31 so he flashback table with TIMESTAMP.
6. Optionally, query the table to check the data.SQL> select count(*) from scott.emp;COUNT(*)----------13
NOTE: Flashback table operation is complete and lost data is recovered.
Keeping Triggers Enabled During Flashback Table
It is recommended to add ENABLE TRIGGER keyword with FLASHBACK TABLE statement. For enable database trigger which create on flashback table.
For example:
SQL> Flashback table SCOTT.EMP to timestamp to_timestamp (‘2008-05-17 18:30:31’,’YYYY-MM-DD HH24:MI:SS’) ENABLE TRIGGERS;

Oracle Flashback Troubleshooting 10g

1- Oracle flashback troubleshooting
SQL> alter database flashback on;
alter database flashback on ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.Cause: Flashback log file are created only in FLASH

RECOVERY AREA then must be DB_RECOVERY_FILE_DEST parameter for flashback recovery areaSolution:
SQL> alter system set db_recovery_file_dest='location';SQL> alter database flashback on;

2. SQL> alter database flashback on; --alter database flashback on

ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38714:

Instance recovery required.Cluase: Database is not cleaning shutdown, database shutdown must be NORMAL, IMMEDIATE or TRANSACTIONAL;Solution: SQL> alter database open; (becuase currently database is in mount stage)

SQL>SHUTDOWN NORMAL SQL>STARTUP MOUNT SQL>ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;3. SQL> startup ORACLE instance started.[output cut] Database mounted.

ORA-38760: This database instance failed to turn on flashback databaseClause: Flashback file is not accessiable or missing or lost
NOTE: check alertlog file for more info

Solution:
SQL> alter database flashback off;
SQL> alter database flashback on;
SQL> alter database open;