Reorganization of Tables and Indexes in Oracle Database

Hi DBAs,

Reorganization is very useful and important tasks DBAs perform in order to reduce space used by blocks and it also helps in improving the performance of the Oracle Database.It also helps to reduce the fragmentation.

 There are 3 ways to do:

1.Export/Import
2.Alter table Move
3.CTAS method(Create table table_name2 as Select *from table_name1)

 I followed Step 2 of the above ways.

 Below are the Steps I follow for doing reorganization:

Step 1:Check the last analyzed, tables size before Reorganization
Query 1:Check the last Analyzed and number of rows
SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');
Query 2:Check the size of the table before Reorganization
SQL>  SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024 FROM DBA_SEGMENTS
     WHERE SEGMENT_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');

Step 2: Get the list of all the INDEXes associated with the tables used for Reorganization.
SET LINES 2000
SET LINESIZE 2000
SQL> select  OWNER||' '||table_name||' '||INDEX_name from DBA_INDEXES where table_name in ('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');

OWNER||''||TABLE_NAME||''||INDEX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSADM PS_TEST_WL PSATEST_WL
SYSADM PS_TEST_WL PSBTEST_WL
SYSADM PS_TEST_WL PS_TEST_WL
SYSADM PS_TEST_USERINST PSATEST_USERINST
SYSADM PS_TEST_USERINST PSBTEST_USERINST
SYSADM PS_TEST_USERINST PSCTEST_USERINST
SYSADM PS_TEST_USERINST PSDTEST_USERINST
SYSADM PS_TEST_USERINST PSETEST_USERINST
SYSADM PS_TEST_USERINST PS_TEST_USERINST
SYSADM PS_TEST_STEPINST PSBTEST_STEPINST
SYSADM PS_TEST_STEPINST PSCTEST_STEPINST

OWNER||''||TABLE_NAME||''||INDEX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSADM PS_TEST_STEPINST PSDTEST_STEPINST
SYSADM PS_TEST_STEPINST PSETEST_STEPINST
SYSADM PS_TEST_STEPINST PSFTEST_STEPINST
SYSADM PS_TEST_STEPINST PSATEST_STEPINST
SYSADM PS_TEST_STEPINST PS_TEST_STEPINST
SYSADM PSWORKLIST PS_PSWORKLIST
SYSADM PSWORKLIST PS0PSWORKLIST
SYSADM PSWORKLIST PSAPSWORKLIST
SYSADM PSWORKLIST PSBPSWORKLIST
SYSADM PS_TEST_TAUTH_AW PS_TEST_TAUTH_AW
SYSADM PS_TEST_TAUTH_AW SYS_IL0000025105C00012$$

OWNER||''||TABLE_NAME||''||INDEX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSADM PS_TEST_SHEET_AW PSATEST_SHEET_AW
SYSADM PS_TEST_SHEET_AW SYS_IL0000024994C00012$$
SYSADM PS_TEST_SHEET_AW PS_TEST_SHEET_AW
SYSADM PS_TEST_ADV_AW SYS_IL0000024456C00012$$
SYSADM PS_TEST_ADV_AW PS_TEST_ADV_AW

27 rows selected.


Step 3:Move the tables to reduce fragmentation.

SQL> spool move_tables.out
SQL> ALTER TABLE PS_TEST_WL MOVE;

Table altered.

SQL> ALTER TABLE PS_TEST_USERINST MOVE;

Table altered.

SQL> ALTER TABLE PS_TEST_STEPINST MOVE;

Table altered.

SQL> ALTER TABLE PSWORKLIST MOVE;

Table altered.

SQL> ALTER TABLE PS_TEST_TAUTH_AW MOVE;

Table altered.

SQL> ALTER TABLE PS_TEST_SHEET_AW MOVE;

Table altered.

SQL> ALTER TABLE PS_TEST_ADV_AW MOVE;

Table altered.

SQL> spool off


Step 4: Rebuild indexes online

spool rebuild.out

ALTER INDEX PSATEST_WL REBUILD ONLINE;
ALTER INDEX PSBTEST_WL REBUILD ONLINE;
ALTER INDEX PS_TEST_WL REBUILD ONLINE;
ALTER INDEX PSATEST_USERINST REBUILD ONLINE;
ALTER INDEX PSBTEST_USERINST REBUILD ONLINE;
ALTER INDEX PSCTEST_USERINST REBUILD ONLINE;
ALTER INDEX PSDTEST_USERINST REBUILD ONLINE;
ALTER INDEX PSETEST_USERINST REBUILD ONLINE;
ALTER INDEX PS_TEST_USERINST REBUILD ONLINE;
ALTER INDEX PSBTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSCTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSDTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSETEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSFTEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSATEST_STEPINST REBUILD ONLINE;
ALTER INDEX PS_TEST_STEPINST REBUILD ONLINE;
ALTER INDEX PSBPSWORKLIST REBUILD ONLINE;
ALTER INDEX PS0PSWORKLIST REBUILD ONLINE;
ALTER INDEX PSAPSWORKLIST REBUILD ONLINE;
ALTER INDEX PS_TEST_TAUTH_AW REBUILD ONLINE;
ALTER INDEX SYS_IL0000025105C00012$$ REBUILD ONLINE;
ALTER INDEX PSATEST_SHEET_AW REBUILD ONLINE;
ALTER INDEX SYS_IL0000024994C00012$$ REBUILD ONLINE;
ALTER INDEX PSATEST_SHEET_AW REBUILD ONLINE;
ALTER INDEX SYS_IL0000024994C00012$$ REBUILD ONLINE;
ALTER INDEX PS_TEST_ADV_AW REBUILD ONLINE;
spool off

Step 5:Check the status of INDEX after rebuild
Check the status of INDEX after rebuild,make sure it is in valid state or else rebuild the INDEX again.
SELECT INDEX_NAME||' '||INDEX_TYPE||' '||STATUS
FROM USER_INDEXES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');

Step 6:Run the gather stats for the tables.
spool gather_stats_tables_TESTDB.out

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_WL',
    method_opt =>'for all columns size auto');
END;
/

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PSWORKLIST',
    method_opt => 'for all columns size auto');
END;
/

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_USERINST',
    method_opt => 'for all columns size auto');
END;
/

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_STEPINST',
    method_opt => 'for all columns size auto');
END;
/

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_TAUTH_AW',
    method_opt => 'for all columns size auto');
END;
/

BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_ADV_AW',
    method_opt => 'for all columns size auto');
END;
/
BEGIN
  DBMS_STATS.gather_table_stats(
    'SYSADM',
    'PS_TEST_SHEET_AW',
    method_opt => 'for all columns size auto');
END;
/

spool off

Step 7:Verify the statistics ,tables size after reorganization

SQL>  SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES
  2  WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
----------------------------------------------------------------------------------------------------
SYSADM PS_TEST_ADV_AW 28-NOV-13 NO 1716
SYSADM PS_TEST_SHEET_AW 28-NOV-13 NO 197699
SYSADM PS_TEST_TAUTH_AW 28-NOV-13 NO 128592
SYSADM PSWORKLIST 28-NOV-13 NO 936531
SYSADM PS_TEST_STEPINST 28-NOV-13 NO 292227
SYSADM PS_TEST_USERINST 28-NOV-13 NO 5111123
SYSADM PS_TEST_WL 28-NOV-13 NO 652895

7 rows selected.

SQL>  SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024 FROM DBA_SEGMENTS
  2   WHERE SEGMENT_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024
----------------------------------------------------------------------------------------------------
SYSADM PS_TEST_ADV_AW .25
SYSADM PS_TEST_ADV_AW .125
SYSADM PS_TEST_SHEET_AW 46
SYSADM PS_TEST_SHEET_AW 31
SYSADM PS_TEST_TAUTH_AW 18
SYSADM PS_TEST_TAUTH_AW 4
SYSADM PSWORKLIST 168
SYSADM PS_TEST_STEPINST 18
SYSADM PS_TEST_STEPINST 19
SYSADM PS_TEST_USERINST 365
SYSADM PS_TEST_USERINST 93

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024
----------------------------------------------------------------------------------------------------
SYSADM PS_TEST_WL 88
SYSADM PS_TEST_WL 64

13 rows selected.

Enjoy DBA tasks and Performance tuning.


Best regards,

Rafi.

My favourite book for Oracle SOA governance 11g implementation

Hi,
   SOA is always my favourite technical area,got interest in reading and Understanging Oracle SOA Governance.I devoted my time in studying Oracle SOA Suite 11g implementation,this time again I was  lucky to get book from packt publication "Oracle SOA governance 11g implementation for Oracle SOA developers.

To buy this book follow the below link provided below:

http://www.packtpub.com/oracle-soa-governance-11g-implementation/book


Brief about Author: Luis is an expert in SOA and also is a thought leader in this field. Having always
had a natural talent for software, computers, and engineering in general, Luis's
career in software started from an early age.
Andrew first started working in the SOA space
10 years ago and has successfully delivered many Oracle Middleware projects for
large and blue chip clients. He currently works at CSC, where he is employed as a
BPM/SOA Solution Architect for the Oracle Global Practice

Brief about Book:
This books covers various the following topics which will help you work in Oracle SOA suite 11g:

1)SOA Governance, describes in detail the key concepts around SOA
Governance and the Oracle SOA Governance Solution infrastructure.
2)Implementation Case Study, highlights the typical problems and
requirements addressed by a typical SOA Governance implementation and
highlights the key critical success factors for the implementation.
3)Introduction to Oracle Enterprise Repository, describes the core concepts,
management consoles, and capabilities supported by OER.
4)Initial Configuration, describes the prerequisites and illustrates the steps to
be considered when configuring OER for the first time.
5)Harvesting, describes the prerequisites and steps for bootstrapping and
harvesting assets into OER.
6)Asset Lifecycle and Workflow, describes how to implement policies,
compliance templates, and workflows in OER in support of the different asset
lifecycle stages.
7)Oracle Service Registry

It is really nice book for SOA Develpers,really love to read this book lots of times to understand more about Oracle SOA Suite 11g implementation and handling in details from practical point

of view.


Happy Oracle SOA Implementaion works...


Best regards,

Rafi.

Advantage of AFPASSWD utility in Oracle EBS R 12.1.2

Advantage of AFPASSWD utility in Oracle EBS R 12.1.2

   I was just experimenting with some test cases and got chance to work with AFPASSWD,which is advantageous  in comparison with FNDCPASS.

Below are the advantages:

1)We can run this utility from DB tier and Apps tier.

2)We can make sure the password is hidden(invisible to users),while changing,which is very good security option.

Below are some of the syntax to use it:

AFPASSWD [-c APPSUSER[@TWO_TASK]] [-f FNDUSER]
AFPASSWD [-c APPSUSER[@TWO_TASK]] [-o DBUSER]
AFPASSWD [-c APPSUSER[@TWO_TASK]] [-a]
AFPASSWD [-c APPSUSER[@TWO_TASK]] [-l ORACLEUSER [TRUE] |
[FALSE]]
AFPASSWD [-c APPSUSER[@TWO_TASK]] [-L [TRUE] | [FALSE]]
AFPASSWD [-c APPSUSER[@TWO_TASK]] [-s] APPLSYS

Eg:When you login with 'applmgr' and after setting environment,we can run this utility as below:

AFPASSWD -c apps@TWO_TASK -s APPLSYS

Enjoy apps DBA tasks....


Thanks,

Rafi.

Oracle Apps DBA open position

Hi Friends,

Apps DBA position open in Bangalore location.Below are more details on this job

Job Description:

• Minimum of 4 years of experience as Oracle Applications 11i/R12 DBA & Oracle DBA, providing 24x7 support of both Production & Development on Various Platforms.
• Exposure to RAC, ASM, GRID/OEM, partitioning, Data Guard or replication a plus.
• Must have experience with Unix servers -AIX, HP-UX, SOLARIS, LINUX
• Demonstrated experience with high levels of accountability
• Demonstrated initiative, innovation, flexibility, and ability to manage workload; achieving desired results with minimal supervision
• Must be proficient in the English language and have strong communication skills.

Principle accountabilities include:

• Assist in tool evaluation, integration and support
• Database cloning and 11i/R12 Apps Cloning
• Assist in perform Oracle Apps Administration and Sysadmin tasks
• Assist in troubleshooting Forms server, Reports server, Apache server, Jserv, Jinitiator and Workflow.
• Assist in applying patches, upgrades, frameworks for 11i/R12 apps.
• Ensuring high availability, backup and recovery of production databases.
• Database planning, scaling and capacity planning.

Interested DBAs  can mail resume to rsahu@teksystems.com 


Best regards,

Rafi.