life is way too long not do well.

Life is too short not to be happy.

Be happy.

Relationship with the people you love is more important to your physical health than the foods you eat, the genes you have.

So either love, or leave.

1, Create Windows service for database RMAN

oradim -new -sid RMAN -intpwd change_on_install -startmode a

This command will create windows service (automatic start on reboot).

2. Create password file

orapwd file=C:\Oracle\ora92\database\PWDRMAN.ora password=change_on_install entries=20

3, Create spfile from pfile

C:\Oracle\>sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 – Production on Mon Mar 26 00:20:52 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL>connect sys/change_on_install@rman as sysdba

SQL>create spfile from pfile=’c:\oracle\dba\init.ora’;

Your tablespace is getting lot of IO, or has grown to unmanageable size. Now you want to move some tables from one tablespace to a new, better orgainzed tablespace. Here is how.

1. Create a new locally managed tablespace

CREATE TEMPORARY TABLESPACE COSTPOINT_DATA4
TEMPFILE 'K:\oradata\SIIT\COSTPOINT_DATA4_1.DBF' SIZE 2000M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 256K;

2, Create three sql files for moving tables to new tablespace, rebuild indexes and compute statistics

connect deltek/xxx@fin;
set heading off;
set feedback off;
set pagesize 1000;
spool C:\Oracle\job\reorg\reorg_tables.sql;

select 'connect deltek/xxx@fin;' from dual;
select 'alter table '||table_name||' move tablespace COSTPOINT_DATA4;'
from user_tables where tablespace_name='COSTPOINT_DATA';
spool off;

spool C:\Oracle\job\reorg\rebuild_index.sql;
select ‘connect deltek/xxx@fin;’ from dual;
select ‘alter index ‘|| INDEX_NAME|| ‘ rebuild TABLESPACE costpoint_INDEX1;’ from user_indexes
where table_name in (select table_name from user_tables where tablespace_name=’COSTPOINT_DATA’);
spool off;

spool C:\Oracle\job\reorg\compute_stat.sql;
select ‘connect deltek/xxx@fin;’ from dual;
select ‘analyze table ‘|| table_name|| ‘ compute statistics;’ from user_tables where tablespace_name=’COSTPOINT_DATA’;
spool off;

exit;

3, Run this batch file:

c:
cd C:\Oracle\job\reorg
sqlplus /nolog @reorg_tables.sql
sqlplus /nolog @rebuild_index.sql
sqlplus /nolog @compute_stat.sql

Tables in Oracle database become fragmented after mass deletion, or after so many delete and/or insert operations. If you are running a 24×7 shop, you don’t have an option to reorganize (or defragement) the table by traditional export/truncate/import method, i.e., exporting data from affected table, truncate the table, then importing data back to the table. Luckily, there is an “alter table table_name move” command that you can use to defragment tables. Follow these steps to make your customer happy. This method does not apply to tables with with ‘LONG’ columns.

1, Make a list of tables needed to be reorganized.

C:\>sqlplus /nolog

connect deltek/xxx@fin
set heading off;
set feedback off;
set pagesize 1000;
spool C:\Oracle\job\dba\chained_tables.sql;
select table_name, num_rows, chain_cnt from user_tables where chain_cnt >0;
spool off;
exit;

Output:

ASSET 7196 10
DFLT_REG_TS 14188 107
EMPL 14188 21
EMPL_LAB_INFO 51744 3
ESS_PAGE_TEXT 262 11
HB_CVG_OPT 353 2
HB_EMPL_PKG 5691 2
JNL_STATUS 11795 1
OPEN_AP 445934 698
PO_HDR_NOTES 10327 9
PO_LN_NOTES 7564 8
PROJ 51671 17
PROJ_BURD_SUM 5089903 74951
PROJ_EDIT 51671 4
PROJ_EMPL 1005025 2803
PROJ_EMPL_LAB_CAT 859823 1679
PROJ_LAB_CAT 384610 62
PROJ_SUM 3362926 922
PROJ_VEND_LAB_CAT 160866 6
SUB_PD_JNL_STATUS 27615 9
TS_HDR 170091 1070
TS_LN 353365 2
VEND_CHK 274322 1
Z_PRPPPAF_EMPL 972 5

2, Run this sql to create sql statements for defragmentation.

connect deltek/xxx@fin;
set heading off;
set feedback off;
set pagesize 1000;
spool C:\Oracle\job\dba\defrag.sql;
select 'alter table '||table_name||' move;'
from user_tables where chain_cnt >0;
spool off;

This will generate ouput:

alter table ASSET move;
alter table DFLT_REG_TS move;
alter table EMPL move;
alter table EMPL_LAB_INFO move;
alter table ESS_PAGE_TEXT move;
alter table HB_CVG_OPT move;
alter table HB_EMPL_PKG move;
alter table JNL_STATUS move;
alter table OPEN_AP move;
alter table PO_HDR_NOTES move;
alter table PO_LN_NOTES move;
alter table PROJ move;
alter table PROJ_BURD_SUM move;
alter table PROJ_EDIT move;
alter table PROJ_EMPL move;
alter table PROJ_EMPL_LAB_CAT move;
alter table PROJ_LAB_CAT move;
alter table PROJ_SUM move;
alter table PROJ_VEND_LAB_CAT move;
alter table SUB_PD_JNL_STATUS move;
alter table TS_HDR move;
alter table TS_LN move;
alter table VEND_CHK move;
alter table Z_PRPPPAF_EMPL move;


3, Generate SQL statements for rebuilding indexes after defragmentation

connect deltek/xxx@fin;
set heading off;
set feedback off;
set pagesize 1000;
spool C:\Oracle\job\dba\rebuild_index.sql;
select 'alter index '|| INDEX_NAME|| ' rebuild;' from user_indexes
where table_name in (select table_name from user_tables where chain_cnt >0);
spool off;

4, Generate SQL statements for compute statistics after defragmentation

set heading off;
set feedback off;
set pagesize 1000;
spool C:\Oracle\job\dba\compute_stat.sql;
select 'analyze table '|| table_name|| ' compute statistics;' from user_tables where chain_cnt >0;
spool off;

5, Defragment these chained tables.

SQL>@C:\Oracle\job\dba\defrag.sql

6. Rebuild indexes because these tables’s indexes are in unstable state.

SQL>@C:\Oracle\job\dba\rebuild_index.sql

7. Compute statistics

SQL>@C:\Oracle\job\dba\compute_stat.sql

8. Find out if it works.

SQL>select table_name, num_rows, chain_cnt from user_tables where chain_cnt >0;