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