Oracle To SQL Migration – Permissions

Posted Leave a commentPosted in Oracle

Objective:

To migrate oracle database objects to SQL Server via SSMA, the following permissions requires from Oracle (Oracle Permissions to Migrate via SSMA)

Reference:

https://docs.microsoft.com/en-us/sql/ssma/oracle/connecting-to-oracle-database-oracletosql

Required Oracle Permissions:

The account that is used to connect to the Oracle database must have at least CONNECT permissions. This enables SSMA to obtain metadata from schemas owned by the connecting user. To obtain metadata for objects in other schemas and then convert objects in those schemas, the account must have the following permissions: +

•CREATE ANY PROCEDURE
•EXECUTE ANY PROCEDURE
•SELECT ANY TABLE
•SELECT ANY SEQUENCE
•CREATE ANY TYPE
•CREATE ANY TRIGGER
•SELECT ANY DICTIONARY

TimeStamp of Change To a Row In Oracle Table

Posted Leave a commentPosted in Oracle

Objective:

To find the recent modified date time of the object in oracle can be find out by using the following scripts. If we keep only the schema restriction, it can pull out all the tables from that owner or schema (Date and Time of Most Recent Change To a Row In Oracle Table)

Many of the technical experts trying to find out when the table lastly modified for several reasons. This below script is a handy tool for them to assist.

 

    declare
    v_count TIMESTAMP;
    strsql VARCHAR2(200);
    tblname VARCHAR2(32);
begin

    for r in (select OBJECT_NAME from all_objects where OBJECT_TYPE='TABLE' 
    and OWNER='OWNERNAME' 
    and OBJECT_NAME like 'TABLENAME%'  -- ANY OBJECT NAME CAN BE WRITTEN HERE 
    ORDER BY OBJECT_NAME) 
    loop
      begin
        tblname := R.OBJECT_NAME;
        strsql := 'select SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM OWNERNAME.' || tblname;
        execute immediate strsql into v_count;
        DBMS_OUTPUT.PUT_LINE(tblname || ',' || v_count);
        EXCEPTION
        when others then 
          null;
         end;