difference between table default oracle and table for user transactions

[Brian Hogantara]

Hi erman,
i'm using database oracle 11g,

did you know some of queries or a document that related something about difference between table configuration default from oracle and table for transactions ??

the issue is,
i want to delete table transactions include the indexes because data is already bigger every single day.
but i don't know how to distinguish it.

before i asked you this questions,
i try to delete this tables randomly once. afterwards, i cannot open the applications

Regards,
brian

[ErmanArslansOracleBlog]

I didn't understand your question at all. Please revise.

1)What do you mean by - > table configuration default from oracle
2)What do you mean by -> table for transactions
3)What do you want to do? What is your problem? Deleting data? Having indexes for performance?

[Brian Hogantara]

difference between seeded table, static table and transactional table

Seeded Tables - Fnd_table name are the seeded tables in apps
Static Tables - Static tables are one that are updated using some scripts or triggers.
Transactions tables - Tables that link the transactions between modules (Eg: PO & AP) or Tables in which a transaction data gets stored.

i want to delete transactions data, but i don't know the different between seeded table and transactions table.

[ErmanArslansOracleBlog]

You can't just delete the data from standard tables.
You need to APIs.
In some cases, you need to open SR and get datafix scripts..

So , tell me the details.. Why you want to delete data? What is your problem (in details) ?

[Brian Hogantara]

the plan is :
1. delete transactions table in development instance
2. create new table in development instance
3. "copy" old data from prod instance and "paste" in to the development instance
4. delete old data from prod instance
5. make link report to pulling old data from prod instance apps to development instance

we still need the report from old data and at the same time to shrink size data from prod instance to make performance is still controllable.

maybe the plan still can change. first of all, i want to know the different between those table(Seeded table, transactions table and static table)

[ErmanArslansOracleBlog]

Write the names of these tables on which you are planning to do your operations..

[Brian Hogantara]

- WIP
WIP_LINES
WIP_OPERATIONS
WIP_ENTITIES
WIP_DISCRETE_JOBS
WIP_TRANSACTIONS
wip_move_transactions
wip_discrete_jobs
wip_repetitive_schedules
wip_move_txn_interface
wip_cost_txn_interface
wip_job_dtls_interface

po_requisition_headers_all
po_requisition_lines_all
po_req_distributions_all
po_headers_all
po_lines_all
po_line_locations_all
po_distributions_all
rcv_shipment_headers
rcv_shipment_lines

Order Management (OM):
---------------------------
oe_order_headers_all
oe_order_lines_all
oe_transaction_types_tl
oe_order_holds
oe_hold_sources
oe_hold_releases
wsh_delivery_details
wsh_new_deliveries
wsh_delivery_Assignments
wsh_trip_stops
hz_cust_accounts
hz_parties
hz_cust_site_uses_all
hz_cust_acct_sites_all
hz_party_sites
hz_locations


Accounts Payable(AP):
-----------------------
ap_invoices_all
ap_invoice_distributions_all
ap_checks_all
ap_invoice_payments_all
ap_payment_schedules_all
ap_holds_all
ap_bank_branches
ap_ae_headers_all
ap_ae_lines_all

Accounts Receivables(AR):
---------------------------
ra_customer_trx_all
ra_customer_trx_lines_all
ra_cust_trx_line_gl_dist_all
ar_receivable_applications_all
ar_cash_Receipts_all
ar_terms
hz_cust_accounts
hz_parties
hz_cust_site_uses_all
hz_cust_acct_sites_all
hz_party_sites
hz_locations

InvenvtoryModule(INV):
-------------------------
mtl_System_items_b
mtl_reservations
mtl_material_transactions
mtl_item_locations
mtl_item_categories
mtl_secondary_inventories
mtl_transaction_Accounts
mtl_txn_source_types
mtl_parameters

General Ledger(GL) :
--------------------

gl_je_headers
gl_je_lines
gl_je_batches
gl_code_combinations
gl_je_sources
fnd_currencies
gl_balances
GL_JE_SEGMENT_VALUES
GL_BC_PACKETS
GL_BC_PACKETS_HISTS

[ErmanArslansOracleBlog]

Okay, you need EBS archiving..

You can't just delete rows from the tables.
Move data from one instance to another partially and so on..
These are unsupported things..

But what you can do is EBS archiving.

Clone your environment to have an exact copy of PROD instance.
Then plan to implement archiving on PROD -> Reducing Your Oracle E-Business Suite Data Footprint using Archiving, Purging, and Information Lifecycle Management (Doc ID 752322.1). There is a pdf attached to this note, please review it.