Export and Import Tables Whose Names are Starting From MMM using Oracle Data-Pump

Hi Guys,

In Oracle we can easily Export and Import tables with your desired Names.
You can see in below example I exported tables whose names are starting from MMM


E:\expbab> expdp directory=expbab dumpfile=MMM include=table:\"in\(select table_name from all_tables where table_name like \'MMM%\')\"


Export: Release 11.2.0.3.0 - Production on Wed Nov 30 14:33:56 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: HMIS
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HMIS"."SYS_EXPORT_SCHEMA_01":  HMIS/******** directory=expbab dumpfile=MMM include=table:"in\(select table_name from all_tables where table_name like \'MMM%\')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 185.1 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HMIS"."MMMEDREPORT"                        39.59 MB    4976 rows
. . exported "HMIS"."MMMEDFILLOC"                        525.2 KB    5566 rows
. . exported "HMIS"."MMMDISSUM"                          18.32 MB    2644 rows
. . exported "HMIS"."MMMEDORD"                           13.41 MB   44904 rows
. . exported "HMIS"."MMMEDLOC"                           227.2 KB    2684 rows
. . exported "HMIS"."MMMEDREPORT2"                       103.5 KB      11 rows
. . exported "HMIS"."MMMETAFILE"                         40.16 KB       3 rows
. . exported "HMIS"."MMMESSAGE"                          6.335 KB      20 rows
. . exported "HMIS"."MMMLOCCD"                           6.210 KB       1 rows
. . exported "HMIS"."MMM"                                    0 KB       0 rows
. . exported "HMIS"."MMMEDRQST"                              0 KB       0 rows
. . exported "HMIS"."MMMEDSTAT"                              0 KB       0 rows
. . exported "HMIS"."MMMEDTEAM"                              0 KB       0 rows
. . exported "HMIS"."MMMOHCPT"                               0 KB       0 rows
. . exported "HMIS"."MMMRGCAS"                               0 KB       0 rows
Master table "HMIS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HMIS.SYS_EXPORT_SCHEMA_01 is:
  E:\EXPBAB\MMM.DMP
Job "HMIS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:34:19

• Now i will import these tables in SAM schema.

E:\impbab> impdp dumpfile=MMM.DMP directory=impbab remap_schema=hmis:sam

Import: Release 11.2.0.3.0 - Production on Wed Nov 30 14:37:58 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=MMM.DMP directory=impbab remap_schema=hmis:sam
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SAM"."MMMEDREPORT"                         39.59 MB    4976 rows
. . imported "SAM"."MMMEDFILLOC"                         525.2 KB    5566 rows
. . imported "SAM"."MMMDISSUM"                           18.32 MB    2644 rows
. . imported "SAM"."MMMEDORD"                            13.41 MB   44904 rows
. . imported "SAM"."MMMEDLOC"                            227.2 KB    2684 rows
. . imported "SAM"."MMMEDREPORT2"                        103.5 KB      11 rows
. . imported "SAM"."MMMETAFILE"                          40.16 KB       3 rows
. . imported "SAM"."MMMESSAGE"                           6.335 KB      20 rows
. . imported "SAM"."MMMLOCCD"                            6.210 KB       1 rows
. . imported "SAM"."MMM"                                     0 KB       0 rows
. . imported "SAM"."MMMEDRQST"                               0 KB       0 rows
. . imported "SAM"."MMMEDSTAT"                               0 KB       0 rows
. . imported "SAM"."MMMEDTEAM"                               0 KB       0 rows
. . imported "SAM"."MMMOHCPT"                                0 KB       0 rows
. . imported "SAM"."MMMRGCAS"                                0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:38:25



Happy Learning ....

Dropping and Recreating DB-Link in Oracle

Hi Guy's,

Its very simple in oracle to drop and recreate the db-link pls find the steps.

Task :-

• Drop the existing db-link and recreate with the same name.

Steps :-

• First take out the DDL of existing db-link to get the db-link details like name of db-link,remote user,db-port,service name of remote database which you are connecting..
• When i extracted Metadata for db-link i found 4 db-links which are created in my database.
• I will drop 'EYES' db-link and recreate with the same name as per our requirement..

SQL> Set long 1000
SQL> SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;

DBMS_METADATA.GET_DDL('DB_LINK',DB.DB_LINK,DB.OWNER)
--------------------------------------------------------------------------------

  CREATE PUBLIC DATABASE LINK "DMS.COM"
   USING 'DMS'


  CREATE PUBLIC DATABASE LINK "EYES"
   CONNECT TO "FMIS" IDENTIFIED BY VALUES '06C652AFA6A72ADA2EFCFDF0850C4A2B817A8
F1EB7D9D01CC4C88940C63D492915BD2C9F6AECD7367C944154F377700E299604BB91A4E369497B9
6540C66A084BBD808BB7EB68C7956108A189BE969CB0D968F54C729FDA4EDF037204EDFF0F4D72E2
B4EC7F08657089E2CF64AB3FD4B374A63A7FB4C6EF66D53F62D22001C39'
   USING 'ORADB1.iecc.md:1521/HRLIVE'

  CREATE PUBLIC DATABASE LINK "MCC.COM"
   CONNECT TO "HMIS" IDENTIFIED BY VALUES '05CF926A55876BECA1276B20465CE75269BB3
45571ACAF806C'
   USING 'MCCDB'

  CREATE PUBLIC DATABASE LINK "MCCLOCAL.COM"
   CONNECT TO "HMIS" IDENTIFIED BY VALUES '05791082DCF9BBB5C0492C52008E0CFAF1'
   USING 'MCCLOCAL'

                                                                          *********
                                                               

 SQL> DROP PUBLIC DATABASE LINK eyes;

 SQL> CREATE PUBLIC DATABASE LINK EYES CONNECT TO fmis IDENTIFIED BY fmis
USING 'ORADB1.iecc.md:1521/HRLIVE';


Happy Learning...

ORA-00980: synonym translation is no longer valid

This can happen for many reasons. Some of them are:

1. You created a synonym on non-existing object by mistake. 
2. For example, you created a synonym on SCOTT.DEPT where either the SCOTT schema in not present or the DEPT table is missing. 
3. You dropped an object but you did not drop the synonyms referencing the object.
4. You dropped a user, but you did not drop synonyms referencing the objects owned by that user. 
5. When an object is dropped, synonyms referring to the object are not dropped. The following script lists all such invalid synonyms:

• This script generates DDL to drop synonyms whose translation is no longer valid. 

SQL>  rem
           rem  Exludes SYS and SYSTEM users
           rem
           select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',
           'SYNONYM'||s.owner||'.')||s.synonym_name||';'
           from dba_synonyms  s
           where table_owner not in('SYSTEM','SYS')
           and db_link is null
           and not exists (select  1 from dba_objects o
           where s.table_owner=o.owner and s.table_name=o.object_name)
            /


Happy Learning....

How to find all Configured Oracle Services in Windows Environment.

C:\Users> sc query state= all | find "SERVICE_NAME" | find "Oracle"

SERVICE_NAME: OracleJobSchedulerPROD
SERVICE_NAME: OracleJobSchedulerTEST
SERVICE_NAME: OracleMTSRecoveryService
SERVICE_NAME: OracleOraDb11g_home1ClrAgent
SERVICE_NAME: OracleServicePROD
SERVICE_NAME: OracleServiceTEST
SERVICE_NAME: OracleVssWriterPROD
SERVICE_NAME: OracleVssWriterTEST
SERVICE_NAME: OracleOraDb11g_home1TNSListener


C:\Users> sc qc OracleServicePROD

[SC] QueryServiceConfig SUCCESS

        SERVICE_NAME                : OracleServicePROD
        TYPE                                  : 10  WIN32_OWN_PROCESS
        START_TYPE                     : 2   AUTO_START
        ERROR_CONTROL           : 1   NORMAL
        BINARY_PATH_NAME        : e:\app\moin\product\11.2.0\dbhome_1\bin\ORACLE.EXE PROD
        LOAD_ORDER_GROUP    :
        TAG                                    : 0
        DISPLAY_NAME                 : OracleServicePROD
        DEPENDENCIES                :
        SERVICE_START_NAME    : LocalSystem


Happy Learning...

Oracle Database Partitions Types in 10g..

Hi Guys, Today will discuss about Oracle Database Partitions...


Partitioning is an approach or an option which is supplied by oracle  which splits the tables into small chunks.

Main Purpose :-

> To enhance the SQL performance by scanning a small set of rows instead of  complete table.

> To reduce the time in backups.

>  Index Range Scan Become more Inefficient.

> In 11g You Can Transport the Tablespace with the Single Partitions.


When to Partitioned a Table?

1. Oracle recommends When table size is reached up-to 2gb with high I/O is always considered to be partitioned.

2. Tables which are considering historical data where only current data is maintained and the old data will be Archived on Storage.


Some Basic and Mostly used Partitions Types up-to 10g :- 


   Range Partitions : If your Business data is on Range Basis it will support By Ranges Ex:- Dates

 >  Below i have created a Range-partitioned table which distribute the data Range-Wise
    on monthly basis.

>  In this Range-Partition table i gave 3 partitions (p1,p2,p3) which distributes the data on the range of months
    in the same tablespace.

>  This partition has created on the join_date column.

Example of Range Partition :-

SQL> create table emp_part
    (
    empno number(5),
    ename varchar2(5),
    job varchar2(5),
    join_date date
    )
    partition by range (join_date)
    (
   partition p1 values less than (TO_DATE ('01-JAN-2017','DD-MON-YYYY')) tablespace ts4,
   partition p2 values less than (TO_DATE ('01-FEB-2017','DD-MON-YYYY')) tablespace ts4,
   partition p3 values less than (TO_DATE ('01-MAR-2017','DD-MON-YYYY')) tablespace ts4
   );

Table created.

                                                          =====  XXX =====

 List Partitions : The main advantage of List Partition is you can Group and Organize Un-ordered set of  data in a natural way. We can distribute the data in many ways by using List Partitions for example if your Business data is on Region,Designation or Country basis we can easily distributes this type of  data by making List Partitions.

> Below Example i have created Partition-table on LIST basis for job Designations.

SQL> create table emp_part
    (
    empno number(4),
    ename varchar2(10),
    job varchar2(9),
    mgr number(4),
    hiredate date,
    sal number(7,2),
    comm number(7,2),
    deptno number(2)
    )
    partition by list (job)
    (
    partition p1 values ('CLERK'),
    partition p2 values ('SALESMAN'),
    partition p3 values ('MANAGER'),
    partition p4 values ('ANALYST'),
    partition p5 values ('PRESIDENT'));

SQL> SELECT OWNER,TABLE_NAME,PARTITIONING_TYPE FROM ALL_PART_TABLES WHERE OWNER='SAM';

OWNER                  TABLE_NAME       PARTITIONING_TYPE

------------------------------ ------------------------------ ----------------------

SAM                            EMP_PART                             LIST

                                                    ==== XXX ====

  Hash Partitioning : is a technique in which oracle maps data to a partitions Based on a Hashing algorithm.  This hashing algorithm evenly distributes Rows among partitions by giving same size.
       It is used with many distinct values (not identical) where there are no searches on ranges.

Syntax :-

 CREATE TABLE EMP_HASH
(
EMPNO NUMBER(5),
ENAME VARCHAR2(5),
LOCATION VARCHAR2(5))
PARTITION BY HASH (LOCATION)
(
PARTITION PH1 TABLESPACE USERS,
PARTITION PH2 TABLESPACE USERS
);

Table created.



Happy Learning...















   

ORA-14255 Table is not Partitioned by Range,List

> A Table that Contains only One Partition Cannot be Dropped. You must drop the Table.


SQL> CREATE TABLE KKHASH
    (
    EMPNO NUMBER(5),
    ENAME VARCHAR2(5),
    LOCATION VARCHAR2(5))
    PARTITION BY HASH (LOCATION)
    (
    PARTITION H1 TABLESPACE USERS,
    PARTITION H2 TABLESPACE USERS
    );

Table created.


SQL> ALTER TABLE KKHASH DROP PARTITION H1;

         ERROR at line 1:

ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method


SQL> DROP TABLE KKHASH;
       
Table dropped.


Happy Learning...

How to Find Global & Local Indexes in Oracle Partitions.

SQL> CREATE INDEX  EMP_IDX_G ON EMP_TAB (JOIN DATE)
         GLOBAL PARTITION BY RANGE (JOIN DATE)
         (PARTITION P1 VALUES LESS THAN (TO_DATE ('01-JAN-2020','DD-MON-YYYY')) tablespace users,
          PARTITION P2 VALUES LESS THAN (TO_DATE ('01-JAN-2021','DD-MON-YYYY')) tablespace users,
          PARTITION P3 VALUES LESS THAN (TO_DATE ('01-JAN-2022','DD-MON-YYYY')) tablespace users,
          PARTITION P4 VALUES LESS THAN (MAXVALUE) tablespace users
        );

Index created.


SQL> select table_name,index_name,locality from user_part_indexes where table_name='EMP_TAB';

TABLE_NAME                                INDEX_NAME                     LOCALITY
------------------------------ ------------------------------ --------------------------------
EMP_TAB                                          EMP_IDX_G                       GLOBAL



SQL> CREATE INDEX IX_EMP ON EMP_TAB (SDHSCD,SDMEDN,SDCASN) LOCAL;

Index created.


SQL> select table_name,index_name,locality from user_part_indexes where table_name='EMP_TAB';

TABLE_NAME                     INDEX_NAME                     LOCALITY
------------------------------ ------------------------------ -------------------------------
EMP_TAB                                  IX_EMP                               LOCAL




Happy Learning...

How to Calculate Index Size and Table Size in Oracle

Hi Guy's we can calculate all indexes sizes and table size in the below query.
where table name is PHTRNHST and rest of all are the indexes.


SQL> BREAK ON REPORT
SQL> COMPUTE SUM OF SIZE_MB ON REPORT
SQL> SELECT segment_name, SUM (bytes) / (1024 * 1024) size_mb FROM sys.dba_extents WHERE segment_type in('TABLE','INDEX')  AND segment_name in('PHTRNHST', 'X_1','X_TRANSACTION','X_RETURN','X_RET_1','IND_MRM,D_S') and owner='HMIS' group by segment_name;

SEGMENT_NAME                 SIZE_MB
--------------------------------------------------------
X_RETURN                                       80
X_RET_1                                          120
X_1                                                   60
PHTRNHST                                      344
X_TRANSACTION                           128
                                                     ----------
sum                                                   732

Happy Learning...

Exclude Parameter in Oracel Data-Pump

Environment : 11gR2 on Windows 2008 Server



>> Export full=y backup and Skip One Table Using ' Exclude ' Parameter.

D:\ iectest_exp_full > expdp directory=iectest_exp_full dumpfile=abc  EXCLUDE=TABLE:\"IN\(\'SHSHTDTA'\)\" full=y


>> From Full=Y backup Skip two Table..

D:\ iectest_exp_full > expdp directory=iectest_exp_full dumpfile=ahph EXCLUDE=TABLE:\"IN\(\'AHTRNHST','PHTRNHST'\)\" full=y

                                             

 >> Skipping 2 Tables from Schema Level Backup

D:\ iectest_exp_full > expdp directory=iectest_exp_full dumpfile=abcd EXCLUDE=TABLE:\"IN\(\'AHTRNHST','PHTRNHST'\)\" schemas=HMIS



Happy Learning...

ORA- 31687 / 31688 ERROR While performing Schema Level Backup Using Data-Pump

E:\hmis_schema>IMPDP DUMPFILE=HMIS.DMP DIRECTORY=HMIS_SCHEMA REMAP_SCHEMA=HMIS:HMIS

Import: Release 11.2.0.3.0 - Production on Wed Sep 21 12:52:05 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31687: error creating worker process  with worker id 1
ORA-31687: error creating worker process  with worker id 1
ORA-31688: Worker process  failed during startup.


Note:- Data-Pump Uses Streams Pool So Increase the Size of Streams Pool, 
           But I increased   total SGA size.

> Check First What is the size of SGA.

SQL> select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;

TOTAL SGA (GB)
--------------
    1.94439697

> Increase the Size of SGA.

SQL> alter system set sga_max_size=10g scope=spfile;

System altered.

SQL>  alter system set memory_max_target=12g scope=spfile;

System altered.

SQL>  alter system set memory_target=12g scope=spfile;

System altered.


Now You Can Start Using Data-Pump Job.......


Happy Learning..

How to check Table Creation Date in Oracle

SQL> SELECT OWNER,OBJECT_NAME,CREATED FROM DBA_OBJECTS WHERE                                       OBJECT_NAME='PHTRNHST';

OWNER       OBJECT_NAME       CREATED
----------- ----------------- ------------------------------
PUBLIC          PHTRNHST             12-JAN-15



>> How to Check Table Names Which Starts from SHSH.


SQL> select table_name from all_tables where table_name like 'SHSH%';

TABLE_NAME
------------------------------
SHSHDTA_KK
SHSHTDTA
SHSHTDTA_BACKUP
SHSHTDTA_ORIGINAL
SHSHTDTA_PART
SHSHTDTL
SHSHTDTLLAST
SHSHTFLD
SHSHTGRP
SHSHTMRLIST
SHSHTNAM
SHSHTOLE
SHSHTPIC
SHSHTSNT
SHSHTDTL

15 rows selected.

Happy Learning....

How to enable Autoextend on in Oracle

>> Adding data file with Auto-extend on option.

SQL> ALTER TABLESPACE USERS ADD DATAFILE 'J:\ORADATA\KPILIVE\USERS03.DBF' SIZE 10G AUTOEXTEND ON NEXT 1024M;

Tablespace altered.


>> Autoextend the existing datafile to 1024m.

SQL> ALTER DATABASE DATAFILE 'J:\ORADATA\KPILIVE\USERS01.DBF' AUTOEXTEND ON NEXT 1024M;

Database altered.


>> Now query the auto extensible is enabled or not.


SQL> col FileName format a15
SQL> col AUTO format a5
SQL> select tablespace_name,file_name,round(bytes/1024/1024,0) bytesMB, autoextensible AUTO,(increment_by*8192)/1024/1024  Incrmnt,round(maxbytes/1024/1024,2) MaxBytes from dba_data_files;

TABLESPACE                 FILE_NAME                BYTESMB        AUTO  INCRMNT     MAXBYTES
------------------------------------------------------------------------------------------------------- -------------        
USERS            G:\IECT EST2\USERS01.DBF         18560           YES        1024          32767.98
         
SYSAUX         G:\IECTEST2\SYSAUX01.DBF        5120              YES         1024          32767.98
     
SYSTEM         G:\IECTEST2\SYSTEM01.DBF         5120              YES         1024          32767.98
       
UNDONEW     G:\IECTEST2\UNDOTBS009           30720           YES          1024           32767.98
         
4 rows selected.


Happy Reading ....

How to Kill the Session by Finding Logon Time in Oracle RAC

SQL> select username,inst_id,sid,serial# from gv$session where username='RNGLCA';

            USERNAME     INST_ID        SID    SERIAL#
          --------- ---------- ---------- -----------------------------
             RNGLCA                 1             1161      33560


SQL> select s.username,  s.sid,  s.serial#,p.spid, last_call_et,status,to_char(logon_time, 'hh24:mi dd/mm/yy')
    logon_time from GV$SESSION s,GV$PROCESS p where s.PADDR=p.ADDR and s.username='RNGLCA';

   USERNAME    SID      SERIAL#     SPID  LAST_CALL_ET       STATUS         LOGON_TIME
   --------- ---------- ---------- --------- ------------ -------- ---------------------------------------------------
      RNGLCA      1161      33560       14600         14                   INACTIVE        09:45 27/07/16
      RNGLCA      1161      33560       10320         14                   INACTIVE        09:45 27/07/16


SQL> ALTER SYSTEM KILL SESSION '1161,33560,@1';

System altered.


Happy Learning ):

Table Reorganizing by Alter Table Move in Oracle

Alter table move is the another option through which you can reorganize the tables in oracle.

Syntax:-

SQL> ALTER TABLE EMP MOVE;

Table altered


Advantages :-

1. When you reorganize the table oracle reset the HWM which removes the fragmentation of a table and increases the performance of a SQL's.

2. High Water Mark is a Marker where oracle stops writing in the block and put a mark where it ends or We can say its a barrier between used and unused blocks.


Drawbacks:-

1. Table is not available for DML during the move operation.
2. Additional space is needed.
3. Indexes need to be rebuilt manually because Row-Ids are changed.

• You can see in this output how row-ids will change after moving the table.
• All the indexes are created on this row-ids this is the reason indexes will not work. 

>> Before Moving Table <<

SQL> Select rowid from EMP;

ROWID
------------------
AAAVlnAAGAAEcPlAAO
AAAVlnAAGAAEcPlAAP
AAAVlnAAGAAEcPlAAQ
AAAVlnAAGAAEcPlAAR
AAAVlnAAGAAEcPlAAS
AAAVlnAAGAAEcP0AAa


>> After moving Table <<


SQL> Select rowid from EMP;

ROWID
------------------
AAAWITAAGAACzdNAAa
AAAWITAAGAACzdNAAb
AAAWITAAGAACzdNAAc
AAAWITAAGAACzdNAAd
AAAWITAAGAACzdOAAA
AAAWITAAGAACzdOAAB


Check the index status Now

SQL> select status,index_name from dba_indexes where table_name='EMP';


STATUS   INDEX_NAME
-------- ---------------------------------
UNUSABLE      EMP_INDX
UNUSABLE      EMP_INDX_001

>> Indexes Should be rebuild now

SQL> Alter index emp_indx rebuild online;

SQL> Alter index emp_indx_001 rebuild online;


>> Now Gather Fresh Stats of a table to update optimizer.

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
         ownname =>'HMIS',
         tabname =>'EMP',
         degree => 2,
         cascade => TRUE,
         METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
         estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
         END;
         /

Thanks For Reading....

Reorganizing Table using CTAS in Oracle

Steps:-

•   Create a copy of the original table using CTAS.

           SQL> create table new_table as select * from old_table;

•      Drop the original table and all indexes/constraints

           SQL> drop table emp cascade constraints;

•      Rename new table

          SQL>  alter table new_emp rename to old table name;

•      Recreate all indexes because row-ids are change when you move the table.

         
Advantages:-

1. Easy to use.
2. With small maintenance window this CTAS option is very helpful for reorganizing table online.
2. Table is available for DML operations during CTAS (but these DMLs are not copied to target table).


Drawbacks:-

1. CTAS is slow.
2. Additional space is required.
3. Any DML that is performed on the table during CTAS operation is not recorded in target table.
    Differential data should be copied using insert into..select with required filters.
4. Indexes  need to be manually created on target table.


Thanks Reading....

Table Fragmentation in Oracle

What is Fragmentation :-

 When there are heavy transactions such as deletes and updates are running in a table, Fragmentation comes in picture. Oracle will read up to high water mark (HWM) Which is nothing but
 'Point at which Oracle Consumed the Most Extents'
This is the reason  users will face slowness in the application to retrieve the data,because oracle not reset the HWM after deletions.


High Water Mark : Is  a pointer where oracle stops writing in the blocks and put a mark where it ends. OR  we can say its an Marker between Used and Unused Blocks.

 Example :-
Assume that you have 5 lac rows in your table which has accommodated in 1000 blocks, when you are executing SELECT query its taking 20 minutes to give the output, and If you deleted 4 lac rows from this table. and run again same SELECT query it will take same 20 minutes to read again. Here oracle read 1000 blocks again This is because of HWM is not reset by oracle after deletion.


We Can Reset HWM By Using These Methods.

1.  Alter Table Move.

2. Expdp / Impdp

3. Shrink Command.

4. CTAS

5. DBMS Redefinition Package.

                                                               ***************


>> You can check this link how to reset the HWM  and Remove Fragmentation from a table.


http://moindba.blogspot.com/2016/06/how-to-reorganize-table-using.html



 Happy Learning...

How to Drop a Multi-Column Which is Having Constraints in Oracle

SQL> alter table pmpmcstk drop (pshscd,psbtn);

ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint


SQL> alter table pmpmcstk drop (pshscd,psbtn) cascade constraints;

Table altered.


Happy Learning...

How to Reorganize Table using Expdp/Impdp Utility

Hi guys, today i will show how to reorganize fragmented data and reclaim the unused space  using Data-Pump Utility.

 Assume that you received the call from particular Department Users.. For example we will take a "Hospital Pharmacy Department" The pharmacy users are not able to order the medicine from the  pharmacy table.


As a DBA following actions you have to take....


1. First take the table-name of Pharmacy module from the Apps Team.


2. Then check the health of a database like datafile size,table size,Index status etc ...


3. If every thing is fine, then contact your developer, if they did any modifications in the table. Specially Deletions and Updations.


Note:-  Because when rows are deleted or updated in the table Oracle will not Reset the High water mark Which leads to Fragmentation, unnecessary oracle scan all the empty blocks also when query is executed which will degrade the performance.

High Water Mark : Is  a pointer where oracle stops writing in the blocks and put a mark where it ends. OR  we can say its an Barrier between Used and Unused Blocks.

 Example :-
Assume that you have 5 lac rows in your table which has accommodated in 1000 blocks, when you are executing SELECT query its taking 20 minutes to give the output, and If you deleted 4 lac rows from this table. and run again same SELECT query it will take same 20 minutes to read again. Here oracle read 1000 blocks again This is because of HWM is not reset by oracle after deletion.



Reorganizing Steps:-

 So first we have to check how much data got fragmented in that particular Table.

SQL> set verify off
     column owner format a10
     column alcblks heading 'Allocated|Blocks' just c
     column usdblks heading 'Used|Blocks'      just c
     column hgwtr heading 'High|Water'         just c
     break on owner skip page
     select a.owner,a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
     hgwtr from dba_tables a,dba_segments b where a.table_name=b.segment_name
     and a.owner=b.owner and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
     and a.owner like upper('&owner')||'%'and a.table_name like upper('&table_name')||'%'
     order by 1,2;
Enter value for owner: HMIS
Enter value for table_name: PMPMCSTK

 OWNER             TABLE_NAME        Allocated Blocks     Used Blocks     High Water
---------- ---------------------------------------------------------------------------------------------------
HMIS                     PMPMCSTK                    640                        382                  639

Here you can observe the value in High-Water column is 639 but your actual data is in Used Blocks i.e 382 when we reorganize it should be 382 in all these 3 columns.

>> Now we start Re-Organizing the table in production and Import it back on Test database using expdp/impdp

Production Server

D:\exp_table> expdp dumpfile=PMPMCSTK.DMP directory=exp_table tables=HMIS.PMPMCSTK

> Announce the Down time if you are doing on production because you to have drop the table and import it back.

Target Test Server 

C:\imp_table> impdp dumpfile=PMPMCSTK.DMP directory=imp_table tables=HMIS.PMPMCSTK


> Now table has re-organized check whether data is still fragmented....

SQL> set verify off
     column owner format a10
     column alcblks heading 'Allocated|Blocks' just c
     column usdblks heading 'Used|Blocks'      just c
     column hgwtr heading 'High|Water'         just c
     break on owner skip page
     select a.owner,a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
     hgwtr from dba_tables a,dba_segments b where a.table_name=b.segment_name
     and a.owner=b.owner and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
     and a.owner like upper('&owner')||'%'and a.table_name like upper('&table_name')||'%'
     order by 1,2;
Enter value for owner: HMIS
Enter value for table_name: PMPMCSTK

                                         

OWNER         TABLE_NAME        Allocated Blocks     Used Blocks     High Water
---------- ------------------------------ ---------- ----------------------------------------------------
HMIS                PMPMCSTK                 384                           382                383

• You can observe here how High Water Mark has reset and reclaim the space from 639 to 383 and 640 to 384.

• Now gather table level stats to updating Optimizer for creating Fresh Execution plan as per newly reorganized table data..

   SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
             ownname =>'HMIS',
             tabname =>'PMPMCSTK',
             degree => 2,
             cascade => TRUE,
             METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
             estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
             END;
              /


Advantages if we are using Expdp method for Reorganizing:-

1. Faster compared to other re-org options.
2. Extra space is not required if source table is dropped before import.
3. Easier to use when there are too many objects for reorganization.

Drawbacks:-

1. Table availability is affected. So, application downtime should be planned.
2. Any DML performed on the table between export completion and import start time will not be recorded.



Happy Learning...

How to drop data pump job in oracle11g

SQL> select * from dba_datapump_jobs;


OWNER       JOB_NAME                OPERATION     JOB_MODE   STATE               DEGREE ATACHED_SESSIONS DATAPUMP_SESSIONS
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTEM    SYS_IMPORT_FULL_03   IMPORT          FULL          EXECUTING              1                         1                         3
SYSTEM    SYS_IMPORT_FULL_01   IMPORT          FULL          NOT RUNNING          0                          0                         0
SYSTEM    SYS_EXPORT_FULL_01   EXPORT         FULL          NOT RUNNING          0                          0                         0



SQL> DROP TABLE SYSTEM.SYS_IMPORT_FULL_01;

Table dropped.


SQL> DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;

Table dropped.



Happy Learning....

How to find Top 10 Fragmented Tables in Oracle

SQL> select * from (select table_name,round((blocks * 8), 2) "size (kb)",round((num_rows *                        avg_row_len / 1024), 2) "actual_data (kb)", (round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2)) "wasted_space (kb)"  from dba_tables where (round((blocks * 8), 2) > round((num_rows * avg_row_len / 1024), 2)) order by 4 desc)WHERE ROWNUM <= 10;


TABLE_NAME                                                   size (kb)     actual_data (kb)      wasted_space (kb)
------------------------------ ---------- ---------------- ---------------------------------------------------------------
SHSHTDTA                                                        2316384       1871077.06         445306.94
TBL_HIJRI_CONTROLS                                  1905392        1552467.5           352924.5
IDL_UB1$                                                          248272           810.97                247461.03
AHTRNHST                                                       1073152        861498.85           211653.15
OTOCURNC                                                       948296         750972.9             197323.1
AHTRNHST_B4_OPENING_FINAL_BILL          595432         469561.71           125870.29
SHDESCPT                                                         124936            44.83                124891.17
OTORDERS                                                        248776         204020.26            44755.74
INVSRVITM                                                        188416         145291.38              43124.62
HISTGRM$                                                         34584           1128.22                  33455.78

10 rows selected.



Happy Learning....

How to Find Indexes Status in Oracle

> How to find index status of a Particular Table.

SQL> SELECT OWNER,TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='OTORDERS';

               OWNER               TABLE_NAME         INDEX_NAME              STATUS
              ------------------------------ ----------- -------------- --------------------------------
                 HMIS                  OTORDERS           OREPRC_IDX                VALID
                 HMIS                  OTORDERS           ORMEDN_IND               VALID
                 HMIS                  OTORDERS            PK_ORDERS                 VALID


> How to find the index status of Particular Schema.


SQL> SELECT OWNER,TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES WHERE OWNER='CMIS';

OWNER           TABLE_NAME                     INDEX_NAME                    STATUS                      
---------- ------------ ------------------------- --------------------------------------------------              
CMIS            PMDRGMST                      PK_PMDRGMST_1                VALID                      
CMIS           VISOTUBE                         SYS_C0023980                     VALID                      
CMIS           CANISTER                         SYS_C0023939                     VALID                      
CMIS          WEBCAT                              WEBCAT0                             VALID                      
CMIS          VITALVAL                              VITALVAL0                            VALID                      
CMIS          VITALNRM                            VITALNRM0                          VALID      

Happy Learning...

How to Check the Index Size in Oracle

> How to check a particular index size.


SQL> select segment_name,sum(bytes)/1024/1024 as "SIZE in MB" from user_segments where      segment_name='X_TRANSACTION' group by segment_name


SEGMENT_NAME   SIZE in MB
-------------- -----------------------------
X_TRANSACTION         152



> How to check all the indexes of a Particular User.


 SQL> select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='KMIS' and segment_type='INDEX' group by owner;

OWNER                        SIZE in GB
------------------------------------------------
KMIS                            33.751709


> Check all the indexes of a particular table.


 SQL> SELECT DISTINCT TABLE_NAME,INDEX_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='PHTRNHST';

TABLE_NAME           INDEX_NAME
-------------------- --------------------
PHTRNHST             X_RET_1
PHTRNHST             IND_MRM_D_S
PHTRNHST             X_TRANSACTION
PHTRNHST             X_RETURN
PHTRNHST             X_1


Happy Learning...

How to Check the table Size in Oracle

•  To check table name segment type and table size in MB 

SQL> select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where                                             segment_type='TABLE' and segment_name='OTORDERS'

             SEGMENT_NAME         SEGMENT_TYPE                 MB
           -------------------- ------------------ ---------------------------------------
             OTORDERS                          TABLE                           192

• To check Table Owner,Table Name and Table Size.
• You can check here one HMIS owner have multiple tables with different sizes and names.

SQL> select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments                            where owner='HMIS' and segment_type='TABLE' group by owner,segment_name order by                               "SIZE in GB" desc;

OWNER                          SEGMENT_NAME         SIZE in GB              
------------------------------ ---------------------------------------------------------------            
HMIS                                 SHSHTDTA                  1.50683594              
HMIS                                 AHTRNHST                   .984375              
HMIS                                OTOCURNC                   .921875      



Happy Learning ...

How to Extract DDL of any Table in Oracle

SQL> set long 1000
SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL('TABLE','EMP') from DUAL;

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

Happy Learning...

SGA_MAX_SIZE and Memory_Target Parameter in Oracle 10g & 11g

Hi guys, i will explain you the Difference between 10g SGA_MAX_SIZE, SGA_TARGET & 11g feature MEMORY_TARGET, MEMORY_MAX_TARGET


10g Feature :-


SGA_MAX_SIZE : Not Dynamic

SGA_TARGET     : Dynamic


SGA_MAX_SIZE & SGA_TARGET = Oracle introduces this feature in 10g with Automatic Shared Memory Management (ASMM) where oracle manages only sga and living PGA to parameter pga_aggregate_target.


We can say like this SGA_MAX_SIZE parameter value is the maximum size you can increase for your SGA.


Ex:- If SGA_MAX_SIZE=10gb and SGA_TARGET=6GB in future if you want to increase the size of SGA you can increase only upto 10gb.


> SGA_TARGET : When you set SGA_TARGET to any value the Automatic Shared Memory Management (ASMM) is enabled The main advantage of ASMM is no need to worry for a DBA to size the SGA components.

> But still PGA is not managed here in 10g.


Note:-

  If you are using a server parameter file (SPFILE), the database remembers the sizes of the                    automatically tuned SGA components across instance shutdowns.

  As a result, the database instance does not need to learn the characteristics of the workload again     each  time the instance is started.

  The instance can begin with information from the previous instance and continue evaluating                   workload where it left off at the last shutdown.


                                                   ============ xxxxx ===========

11g New Feature :-


   MEMORY_TARGET             : Dynamic

   MEMORY_MAX_TARGET  : Not Dynamic



> Prior to 11g, Oracle manages PGA seperately by the pga_aggregate_target parameter.

> Oracle introduces AMM feature from 11g which will manage SGA as well as PGA.

> When we set MEMORY_TARGET, Oracle will dynamically assign memory to SGA & PGA            

   Note :-  MEMORY_TARGET= SGA_TARGET + PGA_AGGREGATE_TARGET.



> You can see in this demo my requirement is to enable 11g feature Memory_Target and give 10gb size to SGA.

> I have created database with DBCA and selected SGA size by enabling the feature."Use Automatic Memory Management"

> Oracle created this database with 38gb SGA size, this size oracle calculated 40% from existing RAM which is available in my server.



SQL> select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;

TOTAL SGA (GB)
--------------
    38.3281136

> You can see here memory_target and sga_max_size has enabled.


SQL> show parameter memory

NAME                                                   TYPE                      VALUE
------------------------------------ ----------- -----------------------------------
hi_shared_memory_address                integer                        0
memory_max_target                         big integer                 39424M
memory_target                                  big integer                 39424M
shared_memory_address                     integer                         0


SQL> show parameter sga

NAME                                 TYPE                        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                              boolean                    FALSE
pre_page_sga                     boolean                    FALSE
sga_max_size                     big integer               39424M
sga_target                           big integer                    0



> Now i need to allocate some restricted size for the SGA as per my requirement.

> Like we decided to allocate 10gb to SGA and Max_Target should be 2 gb extra.

> So we need to set these 11g parameters SGA_MAX_SIZE,MEMORY_MAX_SIZE AND MEMORY_TARGET.


Steps:-


SQL> alter system set sga_max_size=10g scope=spfile;

System altered.

SQL> alter system set memory_max_target=12g scope=spfile;

System altered.

SQL> alter system set memory_target=12g scope=spfile;

System altered.


>> To effect this SGA  size you need to bounce the database.


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area     1.0689E+10 bytes
Fixed Size                             2264616 bytes
Variable Size                         4328522200 bytes
Database Buffers                  6341787648 bytes
Redo Buffers                        16900096 bytes
Database mounted.
Database opened.

SQL> show parameter sga

NAME                                         TYPE                    VALUE
------------------------------------ ----------- ------------------------------
lock_sga                                  boolean                  FALSE
pre_page_sga                         boolean                  FALSE
sga_max_size                         big integer               10G
sga_target                               big integer                 0


SQL> show parameter memory

NAME                                                     TYPE                 VALUE
------------------------------------ ----------- ---------------------------------
hi_shared_memory_address                integer                  0
memory_max_target                        big integer               12G
memory_target                                 big integer               12G
shared_memory_address                     integer                 0



>> You can observe here the SGA_max_size is 10gb and Memory_max_target and memory_target is 12g.

>> We can exceed the SGA size upto 12gb.




Happy Learning...

Test and Production Environments in IT

Hi Guy's i will explain you the Environments of the IT companies ....


These sequence will differ in every company but mostly we have Test & Production environment.


DEV :-   A Development environment where you configure, customize, and use source control to                                     build an image of the application to be promoted to another environment.


SIT   :-   System Integration Testing, Its an testing environment where after testing finishes the                                         application and its respective data  are moved to UAT environment.


UAT / TEST  :-  User Acceptance Testing, will be done after finishing testing on SIT environment, Finally                                     after every testing is passed  the app and its respective data will be moved to PROD                                           environment or some companies will keep STAGE environment prior to deploying on                                           production.


STAGE :-   Used for final testing immediately prior to deploying to production. You can also say                                             Mirror of Production.


PROD  :-   This is Production or Live environment after all the testing done it moves in this environment where                    users directly interact with production.




Happy Learning ...

How to check default tablespace in oracle

SQL> SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%TABLESPACE%';


PROPERTY_NAME                              PROPERTY_VALUE                DESCRIPTION
------------------------------ -------------------- ---------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE                TEMP                  Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS               Name of default permanent tablespace



Happy Learning ....

How to Check Temp Tablespace Free Size in Oracle Database

SQL> SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COL TABLESPACE_SIZE FOR 999,999,999,999
COL ALLOCATED_SPACE FOR 999,999,999,999
COL FREE_SPACE FOR 999,999,999,999
SELECT *  FROM   dba_temp_free_space;

Press Return to Continue

TABLESPACE       TABLESPACE_SIZE         ALLOCATED_SPACE             FREE_SPACE
---------- ---------------- ---------------- --------------------------------------------------------------------
TEMP                        39,460,012,032               39,269,171,200                39,457,914,880

Happy  Learning ....

DIM-00014 Cannot Open Windows NT Service Control Manager

Problem : When i am installing Oracle11g on Windows7 i faced this Error.

Solution : Very Simple Run dbca Utility with Administrator Option.


C:\app\moin>dbca

> I executed dbca with Administrator option from Oracle_Home/BIN location its run successfully.

> You can see in this screen-shot its installed 100% without any errors.

Happy Learning ...

How to Check Particular Schema Size in Oracle Database

SQL> select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='KIST' group by owner;

OWNER                          SIZE in GB
-----------------------------------------------------
KIST                               18.2417603

Happy Learning...

How to Uninstall Oracle 11g Client on Windows

Hi guys i will show you how simple it is to Uninstall Oracle Client on Windows.

1.  Go to Start menu, Programs, Oracle_Client11g_Home and Click it will take you to
      Universal Installer Menu as shown below.

2.  When you Click on Universal Installer it will open this welcome screen, Now click on  Deinstall Products.

3.  When you click in Deinstall Products it will show you these  HOMES. now check OraClient11g_home1and click on remove. 

4. When you click on remove it will tell you run the Deinstall command from this given location. 

5.  Now go manually to this location in D:\app\moin\product\11.2.0\client_1\deinstall and run as 

     administrator.

6.  When you run this deinstall utility Oracle Starts the " Deconfig Tool " which will deconfig 

     the Oracle Client just type ' Y ' and Press Enter.

     

7.  When you type 'Y' it will start Deinstall process and shows 'Oracle Universal Installer Cleanup was successful' which means Installer has deleted all the binaries of this client software.

8.  After Cleanup finished sucessfully Installer will delete all the Binaries from this location as shown below.

9. Now go to your Location where you installed your Client_Home and delete this Client_1 folder manually which contain many sub folders inside.

Happy Learning...