Replacing a word to a new word in vi editor

:%s/oldword/newword/g

eg: :%s/harsha/harshavardhan/g

:%s/oldword/newword/

eg: :%s/harsha/harshavardhan/

DROP_OBJECTS.SQL

1.Copy the below procedure to a file and execute the file from sys or system user.


CREATE OR REPLACE PROCEDURE DROP_OBJECTS(P_Owners varchar2) IScursor obj_crs isselect owner, object_name, object_typefrom all_objectswhere owner = upper(P_Owners);obj_REC obj_CRS%ROWTYPE;l_grants_objects varchar2(500);BEGIN dbms_output.put_line('Begining to drop all objects');
Case upper(P_Owners) when 'SYS' then dbms_output.put_line('Can drop objects for:'upper(P_Owners)); when 'SYSTEM' then dbms_output.put_line('Can drop objects for:'upper(P_Owners)); when 'WMSYS' then dbms_output.put_line('Can drop objects for:'upper(P_Owners)); when 'TSMSYS' then dbms_output.put_line('Can drop objects for:'upper(P_Owners)); when 'OUTLN' then dbms_output.put_line('Can drop objects for:'upper(P_Owners)); when 'DBSNMP' then dbms_output.put_line('Can drop objects for:'upper(P_Owners)); ELSE Begin FOR obj_REC IN obj_CRS LOOP begin dbms_output.put_line('OUT-1 -> ' obj_REC.object_type); CASE obj_REC.object_type WHEN 'TABLE' THEN l_grants_objects := 'drop table 'obj_REC.owner'.'obj_REC.object_name' cascade constraints PURGE'; EXECUTE IMMEDIATE l_grants_objects; -- WHEN 'MATERIALIZED VIEW' THEN -- dbms_output.put_line('OUT-2 -> INSIDE MATERIALIZED VIEW'); -- l_grants_objects := 'drop MATERIALIZED VIEW ' obj_REC.owner'.'obj_REC.object_name ; -- dbms_output.put_line(' OUT-2 -> ' l_grants_objects); -- EXECUTE IMMEDIATE l_grants_objects; WHEN 'INDEX' THEN l_grants_objects := ''; WHEN 'TYPE' THEN l_grants_objects := 'drop type 'obj_REC.owner'.'obj_REC.object_name' force'; EXECUTE IMMEDIATE l_grants_objects; ELSE dbms_output.put_line('OUT-2222** -> INSIDE MATERIALIZED VIEW'); l_grants_objects := 'drop 'obj_REC.object_type' 'obj_REC.owner'.'obj_REC.object_name; EXECUTE IMMEDIATE l_grants_objects; END CASE; dbms_output.put_line(l_grants_objects); Exception when others then l_grants_objects := ''; end; end loop; end; end case; dbms_output.put_line('All objects are dropped');End;/

2.Login to db as super user(sys,system) and execute the procedure replacing value with the username.

eg:-

sqlplus system/password@dbname

exec drop_objects('USERNAME');

exec drop_objects('IDS');

CREATE_SYNONYMS_GRANTS.SQL

1.create a 2nd user ending with '_user' where synonyms needs to be created of the original user.

eg:-

create user ids_user identified by ids_user;

2.Copy the below contents of procedure to a file and run the file as a superuser(sys,system)

CREATE OR REPLACE PROCEDURE CREATE_SYNONYMS_GRANTS(P_Owners varchar2) IScursor obj_crs isselect owner, object_name, object_typefrom all_objectswhere owner = upper(P_Owners)and upper(object_type) in ('TABLE','VIEW','PROCEDURE','FUNCTION','SEQUENCE','SYNONYM','TYPE','PACKAGE');
obj_REC obj_CRS%ROWTYPE;l_create_synonyms varchar2(500);l_grants_objects varchar2(500);
BEGIN FOR obj_REC IN obj_CRS LOOP begin --dbms_output.put_line(obj_REC.object_type); l_create_synonyms := 'create synonym 'obj_REC.owner'_USER.'obj_REC.object_name'for 'obj_REC.owner'.'obj_REC.object_name;
--dbms_output.put_line(l_create_synonyms';');
EXECUTE IMMEDIATE l_create_synonyms; Exception when others then dbms_output.put_line(SQLERRM); end; begin CASE obj_REC.object_type WHEN 'TABLE' THEN l_grants_objects := 'grant select, update, delete, insert on'obj_REC.owner'.'obj_REC.object_name' to ' obj_REC.owner'_user';
WHEN 'VIEW' THEN l_grants_objects := 'grant select on 'obj_REC.owner'.'obj_REC.object_name'to ' obj_REC.owner'_user';
WHEN 'PROCEDURE' THEN l_grants_objects := 'GRANT EXECUTE ON 'obj_REC.owner'.'obj_REC.object_name' to ' obj_REC.owner'_user';
WHEN 'FUNCTION' THEN l_grants_objects := 'GRANT EXECUTE ON 'obj_REC.owner'.'obj_REC.object_name' to ' obj_REC.owner'_user';
WHEN 'PACKAGE' THEN l_grants_objects := 'GRANT EXECUTE ON 'obj_REC.owner'.'obj_REC.object_name' to ' obj_REC.owner'_user';
WHEN 'SEQUENCE' THEN l_grants_objects := 'GRANT SELECT, ALTER ON 'obj_REC.owner'.'obj_REC.object_name' to ' obj_REC.owner'_user';
WHEN 'TYPE' THEN l_grants_objects := 'GRANT EXECUTE ON 'obj_REC.owner'.'obj_REC.object_name' to ' obj_REC.owner'_user';
END CASE;
--dbms_output.put_line(l_grants_objects';');
EXECUTE IMMEDIATE l_grants_objects; Exception when others then dbms_output.put_line(SQLERRM); end; end loop;End;

3.Execute the procedure as a super user(sys,system) replacing the value with original username

eg:-

exec CREATE_SYNONYMS_GRANTS('ORIGINAL_USER_NAME');

exec CREATE_SYNONYMS_GRANTS('IDS');