Skip to content

Instantly share code, notes, and snippets.

View mvelikikh's full-sized avatar

Mikhail Velikikh mvelikikh

3 followers · 0 following
View GitHub Profile
@mvelikikh
mvelikikh / object_number_cache_size.sql
Created February 12, 2024 11:44
object_number_cache_size
SQL> sho parameter object_number_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_object_number_cache_size integer 10
SQL> select obj#, dataobj# from obj$ where name='_NEXT_OBJECT';
OBJ# DATAOBJ#
---------- ----------
@mvelikikh
mvelikikh / default_pct_free.log
Created September 14, 2023 14:15
default_pct_free
SQL> conn / as sysdba
Connected.
SQL> select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
@mvelikikh
mvelikikh / dbms_metadata_cbac.sql
Created August 29, 2023 09:51
CBAC DBMS_METADATA
def connect_string="rac1:1522/pdb"
set echo on
conn / as sysdba
alter session set container=pdb;
drop user power_user cascade;
drop user scott cascade;
drop user asktom cascade;
create user power_user no authentication;
@mvelikikh
mvelikikh / output.txt
Created January 16, 2023 17:20
Enable SQL_TRACE for a given SQL_ID and tracefile_identifier
[oracle@rac1 ~]$ sqlplus /nolog @sql_trace_tracefile_identifier.sql
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 16 17:13:28 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb;
@mvelikikh
mvelikikh / after.trc
Created August 11, 2022 16:31
async_gi_maintenance
Trace file /u01/app/oracle/diag/rdbms/racdbb/racdbb1/trace/racdbb1_ora_335870_AFTER.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
Build label: RDBMS_19.11.0.0.0DBRU_LINUX.X64_210412
ORACLE_HOME: /u01/app/oracle/product/19.3.0/dbhome_2
System name: Linux
Node name: rac1.mycompany.mydomain
Release: 5.4.17-2136.308.9.el8uek.x86_64
Version: #2 SMP Mon Jun 13 20:36:40 PDT 2022
Machine: x86_64
@mvelikikh
mvelikikh / output.sql
Created January 31, 2022 18:57
SQL Trace with filter by FORCE_MATCHING_SIGNATURE
SQL> @q
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL> alter session set container=pdb;
Session altered.
SQL>
@mvelikikh
mvelikikh / sql_trace_filter_by_client_id.sql
Created December 22, 2021 20:40
SQL Trace with filter by client_id and sql_id
[oracle@db-21 ~]$ sqlplus /nolog @q
SQL*Plus: Release 21.0.0.0.0 - Production on Wed Dec 22 20:39:06 2021
Version 21.4.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb;
@mvelikikh
mvelikikh / sql_trace_filter_by_username.sql
Created December 21, 2021 15:55
sql_trace filter by username
[oracle@db-21 ~]$ sqlplus /nolog @q
SQL*Plus: Release 21.0.0.0.0 - Production on Tue Dec 21 15:54:22 2021
Version 21.4.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb;
@mvelikikh
mvelikikh / output.txt
Created December 10, 2021 11:13
dbms_sql_translator ** to power
[oracle@db-21 ~]$ sqlplus tc/tc@db-21/pdb @q
SQL*Plus: Release 21.0.0.0.0 - Production on Fri Dec 10 11:11:44 2021
Version 21.4.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Fri Dec 10 2021 11:10:25 +00:00
Connected to:
@mvelikikh
mvelikikh / gist:c9f3936270b9b4bab9554e30f0935638
Created November 4, 2020 13:07
oracle ctx_output logging
drop table ctxtest;
create table ctxtest(stuff varchar2(30));
create index tm_cc_tc_item_txt_cat on ctxtest(stuff) indextype is ctxsys.ctxcat;
desc DR$TM_CC_TC_ITEM_TXT_CAT$I
insert into ctxtest(stuff) values (lpad('x', 30 ,'x'));