Skip to content

Instantly share code, notes, and snippets.

View dincosman's full-sized avatar

Osman DINC dincosman

11 followers · 20 following
View GitHub Profile
@dincosman
dincosman / alter_fda_remove_quota.sql
Created January 11, 2023 21:04
Remove quota clause from flashback data archive
-- REMOVE QUOTA FROM FLASHBACK DATA ARCHIVE WITH SPECIFYING NO QUOTA CLAUSE
SQL> ALTER FLASHBACK ARCHIVE FDA_TEST MODIFY TABLESPACE USERS_TEST ;
@dincosman
dincosman / dba_mviews.sql
Last active October 7, 2023 06:21
Dba_mviews select query
SELECT
s.sowner AS
owner,
s.vname AS
mview_name,
s.tname AS
container_name,
s.query_txt AS
query,
s.query_len
@dincosman
dincosman / execution_plan_dba_mviews.txt
Created January 21, 2023 14:48
Execution plan of the "select * from dba_mviews" query
Execution Plan
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 260 | 171 (5)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | SNAP$ | 3 | 366 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_SNAP2 | 1 | | 0 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 16 | | |
|* 4 | FILTER | | | | | |
| 5 | TABL
@dincosman
dincosman / disable_direct_insertlogging_event.sql
Created January 22, 2023 19:48
Switch to disable direct insert logging in sumdelta$
SQL> alter system set events '10978 trace name context forever, level 128';
@dincosman
dincosman / stale_since_query.sql
Last active October 7, 2023 06:22
The time from when the materialized view became stale query and the execution plan
SELECT
(
SELECT
MIN(time_dp)
FROM
sys.smon_scn_time
WHERE
( scn_wrp * 4294967296 + scn_bas ) > (
SELECT
MIN(t.spare3)
@dincosman
dincosman / index_on_smon_scn.sql
Created January 22, 2023 21:03
create index on smon_scn_time table
SQL> CREATE INDEX SYS.SMON_SCN_TIME_TIM_IDX_TEMP ON SYS.SMON_SCN_TIME
(SCN_WRP * 4294967296 + SCN_BAS)
LOGGING
TABLESPACE SYSAUX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
@dincosman
dincosman / stale_since_query2.sql
Created January 22, 2023 21:08
The new execution plan of stale_since_query
Execution Plan
----------------------------------------------------------
Plan hash value: 1875754335
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 112 | 17 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | SMON_SCN_TIME | 97 | 2037 | 6 (0)| 00:00:01 |
@dincosman
dincosman / parse_error_after_RU.txt
Last active October 28, 2023 17:39
Parse errors recorded after 19.16RU
2023-05-21T17:18:03.998348+03:00
PARSE ERROR: ospid=28682, error=923 for statement:
Additional information: hd=0x6ae210f0 phd=0x8bdc7d18 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=d431n9ajscp24
...Current username=SYS
...Application: sqlplus@blt01.localdomain (TNS V1-V3) Action:
@dincosman
dincosman / query_kglob.txt
Last active October 22, 2023 14:44
Querying sys.x$kglob for failing sql statements
SQL> select kglhdpar, kglhdadr, kglobt03, kglnaobj from sys.x$kglob where kglobt03='d431n9ajscp24'
KGLHDPAR KGLHDADR KGLOBT03 KGLNAOBJ
---------------- ---------------- --------------- --------------------------------------------------
000000008BDC7D18 000000006AE210F0 d431n9ajscp24 select 1923
000000008BDC7D18 000000008BDC7D18 d431n9ajscp24 select 1923
@dincosman
dincosman / cursordump.txt
Last active October 28, 2023 17:39
Dumping failed sql statement cursor information
alter system set events '10035 cursordump(1)';