Oracle create private public synonym command

How to create synonym in Oracle 

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment.  

Private Synonym:

1. Login as scheman user under where should create synonym

2.  schemaname is equal The Schema name for example. 

     TEST01

     TEST02

The user schema TEST01 should get synonym from all tables unter the schema TEST02 located.

Login as  TEST01

sqlplus TEST01

set echo off
set verify off
set pagesize 0
set trimspool on
set headsep off
set linesize 100
set heading off
set feedback off
set timing off
set time off
set termout off
set headsep off
spool create_private_synonom.sql
SELECT 'create public synonym ' || object_name || ' for TEST02.' || object_name || ' ;' FROM dba_objects
WHERE owner = 'TEST02'
AND object_type IN ('TABLE', 'SEQUENCE', 'VIEW', 'PROCEDURE', 'PACKAGE');
spool off

Verify the script before you execute. Examples line

...
create synonym EMPLOY_HR for TEST02.EMPLOY_HR ;

...

Execute the script as follow.

sqlplus TEST01

SQL> @create_private_synonom.sql

Or use simple different way.

set echo off

set feedback off; 

set heading off; 

set term off

set pagesize 1000

set linesize 100

SET PAGES 0

spool create_synonom_table.sql

select 'create synonym ' || table_name || ' for ' || owner || '.' || table_name || ';'  FROM all_tables WHERE owner='TEST02';

spool off

select 'create synonym ' ||view_name || ' for ' || owner || '.' || view_name || ';'  FROM all_views WHERE owner='TEST02';

Public Synonym:

For Public synonym we need to just add the keyword public.

SELECT 'create public synonym ' || object_name || ' for TEST02.' || object_name || ' ;' FROM dba_objects

WHERE owner = 'TEST02'

AND object_type IN ('TABLE', 'SEQUENCE', 'VIEW', 'PROCEDURE', 'PACKAGE');

Your comments are most valuable and it would help me to come up with better posts.

 

Clean the flashback recovery area

free space in flash recovery area

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

 NOTE: You can remove the file on the filesystem level.

cd /fast_recovery_area/TESTDB/archivelog
ls -al 
2022_10_07/
2022_10_08/
2022_10_09/

2022_10_10/

I am going to remove all files older then 5 hours.

find /fast_recovery_area/TESTDB/archivelog/2022*  \( -mmin +360 -a -name "*.arc" \) -exec rm {} \; > /dev/null 2>&1 

Login into DB and check the parameter:

sqlplus / as sysdbaq

col name format a32
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999

select
name,
ceil( space_limit / 1024 / 1024 /1024) size_gb,
ceil( space_used / 1024 / 1024 /1024) used_gb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by

name desc;

NAME                                  SIZE_GB      USED_GB  PCT_USED
-------------------------------- ------------  ------------ --------

/fast_recovery_area                     2,600        2,590       100

Increate the sieze as follow. 

NOTE: NO NEED DB RESART:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =3500G SCOPE=BOTH;

RMAN command required to make empty space show via sql command..

rman target /
crosscheck archivelog all;

delete noprompt force expired archivelog all;

Make empty all files older then 0ne day:

#delete noprompt archivelog all completed before 'sysdate - 1';

Or older the 6 hours:

delete noprompt archivelog all completed before 'sysdate - 6/24';

Now execute the above sql command again and check the space:

NAME                                  SIZE_MB      USED_MB PCT_USED
-------------------------------- ------------ ------------ --------

/fast_recovery_area                     3,500        2,590       74

You can use the follwing scrip to execute the RMAN commands.

delete_archivelog.sh

more delete_archivelog.sh

#!/bin/bash
date '+%Y-%m-%d  %H:%M:%S'
. /set_home_env TESTDB #set DB environment variables.
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
rman target / <<EOF
crosscheck archivelog all;
delete noprompt force expired archivelog all;
#delete noprompt archivelog all completed before 'sysdate - 1';
delete noprompt archivelog all completed before 'sysdate - 6/24';
EOF
date '+%Y-%m-%d  %H:%M:%S'
cd /fast_recovery_area/TESTDB/archivelog

find /fast_recovery_area/TESTDB/archivelog/* -type d -empty -print -exec rm -r {} \;

Your comments are most valuable and it would help me to come up with better posts.

Increase the sessions parameter in oracle

 

How to increase the sessions parameter in oracle

Increase sessions value.

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

You can check the current value as follow

sql> show parameter sessions

sql> show parameter processes

sql> show parameter transactions

Note: Required DB restart.

You can calculate the value as follow using the formula.

processes=x

sessions=x*1.1+5

transactions=sessions*1.1

Example:

 

sql> alter system set processes=500 scope=spfile;

sql> alter system set sessions=555 scope=spfile;

sql> alter system set transactions=610 scope=spfile; 

restart DB: