mdvorkinOct 27, 20211 minGet DDL for Oracle objectset feed off set long 1000000 set longchunksize 1000000 set pages 0 set lines 32767 set trimspool on set feed off set wrap on set...
mdvorkinOct 27, 20211 minCancelled scheduled concurrent requestsThis SQL query returns all concurrent requests that were scheduled in the past and are not scheduled currently. It can be used for...
mdvorkinOct 27, 20212 minForeign key constraint violation/* This PL/SQL script checks violations of foreign key constraints Example of the tables: CREATE TABLE temp_supplier ( supplier_id...
mdvorkinSep 30, 20211 minFrom Concurrent to Trace-- Find running concurrent request's database session SELECT fcr.request_id, ftl.user_concurrent_program_name program_name, v.meaning...
mdvorkinJan 21, 20212 minAfter logon trigger - trace user sessions-- Activate trace for sessions using after logon database trigger -- -- Tables: -- -- SYS.XX_LOGIN_TRACE_CONDITION - add here the...
mdvorkinDec 10, 20201 minINCLUDE parameter of expdp-- Export all public database links expdp user/password full=y \ include=db_link:"in (select db_link from dba_db_links where owner =...
mdvorkinDec 10, 20201 minMultiplexing Redo Log and Control files-- List redo log files select group#, member from v$logfile order by group#, member; -- Check redo log file status select group#,...
mdvorkinDec 4, 20201 minAPEX - change user's passwordsqlplus / as sysdba select owner, table_name from dba_tables where table_name = upper('wwv_flow_fnd_user'); -- Change schema name to...
mdvorkinDec 4, 20201 minUNDO management-- UNDO tablespace space select tablespace_name tablespace, status, sum(bytes)/1024/1024 MB, count(*) counts from dba_undo_extents group...
mdvorkinDec 3, 20201 minRunning EBS concurrent requestsselect fpro.os_process_id pid, fcr.request_id, to_char(fcr.actual_start_date, 'DD/MM/YYYY HH24:MI:SS') start_date,...
mdvorkinDec 3, 20201 minDatabase locksselect /*+ ORDERED */ w.session_id waiting_session, h.session_id holding_session, w.lock_type, h.mode_held, w.mode_requested,...
mdvorkinDec 3, 20201 minViewing EBS user's sessions-- Change SYSADMIN to whatever you need SELECT DISTINCT usr.user_name, TO_CHAR(icx.last_connect,'DD/MM/YYYY HH24:MI:SS') connect_time,...
mdvorkinNov 24, 20201 minDecrypt user's password in EBS-- Create java function for decrypting password -- key - apps password (upper case) -- value - encrypted user password create or replace...
mdvorkinNov 19, 20208 minPersonalization and Diagnostics links in EBS 12.1.3In Oracle Applications there are several options allowing developers to change application behavior without altering code. This can be...
mdvorkinNov 19, 20201 minDatabase Users (with last password change date)select s.name "User", p.granted_role "Role", s.ptime "Password change date" from dba_role_privs p, sys.user$ s, dba_users d where s.name...
mdvorkinNov 19, 20201 minSystem Dump and Hang AnalyzeSystem Dump (should be done 3 times with intervals of at least 1 min): sqlplus "/ as sysdba" oradebug setmypid oradebug unlimit oradebug...
mdvorkinNov 19, 20201 minPipelined Functioncreate type int_set as table of integer; / create or replace function numerator return int_set pipelined is i integer := 0; begin i := 0;...
mdvorkinNov 19, 20201 minPerformance EventsEvent 10046 (Query Execution Plan trace) alter session set timed_statistics = true; alter session set statistics_level = ALL; alter...
mdvorkinNov 19, 20201 minORA-1693: max # extents 505 reached in lobsegmentSolve the following error in init.ora: ORA-1693: max # extents 505 reached in lobsegment APPLSYS.SYS_LOB0000255319C00040$$ sqlplus system...
mdvorkinNov 19, 20201 minFlashback Query Example-- Change table name and interval to whatever you need select * from fnd_user as of timestamp (systimestamp - interval '10' minute);