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;