How to test a connection to SMTP server

CREATE OR REPLACE FUNCTION TEST_SMTP_MAIL RETURN TSTRINGS PIPELINED IS

------------------------------

-- Usage: SELECT column_value as LINE from table(TEST_SMTP_MAIL);  

------------------------------

  v_server         VARCHAR2(100);

  v_port           INTEGER;

  v_smtp           UTL_SMTP.connection;

  v_reply          UTL_SMTP.reply;

  

BEGIN

  v_server := '10.20.30.40';

  v_port   := 25;

  -- attempt to connect to mail server

  pipe row( 'connecting to server '||v_server||' on port: '||v_port||'/tcp' );

  v_reply := UTL_SMTP.open_connection( host=>v_server, port=>v_port, c=>v_smtp );

  pipe row( 'Reply Code: '||v_reply.code||'. 'Reply Text: '|| reply.text );

  

  -- if a successful connect, gracefully disconnect

  if v_reply.code < 400 then

    v_reply := UTL_SMTP.quit( smtp );

    pipe row( 'Reply Code: '||v_reply.code||'. 'Reply Text: '|| reply.text );

  end if;

END TEST_SMTP_MAIL;

/

Oracle PL/SQL send mail with attached File using UTL_SMTP

CREATE OR REPLACE PROCEDURE send_mail_with_attach_file

          (p_from        IN VARCHAR2,

           p_to          IN VARCHAR2,           

           p_subject     IN VARCHAR2,

           p_text_msg    IN VARCHAR2 DEFAULT NULL,

           p_attach_name IN VARCHAR2 DEFAULT NULL,

           p_attach_mime IN VARCHAR2 DEFAULT NULL,

           p_attach_blob IN BLOB DEFAULT NULL,

           p_directory IN VARCHAR2,

           p_file_name      IN VARCHAR2           

           )

AS

  v_mail_conn         UTL_SMTP.connection;

  v_boundary          VARCHAR2(50) := '----=abc1234321cba=';

  v_step              PLS_INTEGER  := 57;

  -------------------------------------

  --Put here real IP!

  -------------------------------------

  v_smtp_server       CONSTANT VARCHAR2(30) := '10.20.30.40'; 

  v_smtp_server_port  CONSTANT INTEGER := 25;

  

  --File Reading  

  c_max_line_width CONSTANT PLS_INTEGER DEFAULT 54;

  v_amt            BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016 */

  v_bfile          BFILE;

  v_file_length    PLS_INTEGER;

  v_buf            RAW(2100);

  v_modulo         PLS_INTEGER;

  v_pieces         PLS_INTEGER;

  v_file_pos       pls_integer := 1;

  v_to_list        VARCHAR2(1000);

  v_to             VARCHAR2(1000);

  

BEGIN

  v_mail_conn := UTL_SMTP.open_connection(v_smtp_server, v_smtp_server_port);

  UTL_SMTP.helo(v_mail_conn, v_smtp_server);

  UTL_SMTP.mail(v_mail_conn, p_from);

  

  v_to_list := p_to;

  WHILE (INSTR(v_to_list, ',') > 0) LOOP    

    --get first from list

    v_to := SUBSTR(v_to_list, 1, INSTR(v_to_list,',')-1);

    --get remaining of list

    v_to_list := SUBSTR(v_to_list, INSTR(v_to_list, ',')+1);

    --call rcpt for mail

    UTL_SMTP.rcpt(v_mail_conn, v_to);    

  END LOOP;

  --last element does not have trailing ','

  UTL_SMTP.rcpt(v_mail_conn, v_to_list);

  

  UTL_SMTP.open_data(v_mail_conn);

  UTL_SMTP.write_data(v_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'Content-Type: multipart/mixed; boundary="' || v_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  IF p_text_msg IS NOT NULL THEN

    UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, p_text_msg);

    UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

  END IF;

  IF p_attach_name IS NOT NULL THEN

    UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    --Read blob

    IF p_attach_blob IS NOT NULL THEN

      FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/v_step) LOOP

        UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, v_step, i * v_step + 1))) || UTL_TCP.crlf);

      END LOOP;

    ELSIF p_directory IS NOT NULL AND p_file_name IS NOT NULL THEN

      --Read File  

      v_bfile := BFILENAME(p_directory, p_file_name);

      -- Get the size of the file to be attached

      v_file_length := DBMS_LOB.GETLENGTH(v_bfile);

      -- Calculate the number of pieces the file will be split up into

      v_pieces := TRUNC(v_file_length / v_amt);

      -- Calculate the remainder after dividing the file into v_amt chunks

      v_modulo := MOD(v_file_length, v_amt);

      IF (v_modulo <> 0) THEN

        v_pieces := v_pieces + 1;

      END IF;

      DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);

      --FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/v_step) LOOP

      FOR i IN 1 .. v_pieces LOOP

        v_buf := NULL;

        DBMS_LOB.READ(v_bfile, v_amt, v_file_pos, v_buf);

        v_file_pos := I * v_amt + 1;

        UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(v_buf))|| UTL_TCP.crlf);

        --Exxample

        --UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, v_step, i * v_step + 1))) || UTL_TCP.crlf);

      END LOOP;

      

      DBMS_LOB.FILECLOSE(v_bfile);

      

    END IF;

  

    UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf);

  END IF;

  UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || '--' || UTL_TCP.crlf);

  UTL_SMTP.close_data(v_mail_conn);

  UTL_SMTP.quit(v_mail_conn);

END send_mail_with_attach_file;

/

SYSAUX tablespace is full with Autostats Advisor related objects

==========

Issue:
==========

SYSAUX tablespace is full with Autostats Advisor related objects

==========

Solution:

==========

Clean up old tasks

Check Current Status
col TASK_NAME format a25
col parameter_name format a35
col parameter_value format a20
set lines 120
SELECT task_name,

       parameter_name, 

       parameter_value 

 FROM DBA_ADVISOR_PARAMETERS 

WHERE task_name='AUTO_STATS_ADVISOR_TASK' 

  AND parameter_name='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME                 PARAMETER_NAME           PARAMETER_VALUE
------------------------- ------------------------ ------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE UNLIMITED

Set limit for old tasks
BEGIN
  DBMS_ADVISOR.SET_TASK_PARAMETER(

     task_name=> 'AUTO_STATS_ADVISOR_TASK',                                parameter=> 'EXECUTION_DAYS_TO_EXPIRE', 
     value => 30);
END;
/

Check Status again

SELECT task_name,

       parameter_name, 

       parameter_value 

 FROM DBA_ADVISOR_PARAMETERS 

WHERE task_name='AUTO_STATS_ADVISOR_TASK' 

  AND parameter_name='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME                 PARAMETER_NAME           PARAMETER_VALUE
------------------------- ------------------------ ------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE 30

Check oldest task date

SELECT MIN(execution_start) 

FROM DBA_ADVISOR_EXECUTIONS WHERE task_name='AUTO_STATS_ADVISOR_TASK';

MIN(EXECUTION_STAR
------------------
27-AUG-19

Purge the expired tasks. 

This step might take time

BEGIN
  PRVT_ADVISOR.delete_expired_tasks;
END;
/

Check status in DBA_ADVISOR_EXECUTIONS 

SELECT task_id, task_name, execution_name, execution_start

  FROM DBA_ADVISOR_EXECUTIONS

 WHERE task_name='AUTO_STATS_ADVISOR_TASK'

 ORDER BY execution_start;

scp backup files to backup mng server

General

On oracle server
RMAN script running at 03:00

expdp script running at 02:00

scp scripts running at 05:00 and 05:30

On backup server
Clean old backups script running at 01:00

scripts on oracle server

00 02 * * * /etc/sh/backup/export_all_instances.sh
00 03 * * * /etc/sh/backup/jobs/run_rman_backup_cron.sh igt
00 05 * * * /home/user/scripts/send_to_mng.sh
30 05 * * * /home/user/scripts/send_to_mng_exp.sh

/home/user/scripts/send_to_mng.sh

#!/bin/bash

RUN_DATE=`date +"%Y%m%d"`

DIR_PREFIX=$RUN_DATE

REMOTE_USER=user

REMOTE_SERVER=mng_server-01

REMOTE_PATH=/backup/ipn_backup/ora_online

LOCAL_DIR=/backup/ora_online/for_backup

KEEP_DAYS=10

is_active_server=`ps -ef | grep ora_pmon_igt | grep -v grep | wc -l`

if [[ $is_active_server -eq 0 ]]; then

 echo "Oracle is not running on this server" 

 exit 0

fi

cd ${LOCAL_DIR}

last_backup_dir=`ls -ltr | grep ${DIR_PREFIX} | tail -1 | awk '{print $9}'`

if [[ -d $last_backup_dir ]]; then

 echo "tar -cvf ${last_backup_dir}.tar ${last_backup_dir}"

 tar -cvf ${last_backup_dir}.tar ${last_backup_dir}

 echo "scp -p  ${LOCAL_DIR}/${last_backup_dir}.tar ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_dir}.tar"

 scp -p  ${LOCAL_DIR}/${last_backup_dir}.tar ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_dir}.tar

 rm -f ${LOCAL_DIR}/${last_backup_dir}.tar

else

 echo "No Archive Directory to Send to mng"

fi

/home/user/scripts/send_to_mng_exp.sh

#!/bin/bash

RUN_DATE=`date +"%Y%m%d"`

FILE_PREFIX=export_igt

FILE_SUFFIX=dmp

FILE_SUFFIX_LOG=log

REMOTE_USER=user

REMOTE_SERVER=mng_server-01

REMOTE_PATH=/backup/ipn_backup/exp_backup

LOCAL_DIR=/backup/ora_exp/for_backup

LOCAL_LOG_DIR=/backup/ora_exp/for_backup/old_log

cd ${LOCAL_DIR}

last_backup_file=`ls -ltr | grep ${FILE_PREFIX} | grep ${FILE_SUFFIX} | tail -1 | awk '{print $9}'`

if [[ -f $last_backup_file ]]; then 

 #echo "scp ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_file} ${LOCAL_DIR}/${last_backup_file}"

 scp ${LOCAL_DIR}/${last_backup_file} ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_file} 

else

 echo "No export dmp file to send to mng"

fi

log_files=`ls -1 ${LOCAL_DIR}/${FILE_PREFIX}*${FILE_SUFFIX_LOG} 2>/dev/null | wc -l`

if [[ $log_files -gt 0 ]]; then

 mv -f ${LOCAL_DIR}/${FILE_PREFIX}*${FILE_SUFFIX_LOG} ${LOCAL_LOG_DIR}

fi

KEEP_BACKUPS=1

cd ${LOCAL_DIR}

#echo "expdp dmp files: ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l"

backups_number=`ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l`

#echo "backups_number = $backups_number"

while [[ $backups_number -gt $KEEP_BACKUPS ]]; do

 backup_name=`ls -ltr ${FILE_PREFIX}*${FILE_SUFFIX} | head -1 | awk '{print $9}'`

 #echo "rm -f $backup_name"

 rm -f $backup_name

 backups_number=`ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l`

done

scripts on mng server

00 01 * * * /home/user/scripts/del_old_backups.sh

/home/user/scripts/del_old_backups.sh

#!/bin/bash

LOCAL_DIR=/backup/ipn_backup/ora_online

RUN_DATE=`date +"%Y%m%d"`

BACKUP_SUFFIX=tar

KEEP_BACKUPS=4

cd ${LOCAL_DIR}

backups_number=`ls -1 *${BACKUP_SUFFIX} | wc -l`

while [[ $backups_number -gt $KEEP_BACKUPS ]]; do

 backup_name=`ls -ltr | grep ${BACKUP_SUFFIX} | head -1 | awk '{print $9}'`

 rm -f $backup_name

 backups_number=`ls -1 *${BACKUP_SUFFIX} | wc -l`

done

LOCAL_DIR=/backup/ipn_backup/exp_backup

FILE_PREFIX=export_igt

BACKUP_SUFFIX=dmp

KEEP_BACKUPS=2

cd ${LOCAL_DIR}

backups_number=`ls -1 ${FILE_PREFIX}*${BACKUP_SUFFIX} | wc -l`

while [[ $backups_number -gt $KEEP_BACKUPS ]]; do

 backup_name=`ls -ltr | grep ${FILE_PREFIX} | grep ${BACKUP_SUFFIX} | head -1 | awk '{print $9}'`

 rm -f $backup_name

 backups_number=`ls -1 ${FILE_PREFIX}*${BACKUP_SUFFIX} | wc -l`

done

ALTER INDEX REBUILD ONLINE in Partitioned Index

ORA-14086: a partitioned index may not be rebuilt as a whole

SET LINESIZE 140

COL sql_cmd FOR A120

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET VERIFY OFF

spool rebuild_ix.sql

SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||PARTITION_NAME||' ONLINE;' AS sql_cmd

  FROM DBA_IND_PARTITIONS

 WHERE index_name = 'MY_INDEX';

spool off

--Same for a user

SELECT 'ALTER INDEX '||index_name||' REBUILD PARTITION '||PARTITION_NAME||' ONLINE;' AS sql_cmd
  FROM USER_IND_PARTITIONS
 WHERE index_name = 'MY_INDEX';

SQL_CMD
------------------------------------------------------------------
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P392 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P393 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P394 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P395 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P396 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P397 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P398 ONLINE;
ALTER INDEX MY_INDEX REBUILD PARTITION SYS_P399 ONLINE;

Before rebuild:

SEGMENT_NAME                   USED_MB

------------------------------ --------------

MY_INDEX                                 3567

After rebuild

SEGMENT_NAME                   USED_MB

------------------------------ --------------

MY_INDEX                                  2301     

Rebuild Tables and Indexes

Rebuild Tables and Indexes from crontab

CREATE DBA USER

CREATE USER SH_USER IDENTIFIED BY XXXXXX;

GRANT CONNECT, RESOURCE TO SH_USER;

GRANT DBA TO SH_USER;

ALTER USER SH_USER DEFAULT TABLESPACE IGT_TABLE;

crontab
20 7 * * * /software/oracle/oracle/scripts/space/rebuild_tables_and_indexes.sh

rebuild_tables_and_indexes.sh

#!/bin/bash

. ~/.bash_profile

. /etc/sh/orash/oracle_login.sh igt

ORACLE_SID=igt

ORACLE_HOME=/software/oracle/1910

WORK_DIR=/software/oracle/oracle/scripts/space/rebuild_tables_and_indexes

RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`

cd ${WORK_DIR}

echo "Start Rebuild Indexes at $RUN_DATE" >> rebuild_tables_and_indexes.log

sqlplus SH_USER/XXXXXXXX@orainst @rebuild_tables_and_indexes.sql

sqlplus SH_USER/XXXXXXXX@orainst @log_connection.sql

rebuild_tables_and_indexes.sql

----------------------

--USER_A

----------------------

ALTER TABLE USER_A.GA_W_COUNTERS_HISTORY ENABLE ROW MOVEMENT;

ALTER TABLE USER_A.GA_W_COUNTERS_HISTORY SHRINK SPACE;

ALTER TABLE USER_A.GA_W_COUNTERS_HISTORY DEALLOCATE UNUSED;

DROP INDEX USER_A.GWCH_PROCESSED_STATIC_IDX;

CREATE INDEX USER_A.GWCH_PROCESSED_STATIC_IDX ON USER_A.GA_W_COUNTERS_HISTORY (is_processed, static_id) TABLESPACE IGT_INDEX;

DROP INDEX USER_A.GWCH_TS_LAST_MODIFIED_IDX;

CREATE INDEX USER_A.GWCH_TS_LAST_MODIFIED_IDX ON USER_A.GA_W_COUNTERS_HISTORY (ts_last_modified) TABLESPACE IGT_INDEX;

----------------------

--USER_B

----------------------

ALTER TABLE USER_B.GA_W_COUNTERS_HISTORY ENABLE ROW MOVEMENT;

ALTER TABLE USER_B.GA_W_COUNTERS_HISTORY SHRINK SPACE;

ALTER TABLE USER_B.GA_W_COUNTERS_HISTORY DEALLOCATE UNUSED;

DROP INDEX USER_B.GWCH_PROCESSED_STATIC_IDX;

CREATE INDEX USER_B.GWCH_PROCESSED_STATIC_IDX ON USER_B.GA_W_COUNTERS_HISTORY (is_processed, static_id) TABLESPACE IGT_INDEX;

DROP INDEX USER_B.GWCH_TS_LAST_MODIFIED_IDX;

CREATE INDEX USER_B.GWCH_TS_LAST_MODIFIED_IDX ON USER_B.GA_W_COUNTERS_HISTORY (ts_last_modified) TABLESPACE IGT_INDEX;

log_connection.sql

SET LINESIZE 140

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET VERIFY OFF

SET ECHO OFF

SET NEWPAGE NONE

SET SHOW OFF

SET TRIMSPOOL ON

spool log_connection.log APPEND

SELECT '=======================' FROM DUAL;

SELECT 'Start Rebuild Tables and Indexes at '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') FROM DUAL;

SELECT '    ' FROM DUAL;

spool off

exit;

See objects in RECYCLEBIN and Purge RECYCLEBIN

See Recyclebin

COL owner FOR A30

COL original_name FOR A30

COL object_name FOR A30

SELECT owner, original_name, object_name, type, droptime

FROM DBA_RECYCLEBIN;

Purge a specific table PURGE TABLE RB$$33750$TABLE$0; 

Purge schema Recyclebin

PURGE RECYCLEBIN;

Purge all Recyclebin

PURGE DBA_RECYCLEBIN;

How To Configure Data Encryption and Integrity

How To Configure Data Encryption and Integrity

Reference Link 1 

Reference Link 2

Network encryption is of prime importance to you if your databases is on the cloud.

Oracle Advanced Security native Oracle Net Services encryption and integrity.

Encryption and integrity parameters are defined by modifying a sqlnet.ora file on the clients and the servers on the network.

The profile on client and server systems using data encryption and integrity must contain some or all of the parameters listed below.

About Activating Encryption and Integrity

In any network connection, it is possible for both the client and server to support more than one encryption algorithm and more than one integrity algorithm. When a connection is made, the server selects which algorithm to use, if any, from those algorithms specified in the sqlnet.ora files.

The server searches for a match between the algorithms available on both the client and the server, and picks the first algorithm in its own list that also appears in the client list.
If one side of the connection does not specify an algorithm list, all the algorithms installed on that side are acceptable.
The connection fails with error message ORA-12650 if either side specifies an algorithm that is not installed.

You can choose to configure any or all of the available Oracle Advanced Security encryption algorithms (Table 9-3), and the available integrity algorithm (SHA-1).
Only one encryption algorithm and one integrity algorithm are used for each connect session.

About Negotiating Encryption and Integrity

To negotiate whether to turn on encryption or integrity, you can specify four possible values for the Oracle Advanced Security encryption and integrity configuration parameters.
The four values are listed in the order of increasing security.

REJECTED provides the minimum amount of security between client and server communications,

ACCEPTED - Default. - Accepts, if possible, whatever encryption the other side is sending.

REQUESTED - "Preferred" method of Encryption, but not mandatory.

REQUIRED  - Mandatory method of Encryption. Provides the maximum amount of network security:

Oracle Database servers and clients are set to ACCEPT encrypted connections out of the box. This means that you can enable the desired encryption and integrity settings for a connection pair by configuring just one side of the connection, server-side or client-side.

So, for example, if there are many Oracle clients connecting to an Oracle database, you can configure the required encryption and integrity settings for all these connections by making the appropriate sqlnet.ora changes at the server end.
You do not need to implement configuration changes for each client separately.

REJECTED

Select this value if you do not elect to enable the security service, even if required by the other side.

In this scenario, this side of the connection specifies that the security service is not permitted. If the other side is set to REQUIRED, the connection terminates with error message ORA-12650. If the other side is set to REQUESTED, ACCEPTED, or REJECTED, the connection continues without error and without the security service enabled.

ACCEPTED

Select this value to enable the security service if required or requested by the other side.

In this scenario, this side of the connection does not require the security service, but it is enabled if the other side is set to REQUIRED or REQUESTED.
If the other side is set to REQUIRED or REQUESTED, and an encryption or integrity algorithm match is found, the connection continues without error and with the security service enabled.
If the other side is set to REQUIRED and no algorithm match is found, the connection terminates with error message ORA-12650.

If the other side is set to REQUESTED and no algorithm match is found, or if the other side is set to ACCEPTED or REJECTED, the connection continues without error and without the security service enabled.

REQUESTED

Select this value to enable the security service if the other side permits it.

In this scenario, this side of the connection specifies that the security service is desired but not required.
The security service is enabled if the other side specifies ACCEPTED, REQUESTED, or REQUIRED.
There must be a matching algorithm available on the other side, otherwise the service is not enabled.
If the other side specifies REQUIRED and there is no matching algorithm, the connection fails.

REQUIRED

Select this value to enable the security service or preclude the connection.

In this scenario, this side of the connection specifies that the security service must be enabled. The connection fails if the other side specifies REJECTED or if there is no compatible algorithm on the other side.

Table 9-2 shows whether the security service is enabled, based on a combination of client and server configuration parameters.
If either the server or client has specified REQUIRED, the lack of a common algorithm causes the connection to fail.
Otherwise, if the service is enabled, lack of a common service algorithm results in the service being disabled.

Configuration

Configuration is done in sqlnet.ora.

Since server can also make client call - on client side need specify both

On server:

SQLNET.ENCRYPTION_SERVER = [accepted | rejected | requested | required]

SQLNET.ENCRYPTION_TYPES_SERVER = (valid_encryption_algorithm [,valid_encryption_algorithm])

On client:

SQLNET.ENCRYPTION_CLIENT = [accepted | rejected | requested | required]

SQLNET.ENCRYPTION_TYPES_CLIENT = (valid_encryption_algorithm [,valid_encryption_algorithm])

For Example:

Client side "sqlnet.ora" file.

To force encryption from a client, while not affecting any other connections to the server, we would add the following to the client "sqlnet.ora" file.
The server does not need to be altered as the default settings (ACCEPTED and no named encryption algorithm) will allow it to successfully negotiate a connection.

SQLNET.ENCRYPTION_CLIENT=REQUIRED

SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

Server side "sqlnet.ora" file.

If we would prefer clients to use encrypted connections to the server, but will accept non-encrypted connections, we would add the following to the server side "sqlnet.ora".

SQLNET.ENCRYPTION_SERVER=REQUESTED

SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

Other options:

SQLNET.ENCRYPTION_CLIENT = REQUESTED

SQLNET.ENCRYPTION_TYPES_CLIENT= (AES256, AES192, AES128)

SQLNET.ENCRYPTION_SERVER = REQUESTED

SQLNET.ENCRYPTION_TYPES_SERVER= (AES256, AES192, AES128)

In this example:
REQUESTED : The client or server will request encrypted traffic if it is possible, but will accept non-encrypted traffic if encryption is not possible.

Data Integrity

The advanced security data integrity functionality is separate to network encryption, but it is often discussed in the same context and in the same sections of the manuals.
The configuration is similar to that of network encryption, using the following parameters in the server and/or client "sqlnet.ora" files.

# Server

SQLNET.CRYPTO_CHECKSUM_SERVER

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER

# Client

SQLNET.CRYPTO_CHECKSUM_CLIENT

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT

The SQLNET.CRYPTO_CHECKSUM_[SERVER|CLIENT] parameters have the same allowed values as the SQLNET.ENCRYPTION_[SERVER|CLIENT] parameters, with the same style of negotiations.

The SQLNET.CRYPTO_CHECKSUM_TYPES_[SERVER|CLIENT] parameters only accepts the SHA1 value prior to 12c. 

From 12c onward they also accept MD5, SHA1, SHA256, SHA384 and SHA512, with SHA256 being the default.

cleanup PERFSTAT tables

How to cleanup PERFSTAT tables

PERFSTAT tables grow in size over time

OWNER           TABLESPACE_NAME  SEGMENT_NAME             USED_MB
--------------- ---------------- ------------------------ -------PERFSTAT        WORKAREA         STATS$SQL_SUMMARY           1664
PERFSTAT        WORKAREA         STATS$SYSSTAT_PK             888
PERFSTAT        WORKAREA         STATS$SQL_SUMMARY_PK         664
PERFSTAT        WORKAREA         STATS$SYSSTAT                608
PERFSTAT        WORKAREA         STATS$EVENT_HISTOGRAM_PK     416
PERFSTAT        WORKAREA         STATS$LATCH                  360
PERFSTAT        WORKAREA         STATS$LATCH_PK               328

PERFSTAT        WORKAREA         STATS$EVENT_HISTOGRAM        272
PERFSTAT        WORKAREA         STATS$PARAMETER_PK           184
PERFSTAT        WORKAREA         STATS$SQLTEXT                176

sqlplus perfstat/perfstat@igt

To make the perfstat job to run every hour:

$ORACLE_HOME/rdbms/admin/spauto.sql

To query current status:

SELECT MIN(SNAP_TIME) FROM stats$snapshot;

MIN(SNAP_TIME)

------------------

11-JAN-24

SELECT SYSDATE FROM DUAL;

SYSDATE

------------------

11-AUG-24

To manually truncate perfstat tables

$ORACLE_HOME/rdbms/admin/sptrunc.sql

DESCRIPTION:    Truncates data in Statspack tables

SQL> @$ORACLE_HOME/rdbms/admin/sptrunc.sql

Warning

~~~~~~~

Running sptrunc.sql removes ALL data from Statspack tables.  You may

wish to export the data before continuing.

About to Truncate Statspack Tables

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If would like to exit WITHOUT truncating the tables, enter any text at the

begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin

the truncate operation, press <return>

Enter value for begin_or_exit:

Table truncated.

Table truncated.

5108 rows deleted.

1 row deleted.

Commit complete.

Package altered.

... Truncate operation complete

SQL>

Per Oracle Technote How To Automate Purging of Statspack Snapshots (Doc ID 464214.1)

CREATE OR REPLACE PACKAGE SPPURPKG

IS

  PROCEDURE purge(in_days_older_than IN INTEGER);

END SPPURPKG;

/

CREATE OR REPLACE PACKAGE BODY SPPURPKG

IS

  PROCEDURE purge(in_days_older_than IN INTEGER)IS

    CURSOR get_snaps(in_days IN INTEGER) IS

      SELECT s.rowid,

s.snap_id,

s.dbid,

s.instance_number

    FROM stats$snapshot s,

sys.v_$database d,

sys.v_$instance i

       WHERE s.dbid = d.dbid

         AND s.instance_number = i.instance_number

         AND s.snap_time < TRUNC(SYSDATE) - in_days;

 

errcontext VARCHAR2(100);

errmsg VARCHAR2(1000);

save_module VARCHAR2(48);

save_action VARCHAR2(32);

  BEGIN

    errcontext := 'save settings of DBMS_APPLICATION_INFO';

dbms_application_info.read_module(save_module, save_action);

dbms_application_info.set_module('SPPURPKG.PURGE', 'begin');

errcontext := 'open/fetch get_snaps';

dbms_application_info.set_action(errcontext);

FOR x IN get_snaps(in_days_older_than) LOOP

    errcontext := 'delete (cascade) STATS$SNAPSHOT';

  dbms_application_info.set_action(errcontext);

  DELETE FROM stats$snapshot

       WHERE ROWID = x.rowid;

      errcontext := 'delete "dangling" STATS$SQLTEXT rows';

      dbms_application_info.set_action(errcontext);

  

      DELETE FROM stats$sqltext

       WHERE (old_hash_value, text_subset) not in

               (SELECT /*+ hash_aj (ss) */ old_hash_value, text_subset

                  FROM stats$sql_summary ss

               );

      errcontext := 'delete "dangling" STATS$DATABASE_INSTANCE rows';

      dbms_application_info.set_action(errcontext);

  DELETE FROM stats$database_instance i

       WHERE i.instance_number = x.instance_number

         AND i.dbid = x.dbid

         AND NOT EXISTS

                    (SELECT 1

                       FROM stats$snapshot s

                      WHERE s.dbid = i.dbid

                        AND s.instance_number = i.instance_number

                        AND s.startup_time = i.startup_time

                    );

      errcontext := 'delete "dangling" STATS$STATSPACK_PARAMETER rows';

      dbms_application_info.set_action(errcontext);

  

      DELETE FROM stats$statspack_parameter p

       WHERE p.instance_number = x.instance_number

         AND p.dbid = x.dbid

         AND NOT EXISTS

                    (SELECT 1

                       FROM stats$snapshot s

                      WHERE s.dbid = p.dbid

                        AND s.instance_number = p.instance_number

                    );

      errcontext := 'fetch/close get_snaps';

      dbms_application_info.set_action(errcontext);

    END LOOP;

commit;

    errcontext := 'restore saved settings of DBMS_APPLICATION_INFO';

    dbms_application_info.set_module(save_module, save_action);

  EXCEPTION

    WHEN OTHERS THEN

  rollback;

      errmsg := sqlerrm;

      dbms_application_info.set_module(save_module, save_action);

      raise_application_error(-20000, errcontext || ': ' || errmsg);

  END purge;

END SPPURPKG;

/

--Create a job to delete data older than 10 days

BEGIN

 DBMS_JOB.submit(v_job_number, 

    WHAT => 'SPPURPKG.purge(10);',

    NEXT_DATE => TRUNC(SYSDATE) +1 + 0/24 + 15/1440,

    INTERVAL  => 'TRUNC(SYSDATE) +1 + 0/24 + 15/1440'); 

 COMMIT;

END;

/

--Execute the job

BEGIN

  SPPURPKG.purge(10);

  commit;

END;

/

--See jobs under perfstat user

COL WHAT FOR A30

COL INTERVAL FOR A40

SELECT what, interval FROM USER_JOBS;

 WHAT                           INTERVAL

------------------------------ ----------------------------------

SPPURPKG.purge(10);            TRUNC(SYSDATE) +1 + 0/24 + 15/1440

statspack.snap;                trunc(SYSDATE+1/24,'HH')

grant change notification permissions. And trc files...

=======================

Oracle server Permissions

=======================

What to check

Reference

SQL> show parameter statistics

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------------

client_statistics_level              string      TYPICAL

optimizer_adaptive_statistics        boolean     FALSE

optimizer_real_time_statistics       boolean     FALSE

optimizer_use_pending_statistics     boolean     FALSE

statistics_level                     string      BASIC

timed_os_statistics                  integer     0

timed_statistics                     boolean     FALSE 

statistics_level should be set to TYPICAL
timed_statistics should be set to TRUE

statistics_level

TYPICAL  - Is the default 

BASIC  - disables the collection of many of the important statistics

Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

timed_statistics

Specifies whether statistics related to time are collected.

=======================

Schema Permissions

=======================

What to check

col GRANTEE for A30
col PRIVILEGE for A30
col ADMIN_OPTION for A30

SELECT grantee, privilege, admin_option, common, inherited
  FROM DBA_SYS_PRIVS
 WHERE privilege = 'CHANGE NOTIFICATION' 

   AND grantee = 'XXX';

sqlplus / as sysdba
grant change notification to vipcreator with admin option;
exit;

sqlplus vipcreator/xxxxxxx@orainst
SELECT 'grant change notification to '||USERNAME||';'
FROM ALL_USERS
WHERE ORACLE_MAINTAINED = 'N';

Execute the sqls in output 
GRANT CHANGE NOTIFICATION TO USERNAME;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO XXX;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO USERNAME;

Check Change Notification Permission:

SELECT grantee, privilege, admin_option, common, inherited
  FROM DBA_SYS_PRIVS
 WHERE privilege = 'CHANGE NOTIFICATION' 

   AND grantee = 'USERNAME';

Once there is notification, data will go into table DBA_CHANGE_NOTIFICATION_REGS

Table Level

See if monitoring is enabled
SELECT monitoring, count(1) 

  FROM USER_TABLES 

 GROUP BY MONITORING;

See if monitoring is logged to USER_TAB_MODIFICATIONS 

COL TABLE_OWNER FOR A30

COL TABLE_NAME FOR A30

SELECT table_owner, table_name, timestamp 

  FROM DBA_TAB_MODIFICATIONS

ORDER BY table_owner, table_name;

Flash writes to USER_TAB_MODIFICATIONS 

BEGIN

 DBMS_STATS.flush_database_monitoring_info;

END;

/

After flush, data should be in USER_TAB_MODIFICATIONS 

SELECT table_name, timestamp 

  FROM USER_TAB_MODIFICATIONS 

 WHERE table_name = 'SGA_W_LOG';

trc files under trace folder

Multiple trc file, with same text are generated under trace folder, with text like:

kpondGetCQNRegId(): subname: CHNF29418 regid 29418

kpondGetCQNRegId(): subname: CHNF29416 regid 29416

kpondGetCQNRegId(): subname: CHNF29414 regid 29414

kpondGetCQNRegId(): subname: CHNF29412 regid 29412

set linesize 400
col username for A24

col table_name FOR A40

col callback for A80

SELECT regid username, table_name, regflags, callback

  FROM DBA_CHANGE_NOTIFICATION_REGS

 WHERE regid iN (29412, 29414, 29416, 29418);

 

REGID USERNAME        TABLE_NAME                                 REGFLAGS 

----- --------------- ---------------------------------------- ---------- 

CALLBACK

-------------------------------------------------------------------

29412 VRS_IPNQQ_0001A VRS_IPNQQ_0001A.FIVEG_REGISTRATION                4 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.106.52)(PORT=47633))?PR=0

29414 VRS_IPNQQ_0001A VRS_IPNQQ_0001A.FIVEG_REGISTRATION                4 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.106.53)(PORT=47632))?PR=0

29416 VRS_IPNQQ_0001A VRS_IPNQQ_0001A.FIVEG_REGISTRATION                4 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.106.53)(PORT=47633))?PR=0

29418 VRS_IPNQQ_0001A VRS_IPNQQ_0001A.FIVEG_REGISTRATION                4 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.106.52)(PORT=47632))?PR=0

SELECT table_name, count(*) 

  FROM DBA_CHANGE_NOTIFICATION_REGS 

GROUP BY table_name;

 

TABLE_NAME                                                     COUNT(*)

------------------------------------------------------------ ----------

SCHEMA_NAME.IPN_TRIGGER_NOT_REPLICATED                           32

SCHEMA_NAME.GA_PROPERTY_VALUES_DEF                                4

SCHEMA_NAME.SHM_SERVICES                                         22

SCHEMA_NAME.IPN_INVALIDATE_TRIGGER                               12

SCHEMA_NAME.IPN_SOD_JOBS                                          2

SCHEMA_NAME.FIVEG_REGISTRATION                                    4

SCHEMA_NAME.SHM_SERVICES_ACTIVE_SITE                             12

SCHEMA_NAME.SHM_SERVICE_PROCESSES                                22

SCHEMA_NAME.GA_PROPERTIES_CONF                                    4

SCHEMA_NAME.GA_PROPERTY_VALUES_CONF                               4

SCHEMA_NAME.CGA_ITEMS                                             4

SCHEMA_NAME.GA_PRODUCT_TREE_CONF                                  1

SCHEMA_NAME.IPN_SOD_JOBS_TRIGGER                                 10

SCHEMA_NAME.GA_PROPERTIES_DEF                                     4

SCHEMA_NAME.REP_COUNTERS                                         12

SCHEMA_NAME.IPN_IOT_OTA_SEG_JOBS                                  2

 

Change Initial and Next extend size for partitioned table

Change Initial and Next extend size for partitioned table.

When changing the next and initial extend size for a Partitioned / Sub Partitioned Table, the change should be done on the Extend level

BUT!!!
For locally managed tablespaces, Oracle Database uses INITIAL , NEXT , PCTINCREASE , and MINEXTENTS to compute how many extents are allocated when the object is first created. After object creation, these parameters are ignored.
The only way to change exiting table, is to recreate it with the values for INITIAL and NEXT

For Example:

At Table level:
For future extends:
ALTER TABLE USER_A.TABLE MODIFY DEFAULT ATTRIBUTES STORAGE (INITIAL 81920 NEXT 65536);

At Partition level:
Modify Next extends on existing Partitions:
ALTER TABLE USER_A.TABLE MODIFY PARTITION P_DEFAULT STORAGE (NEXT 65536) ;

At SubPartition level: 

This should have rebuild the Sub Partition and change the initial extend. 

But it did not work...

ALTER TABLE USER_A.TABLE MOVE SUBPARTITION P_DEFAULT__S_20210904 ONLINE UPDATE INDEXES;

Need to recreate the table with correct initial and next extends, and populate data from backup table.

SELECT segment_name, 

       partition_name, 

       segment_type, 

       blocks, 

       initial_extent, 

       next_extent 

  FROM USER_SEGMENTS 

 WHERE segment_name= 'REP_DAILY_DNORM';

create table TABLE

(

  affiliate_id     NUMBER(10),

  date_of_record   VARCHAR2(8)

... 

...

)

partition by list (AFFILIATE_ID)

subpartition by range (DATE_OF_RECORD)

(

  partition P_DEFAULT values (0) tablespace IGT_REP_TABLE

  storage (initial 80K next 64K)

  (

    subpartition S_DEFAULT values less than ('0') tablespace IGT_REP_TABLE

  ),

  partition P_54 values (54) tablespace IGT_REP_TABLE

  storage (initial 80K next 64K)

(

    subpartition P_54_S_DEFAULT values less than ('0') tablespace IGT_REP_TABLE

  ),

  partition P_606 values (606) tablespace IGT_REP_TABLE

  storage (initial 80K next 64K)  

(

    subpartition P_606_S_DEFAULT values less than ('0') tablespace IGT_REP_TABLE,

    subpartition P_606_S_20240524 values less than ('20240525') tablespace IGT_REP_TABLE,

    subpartition P_606_S_20240525 values less than ('20240526') tablespace IGT_REP_TABLE,

    subpartition P_606_S_20240526 values less than ('20240527') tablespace IGT_REP_TABLE,

    subpartition P_606_S_20240527 values less than ('20240528') tablespace IGT_REP_TABLE,

    subpartition P_606_S_20240528 values less than ('20240529') tablespace IGT_REP_TABLE,

    subpartition P_606_S_20240529 values less than ('20240530') tablespace IGT_REP_TABLE,

    subpartition P_606_S_20240530 values less than ('20240531') tablespace IGT_REP_TABLE,

    subpartition P_606_S_20240531 values less than ('20240601') tablespace IGT_REP_TABLE,

    subpartition P_606_S_20240601 values less than ('20240602') tablespace IGT_REP_TABLE,

    subpartition P_606_S_20240602 values less than ('20240603') tablespace IGT_REP_TABLE,

    subpartition P_606_S_20240723 values less than ('20240724') tablespace IGT_REP_TABLE

...

...

  )

);