ITRemote

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

#!/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
}