OracleApps/DB/OS Administration Solutions
Tuesday 17 October 2023
SQL Developer displaying Junk characters (??????)
Monday 16 October 2023
EBS : SQL to find SID from Concurrent Request ID
Sunday 18 June 2023
Oracle: Purge UINIFIED AUDIT TRAIL
1. Get the count from audsys table:
select count(1) from audsys.aud$unified;
2. Get the count from unified_audit_trail view:
select count(*) from unified_audit_trail;
3. Get the list of unified audit polices with action name and count
select unified_audit_policies,action_name,count(*) from unified_audit_trail group by unified_audit_policies,action_name;
4. Set the Timestamp that to be deleted as here it is set to 10 days before SYSDATE
begin
dbms_audit_mgmt.set_last_archive_timestamp(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,last_archive_time=>sysdate-10);
end;
/
5. Execute the below PL/SQL block to delete the UNIFIED_AUDIT_TRAIL to the timestamp set above
set serveroutput on;
begin
DBMS_OUTPUT.PUT_LINE('Time Started: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified
,use_last_arch_timestamp=>TRUE);
DBMS_OUTPUT.PUT_LINE('Time Ended: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
end;
/
6. Execute the below PL/SQL block to delete All UNIFIED_AUDIT_TRAIL
set serveroutput on;
begin
DBMS_OUTPUT.PUT_LINE('Time Started: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => FALSE);
DBMS_OUTPUT.PUT_LINE('Time Ended: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
end;
/
Sunday 26 December 2021
MySQL: Fulsh hosts in the MySQL database
Solution:
Execute the below command to flush the connections
mysqladmin -u root -h hostname -P 3351 -p flush-hosts
Tuesday 7 December 2021
DB: Upload or Download a file from Oracle Table
1. Create a directory "DB_Files_test" under D:\SURESH\03_Working\ in Windows
2. Connect as SYSDBA and create a directory as DB_FILES
SQL> CREATE OR REPLACE DIRECTORY DB_FILES As 'D:\SURESH\03_Working\DB_Files_test';
Directory created.
--Grant read, write access on the directory to the user which you want to perform upload & download file
SQL> grant read, write on directory db_files to test;
Grant succeeded.
3. Create a table POC_FILE_STORAGE to store the files as BLOB
CREATE TABLE POC_FILE_STORAGE (
file_name VARCHAR2 (1000),
file_content BLOB)
/
4. Create the below procedure to convert a file to BLOB
CREATE OR REPLACE FUNCTION FILE_TO_BLOB(p_file_name VARCHAR2) RETURN BLOB AS
destination_location BLOB := empty_blob();
source_location BFILE := BFILENAME('DB_FILES', p_file_name);
BEGIN
DBMS_LOB.OPEN(source_location, DBMS_LOB.LOB_READONLY);
DBMS_LOB.CREATETEMPORARY(
lob_loc => destination_location
, cache => true
, dur => dbms_lob.session);
DBMS_LOB.OPEN(destination_location, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(
dest_lob => destination_location
, src_lob => source_location
, amount => DBMS_LOB.getLength(source_location));
DBMS_LOB.CLOSE(destination_location);
DBMS_LOB.CLOSE(source_location);
RETURN destination_location;
END FILE_TO_BLOB;
/
5. Use Below PL/SQL block to store the file in the POC_FILE_STORAGE tables
DECLARE
v_blob BLOB;
BEGIN
v_blob := FILE_TO_BLOB ('OCA.pdf');
INSERT INTO POC_FILE_STORAGE VALUES ('OCA.pdf', v_blob);
COMMIT;
END;
/
6. Use Below SQL to list the records in the table POC_FILE_STORAGE table
SQL> col file_name for a20
SQL> set lines 200
SQL> select * from POC_FILE_STORAGE
FILE_NAME FILE_CONTENT
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
OCA.pdf 255044462D312E360D25E2E3CFD30D0A363935342030206F626A0D3C3C2F46696C7465722F466C6174654465636F64652F466972737420313733372F4C656E67746820343933382F4E203136372F5479
7. Create the below procedure to convert BLOB to a file:
CREATE OR REPLACE PROCEDURE blob_to_file (i_dir IN VARCHAR2,
i_file IN VARCHAR2,
i_blob IN BLOB)
AS
l_file UTL_FILE.file_type;
l_buffer RAW (32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
BEGIN
l_blob_len := DBMS_LOB.getlength (i_blob);
l_file :=
UTL_FILE.fopen (i_dir,i_file,'WB',32767);
WHILE l_pos < l_blob_len
LOOP
DBMS_LOB.read (i_blob,
l_amount,
l_pos,
l_buffer);
UTL_FILE.put_raw (l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
UTL_FILE.fclose (l_file);
EXCEPTION
WHEN OTHERS
THEN
IF UTL_FILE.is_open (l_file)
THEN
UTL_FILE.fclose (l_file);
END IF;
RAISE;
END blob_to_file;
/
8. Use Below PL/SQL Block to download the file
DECLARE
v_blob BLOB;
BEGIN
select FILE_CONTENT into v_blob from POC_FILE_STORAGE where FILE_NAME='OCA.pdf';
blob_to_file ('DB_FILES', 'OCA.pdf', v_blob);
END;
/
Monday 22 November 2021
Oracle : Display Arabic Character in the Linux Terminal sqlplus Prompt
Set the NLS_LANG parameter and connect as sqlplus and execute the command.
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
sqlplus / as sysdba
<your sql select statement>
Tuesday 7 July 2020
locate: command not found
SQL Developer displaying Junk characters (??????)
Change the Encoding in SQL Developer Preferences as below Tools --> Preferences --> Environment --> Encoding --> UTF...
-
Solution: SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s WHE...
-
$FND_TOP/sql/afsvcpup.sql Example: SQL> @$FND_TOP/sql/afsvcpup.sql Component Id Component Name Component Status Typ...
-
Error: [AutoConfig Error Report] The following report lists errors AutoConfig encountered during each phase of its execution. Errors a...