Oracle command to create a table from another schema, including triggers?

Using this command, I am able to create a table from another schema, but it does not include triggers. Is it possible to create a table from another schema, including triggers?

create table B.tablename unrecoverable as select * from A.tablename where 1 = 0;

1 answer

  • answered 2018-01-11 20:44 Littlefoot

    First option is to run CREATE script for those objects, if you have a code repository. I suppose you don't.

    If you use any GUI tool, things are getting simpler as they contain the SCRIPT tab that enables you to copy code from source and paste it into target user.

    If you're on SQLPlus, it means that you should, actually, know what you're supposed to do. Here's a short demo.

    SQL> connect hr/hr@xe
    Connected.
    SQL> create table detail (id number);
    
    Table created.
    
    SQL> create or replace trigger trg_det
      2  before insert on detail
      3  for each row
      4  begin
      5    :new.id := 1000;
      6  end;
      7  /
    
    Trigger created.
    
    SQL>
    
    SQL> -- you'll have to grant privileges on table to another user
    SQL> grant all on detail to scott;
    
    Grant succeeded.
    

    Connect as SCOTT and check what we've got:

    SQL> connect scott/tiger@xe
    Connected.
    SQL> -- now, query ALL_SOURCE and you'll get trigger code
    SQL> set pagesize 0
    SQL> col text format a50
    SQL> select text from all_source where name = 'TRG_DET' order by line;
    trigger trg_det
    before insert on detail
    for each row
    begin
      :new.id := 1000;
    end;
    
    6 rows selected.
    
    SQL>
    

    Yet another option is to export & import table, which will get the trigger as well (I've removed parts that aren't relevant, as Oracle database version):

    C:\>exp hr/hr@xe tables=detail file=detail.dmp
    About to export specified tables via Conventional Path ...
    . . exporting table                         DETAIL          0 rows exported
    Export terminated successfully without warnings.
    
    C:\>imp scott/tiger@xe file=detail.dmp full=y
    . importing HR's objects into SCOTT
    . importing HR's objects into SCOTT
    . . importing table                       "DETAIL"          0 rows imported
    Import terminated successfully without warnings.
    
    C:\>
    

    Check what's imported (should be both table and trigger):

    SQL> desc detail
     Name                                      Null?    Type
     ----------------------------------------- -------- ---------------
     ID                                                 NUMBER
    
    SQL> select * From detail;
    
    no rows selected
    
    SQL> insert into detail (id) values (-1);
    
    1 row created.
    
    SQL> select * From detail;
    
            ID
    ----------
          1000
    
    SQL>
    

    Cool; even the trigger works.

    There might be some other options, but these 4 should be enough to get you started.