Exploring Oracle MFT 12c Database Tables

Oracle MFT Tables Useful For Gathering Daily Transactions

• Oracle MFT RCU utility creates multiple schemas for storing transfer configurations and runtime transaction data
• Below are some important schemas
• <Prefix>_MFT
• <Prefix>_MDS
• <Prefix>_ESS
• <Prefix>_OPSS
• <Prefix>_UMS
• Prefix can be like Dev, UAT or PROD. If we use MFT cloud service then prefix would be like SP<number>, number is randomly picked by oracle cloud orchestration logic which provisions MFT cloud service instance
• To gather daily statistics of transfers then we need to explore more on <Prefix>_MFT schema
• Below tables are useful under MFT schema

Table Name	Description
MFT_CALLOUT_INSTANCE	Contains data related to the actions or callouts performed during the transfers like Delete, Run Script PGP Encryption and so on
MFT_ERROR_INFO	Contains data related to the errors that occurs during mft transfers
MFT_SOURCE_MESSAGE	Contains data related to source details of the file transfer like source name, source endpoint and file name and so on
MFT_TARGET_INSTANCE	Contains data related to target details of the file transfer like transfer name, target name and status and so on
MFT_TARGET_MESSAGE	Contains data related to target details of the file transfer like target name, target endpoint and modified file name and so on
MFT_TRANSFER_INSTANCE	Contains data related to transfer name,status, creation and completed time and so on
MFT_TRIGGER_EVENT	Contains data when the source event is invoked for on demand file transfers and specifies the status of the event invoked
MFT_DATA_STORAGE	Contains information of payload file reference path, payload size and so on

• Query for source level details
• select source_name,source_endpoint_ref,file_name,status from mft_source_message
• Query for target level details
• select target_name,endpoint_reference,file_name,status from mft_target_message
• Composite query to fetch both source and target details of file transfers
• select distinct(MSG.FILE_NAME) Source_File_name,MTG.DELIVERED_FILE_NAME Target_File_name,MSG.source_name Source_name,MTG.target_name Target_name,MSG.source_endpoint_ref Source_Endpoint,MTG.endpoint_reference Target_Endpoint,TO_CHAR(MTG.transfer_completed_time, 'DD-MON-YY') Transfer_Completed_Time,MDS.PAYLOAD_SIZE,MTG.STATUS from MFT_SOURCE_MESSAGE MSG, MFT_TARGET_MESSAGE MTG,MFT_DATA_STORAGE MDS where trunc(MTG.transfer_start_time) = trunc(sysdate)and MTG.ecid = MSG.ecid and MSG.DATA_STORAGE_ID = MDS.ID order by transfer_completed_time desc    
• There are many other tables and views in *_MFT schema. Need to explore on these tables as per requirement