Oracle – Shrink LOB columns

--identifying lob segments using more space:
select sg.segment_name, lb.table_name , lb.column_name, sum(sg.bytes)/(1024*1024) mb
from dba_segments sg, dba_lobs lb
where sg.segment_name = lb.segment_name
group by sg.segment_name, lb.table_name , lb.column_name order by mb desc;
--generating shrink command to reduce the lob sizes
select sg.segment_name, lb.owner, lb.table_name, lb.column_name, sum (sg.bytes)/(1024*1024) mb
'alter table ||lb.owner||'.'|| lb.table_name || modify lob (' || lb.column_name || )
(shrink space) ;' shrink_command
from dba_segments sg, dba_lobs lb
where sg.segment_name = lb.segment_name
group by sg.segment_name, lb.owner, lb.table_name, lb.column_name order by mb desc;
--example:
alter table hr.employee modify lob (emp_address) (shrink space);

Reference:-

https://oracle-base.com/articles/misc/alter-table-shrink-space-online

Oracle Schema export using Datapump API

We wanted to schedule a nightly backup in a new Oracle 19c DB – however, we could not trigger unix script (expdp) from DBMS_SCHEDULER due a linux PAM missing. So, as a temporary arrangement we used DBMS_DATAPUMP API to perform this task as below. DBMS_DATAPUMP API comes handy when you want to perform export / import entirely within SQL Developer and don’t prefer to use the Unix command prompt.

We created a Procedure EXPORT_SCHEMA and scheduled to run daily as below. You can also find the links I referred below to prepare this.

create or replace NONEDITIONABLE procedure SYSTEM.EXPORT_SCHEMA IS
-- Data Pump job handle
h1 NUMBER;
v_current_time DATE := sysdate;
V_DBNAME varchar2(8) := ora_database_name;
v_schema_expression varchar2(100) := 'LIKE ''%MYSCHEMAS''';
v_dump_file_name VARCHAR2 (100);
v_log_file_name VARCHAR2 (100);
-- BEGIN
-- h1 := dbms_datapump.open('EXPORT', 'SCHEMA', NULL, 'EXP_JOB_' | to char(v_current_time, "YYYY_MM_DD_HH24MI'), 'LATEST');
-- v_ dump_file_name := V_DBNAME || 'DAILY_BACKUP' || trim(to_char(v_current_time, 'DAY')) || '%U.dmp;
-- v_log_file_name := V_DBNAME || '_DAILY_BACKUP' || trim(to_char(v_current_time, 'DAY')) || '_' || to_char(v_current_time, 'YYYY-MM-DD-HH24MI') || '.log';
-- dbms_datapump.add_file (handle => h1, filename => v_dump_file_name, directory => 'DATA_PUMP_DIR', fíletype => dbms_datapump.ku$_file_type_dump_file, reusefile => 1);
-- dbms_datapump.add_file (handle => h1, filename > v_log_file_name, directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
-- --A metadata filter is used to specify the schema that will be exported dbms_datapump.metadata_filter(h1, 'SCHEMA_EXPR', v_schema_expression) ;
-- dbms_datapump.set_parallel (h1, 8);
-- dbms_datapump.set_parameter(handle=>h1,name=>'COMPRESSION',value=>'ALL');
-- dbms_datapump.start_job(h1);
-- dbms_datapump.detach(h1); END / grant execute on SYSTEM.EXPORT_SCHEMA TO MYUSER; / begin dbms_scheduler.create_job( job_name=>'EXPORT_DATA_PUMP', job_type=> 'PLSQL_BLOCK', job_action=> 'BEGIN SYSTEM.EXPORT_SCHEMA; END;' , start_date => SYSTIMESTAMP end_date => SYSTIMESTAMP + INTERVAL '3650' day, repeat_interval => 'freq=daily; byday=mon, tue, wed, thu, fri, sat, sun; byhour=20; byminute=0', comments=>'Nightly Backup', enabled=>TRUE, auto_drop=>TRUE); end;

Reference Links:-

https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_api.htm

https://docs.oracle.com/database/121/SUTIL/GUID-5AAC848B-5A2B-4FD1-97ED-D3A048263118.htm#SUTIL977

https://www.morganslibrary.org/reference/datapump.html

Oracle Redaction details in the database

The dba tables in Oracle that display the redaction information (Oracle Advanced security) are redaction_columns & redaction_policies tables.

PUBLIC.redaction_columns
PUBLIC.redaction_policies
SYS.redaction_columns
SYS.redaction policies
SELECT * FROM redaction_columns;

OBJECT_OWNER|OBJECT_NAME|COLUMN_NAME|FUNCTION_TYPE|FUNCTION_PARAMETERS|REGEXP_PATTERN|REGEXP_REPLACE_STRING|REGEXP_POSITION|REGEXP_OCCURRENCE|REGEXP_MATCH_PARAMETER|COLUMN_DESCRIPTION|

SELECT * FROM redaction_policies;

OBJECT_OWNER|OBJECT_NAME|POLICY_NAME|EXPRESSION|ENABLE|POLICY_DESCRIPTION|

Oracle index in UNUSABLE state

Oracle indexes may get into UNUSABLE state due to multiple reasons – one of them could be a partition of a Partitioned table containing Global indexes was dropped without using ‘UPDATE INDEXES’ option.

Identifying indexes that are in UNUSABLE state (for a table):-
select *
from all_indexes where status = 'UNUSABLE' AND TABLE_NAME = 'my_table';

Rebuild the indexes so that they change to ‘VALID’ state. (use ‘Online’ option if the table is continuously used)

select 'alter index ' || OWNER || '.' || INDEX_NAME || ' rebuild online;'
from all_indexes where status = 'UNUSABLE' AND TABLE_NAME = 'my_table';

Oracle object locked by a session (v$locked_object, dba_hist_active_sess_history)

Find out which session is currently locking (causing deadlock) a particular object:-

select a.sid, a.serial# from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='MY_TABLE';

Find out which session(s) have locked an object in the history:-

Step 1:-
Scan the active session history for your session which is blocked by another session

select * from dba_hist_active_sess_history
where blocking_session is not null and current_obj# IN
(select object_id from dba_objects where owner = 'MY_SCHEMA' --Enter the object owner here:
--and object_name = 'TEST1 --Enter the object name here,
);

Step 2:-
Find the SQL ID to see what the blocking session was exactly doing

SELECT * FROM dba_hist_active_sess_history
WHERE SESSION_ID = 1234 AND SESSION_SERIAL# = 12345
and
current_obj# IN
(select object_id from dba_objects where owner = 'MY_SCHEMA' --Enter the object owner here:
--and object_name = 'TEST1 --Enter the object name here,
);

Step 3:-
Check the sql_fulltext of the sql_id in v$sql.

select sql_fulltext from v$sql;

Based on the following sql I got from stackoverflow.

SELECT *
from gv$active_session_history
--Or use this table for further back.
--from dba_hist_active_sess_history
where blocking_session is not null
and current_obj# =
(
select object_id
from dba_objects
where owner = 'JHELLER' --Enter the object owner here.
and object_name = 'TEST1' --Enter the object name here.
);

Oracle tablespace – add space / resize / check autoextend / set autoextend off

-- checking tablespace sizes
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
Tablespace|Used MB|Free MB|Total MB|Pct. Free|
----------|-------|-------|--------|---------|
SYSAUX | 606| 34| 640| 5|
UNDOTBS1 | 23| 2| 25| 8|
USERS | 2| 98| 100| 98|
SYSTEM | 352| 8| 360| 2|
--check auto extend for a datafile

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files ;
TABLESPACE_NAME|FILE_NAME |AUTOEXTENSIBLE|MAXBYTES |
---------------|---------------------------------------|--------------|-----------|
USERS |/u01/app/oracle/oradata/XE/users.dbf |YES |11811160064|
SYSAUX |/u01/app/oracle/oradata/XE/sysaux.dbf |YES |34359721984|
UNDOTBS1 |/u01/app/oracle/oradata/XE/undotbs1.dbf|YES |34359721984|
SYSTEM |/u01/app/oracle/oradata/XE/system.dbf |YES | 629145600|
--set auto extend off for a datafile (I think you cannot change it back)

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf'
AUTOEXTEND OFF;

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files ;
TABLESPACE_NAME|FILE_NAME |AUTOEXTENSIBLE|MAXBYTES |
---------------|---------------------------------------|--------------|-----------|
USERS |/u01/app/oracle/oradata/XE/users.dbf |NO | 0|
SYSAUX |/u01/app/oracle/oradata/XE/sysaux.dbf |YES |34359721984|
UNDOTBS1 |/u01/app/oracle/oradata/XE/undotbs1.dbf|YES |34359721984|
SYSTEM |/u01/app/oracle/oradata/XE/system.dbf |YES | 629145600|
--Resizing a datafile

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf'
RESIZE 100M;
--adding a new datafile for a tablespace (with Auto extend) to add more memory
ALTER TABLESPACE users
ADD DATAFILE '/u01/app/oracle/oradata/XE/users3.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
--adding a new datafile for a tablespace (without Auto extend) to add more memory
ALTER TABLESPACE users
ADD DATAFILE '/u01/app/oracle/oradata/XE/users2.dbf' SIZE 20M
AUTOEXTEND OFF;

Oracle SGA – System Global Area

The System Global Area (SGA) is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas. Read more

Checking SGA size of your database:-

SELECT * FROM v$sgainfo;
NAME |BYTES |RESIZEABLE|
----------------------------------|----------|----------|
Fixed SGA Size | 2233344|No |
Redo Buffers | 5541888|No |
Buffer Cache Size | 444596224|Yes |
Shared Pool Size | 176160768|Yes |
Large Pool Size | 4194304|Yes |
Java Pool Size | 4194304|Yes |
Streams Pool Size | 0|Yes |
Shared IO Pool Size | 0|Yes |
Granule Size | 4194304|No |
Maximum SGA Size |1068937216|No |
Startup overhead in Shared Pool | 75497472|No |
Free SGA Memory Available | 432013312| |

As you see above, I have 1 GB of SGA in my local DB. Enterprise databases having around 1 TB of SGA is not unusual (to have more buffer cache to reduce the disk reads).

Also, you can see that I have around 424 MB buffer cache and 412 MB free SGA. Let me try to add the free SGA to Buffer cache.

SQL> show parameter cache_size;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size	     big integer 0
db_16k_cache_size		     big integer 0
db_2k_cache_size		     big integer 0
db_32k_cache_size		     big integer 0
db_4k_cache_size		     big integer 0
db_8k_cache_size		     big integer 0
db_cache_size			     big integer 0
db_flash_cache_size		     big integer 0
db_keep_cache_size		     big integer 0
db_recycle_cache_size		     big integer 0

SQL> alter system set db_cache_size = 410M scope=both;

System altered.

SQL> show parameter cache_size;


NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size	     big integer 0
db_16k_cache_size		     big integer 0
db_2k_cache_size		     big integer 0
db_32k_cache_size		     big integer 0
db_4k_cache_size		     big integer 0
db_8k_cache_size		     big integer 0
db_cache_size			     big integer 412M
db_flash_cache_size		     big integer 0
db_keep_cache_size		     big integer 0
db_recycle_cache_size		     big integer 0

Query to check SGA target advise:-

select * from v$sga_target_advice order by sga_size;

A DEAD Scrum Master is a USELESS Scrum Master – Ken Schwaber

In Ken Schwaber’s book, “Agile Project Management with Scrum“, he says

Lessons Learned
The ScrumMaster’s job is to protect the team from impediments during the Sprint. However, the ScrumMaster has to operate within the culture of the organization. My mistake lay in
failing to recognize the value of teamwork to this organization. I had been a consultant for so long that I’d forgotten how much some large organizations cared about not rocking the
boat and keeping the corporate family together.
The ScrumMaster walks a fine line between the organization’s need to make changes as quickly as possible and its limited tolerance for change. Whenever possible, the
ScrumMaster makes a case and pushes the necessary changes through. The results are often greater productivity and greater return on investment (ROI). However, sometimes these
changes are culturally unacceptable and the ScrumMaster must acquiesce. Remember that Scrum is the art of the possible. A dead sheepdog is a useless sheepdog.

Cobol Code analyser

I had to analyse / review COBOL code as part of my job, and I have always found it difficult to do it from the basic ISPF browser. My friend Selva has a created a useful, simple tool to make this easier.
You can download it here. It is a jar file that will work if you have Java Runtime Environment installed.

Let me know in the comments if you find it useful, or any suggestions for improvements.

(This is only an initial version of this tool. Selva is still working to introduce much more features).

t2

Click on the “Load” button to open any cobol file that you have downloaded from mainframe. The tree structure (Code flow) present in the right side, will list all the sections / paragraphs present in the code. You can double click any section in the tree to locate that part within the code. You can use ‘Ctrl + F’ shortcut to find any part code, and use F5 to reach next occurrences of the found word.

Download

Easytrieve – check if value IS NUMERIC in an alpha variable

Similar to how we have the ‘IS NUMERIC’ verb in COBOL, we can validate if a Alphanumeric variable contains Numeric value or not in Easytrieve.

Sample program:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
 DEFINE WS-ALPHA W 6 A
 JOB INPUT NULL
* ----- NOT NUMERIC --------
 WS-ALPHA = 'AB1234'
 IF WS-ALPHA NUMERIC
    DISPLAY WS-ALPHA ' IS NUMERIC'
 ELSE
    DISPLAY WS-ALPHA ' IS NOT NUMERIC'
 END-IF
* ----- NUMERIC --------
 WS-ALPHA = '123456'
 IF WS-ALPHA NUMERIC
    DISPLAY WS-ALPHA ' IS NUMERIC'
 ELSE
    DISPLAY WS-ALPHA ' IS NOT NUMERIC'
 END-IF
* ----- NOT NUMERIC --------
 WS-ALPHA = '123.45'
 IF WS-ALPHA NUMERIC
    DISPLAY WS-ALPHA ' IS NUMERIC'
 ELSE
    DISPLAY WS-ALPHA ' IS NOT NUMERIC'
 END-IF
* ----- NOT NUMERIC --------
 WS-ALPHA = '  1234'
 IF WS-ALPHA NUMERIC
    DISPLAY WS-ALPHA ' IS NUMERIC'
 ELSE
    DISPLAY WS-ALPHA ' IS NOT NUMERIC'
 END-IF
 STOP