#********************************************************************************************************
#createpartition.ksh
#Script Name: createpartition.ksh
#Author:Bhabani S Maharana
#Date: 15-April-2006
#Info:This script creates the partition's for WCC_NNI_CONVEYANCE_EVENT table in BBFA_MGR schema.
#Version:
#*********************************************************************************************************
clear
tput cup 20 20
echo "Do you want to run the Script(y/n):\c"
read ans
if [ "$ans" = "y" ];then
tput cup 25 20
echo "Please enter the Database SID:\c"
read sid
DB_SID=$sid
tput cup 28 20
echo "Please enter the Schema Name:\c"
read user
SCHEMA_NAME=$user
tput cup 30 20 
echo "Please enter the Database password:\c"
read pass
PASSWORD=$pass
fi

export ORACLE_SID=${DB_SID}
sqlplus ${SCHEMA_NAME}/${PASSWORD}@${DB_SID}<
--@select_partition_table.sql;

conn /as sysdba

--@tablespace_size_stat.sql;

rem-----------------------------------------------------------------------------------------
if a new tablespace is to be added edit and run create_partition_tablespace.sql as SYS user. 
rem-----------------------------------------------------------------------------------------

@create_partition_tablespace.sql;

rem---------------------------------------------------------------------------------------------------
Here TABLESPACE_NAME refers to the partition tablespace created.
rem---------------------------------------------------------------------------------------------------

alter user bbfa_mgr quota unlimited on WCC_NNI_DATA_8;

rem--------------------------------------------------------------------------------------------------------
The partition should be added as BBFA_MGR database user and the script add_partition.sql is to be edited
rem--------------------------------------------------------------------------------------------------------

connect ${SCHEMA_NAME}/${PASSWORD}

@add_partition.sql;

rem------------------------------------------------------------------------------------------------
Run create_ind_partition_tablespace.sql after editing.
rem------------------------------------------------------------------------------------------------

conn /as sysdba

@create_ind_partition_tablespace.sql;

rem---------------------------------------------------------------------------------------------------
Here TABLESPACE_NAME refers to the index partition tablespace created.
rem---------------------------------------------------------------------------------------------------

alter user bbfa_mgr quota unlimited on WCC_NNI_INDEX_8;

rem----------------------------------------------------------------------------------------------------
Rebuild the partition index by running rebuild_partition_index.sql after editing it.
rem----------------------------------------------------------------------------------------------------

connect ${SCHEMA_NAME}/${PASSWORD}

conn /as sysdba

@rebuild_partition_index.sql;

commit;

EOF
###############################################
CODE FOR THE SCRIPTS CALLED BY THE ABOVE SHELL SCRIPT
###############################################
1. tablespace_size_stat.sql

column sfile new_value ofile noprint;
select upper(substr(global_name,1,(instr(global_name,'.')-1)))||'_tablespace_stat.lst' sfile from global_name;

column sid new_value osid noprint;
select upper(substr(global_name,1,(instr(global_name,'.')-1))) sid from global_name;
TTITLE CENTER "TABLESPACE SIZE DETAILS" skip 1 -
CENTER "========================" skip 2 -
LEFT "-----------------" skip 1 -
LEFT "ORACLE SID :" &osid skip 1 -
LEFT "-----------------" skip 1
column tablespace heading TABLESPACE
column total heading 'Size (MB)'
column free heading 'Free Space (MB)'
column used heading 'Used Space (MB)'
column pct_used heading '%Used'
break on report
compute sum label "TOTAL SIZE ----------->" of total on report
compute sum of free on report
set pagesize 200
set linesize 80
spool &ofile
col tablespace for a30
col total for 9999999
col used for 9999999
col pct_used for a6
select tablespace,total,free ,total-nvl(free,0) used,trim(to_char(round((total-nvl(free,0))*100/total)))||'%' "pct_used"
from 
(select b.tablespace_name ,round(sum(b.bytes)/1024/1024) free
from dba_free_space b
group by b.tablespace_name) b, 
(select a.tablespace_name tablespace ,round(sum(a.bytes)/1024/1024) total 
from dba_data_files a
group by a.tablespace_name ) a
where a.tablespace=b.tablespace_name(+)
order by a.tablespace
/
spool off
clear breaks
TTITLE OFF
______________________________________________

2. select_partition_table.sql

select table_name,table_owner,PARTITION_NAME,TABLESPACE_NAME from dba_tab_partitions; 

_____________________________________

3.create_partition_tablespace.sql

create tablespace WCC_NNI_DATA_8
datafile '/genius/oradata02//WCC_NNI_DATA_8_01.dbf'
size 2200M
default storage ( initial 50M next 50M pctincrease 0 minextents 1 maxextents 2147483645 )
ONLINE PERMANENT
/
___________________________________________
4.add_partition.sql

alter table WCC_NNI_CONVEYANCE_EVENT
add partition MAY2006 VALUES LESS THAN (TO_DATE(' 2006-05-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
tablespace WCC_NNI_DATA_8
STORAGE (
INITIAL 10K 
NEXT 10K 
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL RECYCLE
)

/
___________________________________

5.create_ind_partition_tablespace.sql

create tablespace WCC_NNI_INDEX_8
datafile '/genius/oradata02//WCC_NNI_INDEX_8_01.dbf'
size 200M
default storage ( initial 52428800 next 52428800 pctincrease 0 minextents 1 maxextents 2147483645 )
ONLINE PERMANENT
/
___________________________________
6.rebuild_partition_index.sql

select 'alter index '||index_name||' '||'rebuild partition '||partition_name
||' tablespace wcc_nni_index_8'||';' from dba_ind_partitions
where partition_name='MAY2006'
/
________________________________________