This is a library for DDL Operations
This will be the repository of the functions

[code language=”sql”]

#!/bin/ksh
#################################################################################################

lst_objs_all()
{
##############################
# List objs for schema
##############################

echo
echo
echo

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
column object_name format a25
— spool <some file name>

set serveroutput on size 1000000

BEGIN
dbms_output.put_line(‘—————————————————————–‘);
dbms_output.put_line(‘<<<<<<<<<<<<<<<<<<<< All database objects >>>>>>>>>>>>>’);
dbms_output.put_line(‘—————————————————————–‘);
END;
/

— from dba_segments
— where owner=’SCHEMA_OWNER’;

select table_name,
tablespace_name,
avg_row_len*num_rows
from dba_tables
where owner not in(‘SYS’,’SYSTEM’,’DBSNMP’,’WMSYS’,’OUTLN’,’APPQOSSYS’,’ORACLE_OCM’,’CORP_AUDIT’)
order by table_name asc;

BEGIN
dbms_output.put_line(‘————————————————————————‘);
dbms_output.put_line(‘<<<<<<<<<<<<< Schema Objects Count by object type >>>>>>>>>>>>>>>>>>>>>>’);
dbms_output.put_line(‘————————————————————————‘);
END;
/

select owner,
count(object_type) "Count",
object_type
from all_objects
where owner not in(‘SYS’,’SYSTEM’,’DBSNMP’,’WMSYS’,’OUTLN’,’APPQOSSYS’,’ORACLE_OCM’,’CORP_AUDIT’)
group by owner, object_type
order by owner;

EOF

}

sho_users()
{
##############################################
# Show the quotas for users
##############################################
echo
echo "Show the users "
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
select username
from dba_users
order by 1;
EOF

}

sho_quotas()
{
##############################################
# Show the quotas for users
##############################################
echo
echo "Show the quotas for users "
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150

PROMPT
PROMPT Show the quotas for all of the users
PROMPT

select * from dba_ts_quotas
order by tablespace_name;

EOF
}

del_app_users()
#############################################################
# This is a generic drop user cascade function to drop users
#############################################################
{
echo
sqlplus -s "/ as sysdba" ;EOF
set echo on
set feed on
set time on
set timing on
spool
drop role <role name>_ROLE;
commit;
spool off
exit
EOF
}

cr_app_users()
{
echo "Create application users"
}

lst_objs_schm()
{
##############################
# List objs for schema
##############################

SCHM=$1

echo
echo "List objects for schema ${SCHM}…"
echo
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set feedback off
column object_name format a25
— spool <some file name>

set serveroutput on size 1000000

BEGIN
dbms_output.put_line(‘—————————————————————–‘);
dbms_output.put_line(‘<<<<<<<<<<< ${SCHM} Objects >>>>>>>>>>>>>’);
dbms_output.put_line(‘—————————————————————–‘);
END;
/

— from dba_segments
— where owner=’${SCHM}’;

select table_name,
tablespace_name,
avg_row_len*num_rows
from dba_tables
where owner=’${SCHM}’
order by table_name asc;

PROMPT
PROMPT
PROMPT

BEGIN
dbms_output.put_line(‘—————————————————————–‘);
dbms_output.put_line(‘<<<<<<<<<<< ${SCHM} Objects Count ${SCHM} >>>>>>>>>>>>>>>>>’);
dbms_output.put_line(‘—————————————————————–‘);
END;
/

select owner,
count(object_type) "Count",
object_type
from all_objects
where owner=’${SCHM}’
group by owner, object_type;

EOF

}

sho_seq()
{

echo
echo
echo

schm=$1

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150

PROMPT
PROMPT Showing sequences for ${schm}
PROMPT

select count(*) from dba_sequences where sequence_owner=’${schm}’;

select sequence_name, sequence_owner from dba_sequences where sequence_owner=’${schm}’;

EOF
}

test_conn_user()
{

###############################################################
# This is the one table currently not being produced by Zafin
# pass the environment name and the password for the user
###############################################################

ENV=$1
PWRD=$2

echo
echo "User : "${ENV}
echo

sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150

connect ${ENV}/${PWRD}

select owner,
table_name
from all_tables
order by owner;

select object_name
from all_objects
where object_type=’SYNONYM’;

EOF
}

[/code]