CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
J. A.
J. A.

Reputation: 15

Moving triggers and sequences from one schema to another

My problem consists of a need to move/copy triggers from one schema to another one. One of the problems is that I cannot create views crossing from schema1 to schema2, to solve this I want to move all to one big schema.

But doesn't appear that triggers and sequences will follow. Maybe I need to make a file ready that creates all triggers and sequences, and ensures that the sequences will be updated with the last number from the old schema..

Upvotes: 0

Views: 1381

Answers (2)

q4za4
q4za4

Reputation: 652

Mayby try to generate DDL and put it in file via:

declare
cl clob;

begin
--(optional) for rec in (select * from all_views) loop

SELECT DBMS_METADATA.get_ddl ('VIEW', VIEW_NAME, VIEW_OWNER)
into   cl
from   dual;
dbms_output.put_line( cl);
end;

Upvotes: 0

Rene
Rene

Reputation: 10541

"One of the problems is that I cannot create views crossing from schema1 to schema2"

A possible solution is to leave everything where it is and grant the necessary permissions to create the view you need.

grant select on schema1.table to schema2;

create schema2.view as select * from schema1.table;

Upvotes: 2

Related Questions