Oracle : Database Startup Time

 Oracle Instance Startup Time

SET LINES 2000
SET PAGES 9999
COLUMN INSTANCE_NAME FOR A20
SELECT
    instance_name,
    to_char(startup_time, 'HH24:MI DD-MON-YY') "STARTUP TIME"
FROM
    v$instance;

Startup history timing of Oracle Instance

COL INSTANCE_NAME FOR A10
SELECT
    instance_name,
    to_char(startup_time, 'HH24:MI DD-MON-YY')
FROM
    dba_hist_database_instance
ORDER BY
    startup_time DESC;

Uptime of Oracle Database

SET LINE 200
COLUMN HOSTNAME FOR A60
COLUMN INSTANCE_NAME FOR A60
COLUMN START_TIME FOR A60
COLUMN UP_TIME FOR A60
SELECT
    'HOSTNAME : ' || host_name,
    'INSTANCE NAME : ' || instance_name,
    'STARTED AT : ' || to_char(startup_time, 'DD-MON-YYYY HH24:MI:SS') start_time,
    'UPTIME : '
    || floor(sysdate - startup_time)
    || ' DAYS(S) '
    || trunc(24 *((sysdate - startup_time) - trunc(sysdate - startup_time)))
    || ' HOUR(S) '
    || mod(trunc(1440 *((sysdate - startup_time) - trunc(sysdate - startup_time))), 60)
    || ' MINUTE(S) '
    || mod(trunc(86400 *((sysdate - startup_time) - trunc(sysdate - startup_time))), 60)
    || ' SECONDS'                                                      up_time
FROM
    sys.v_$instance;

SQL Developer displaying Junk characters (??????)

Change the Encoding in SQL Developer Preferences as below Tools --> Preferences --> Environment --> Encoding --> UTF-8 --> Restart the SQL Developer

After Restart we get the output as below

EBS : SQL to find SID from Concurrent Request ID

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id FROM apps.fnd_concurrent_requests a left outer join apps.FND_CONCURRENT_PROCESSES b on a.controlling_manager = b.concurrent_process_id left outer join GV$PROCESS c on c.pid = b.oracle_process_id left outer join GV$SESSION d on b.session_id=d.audsid WHERE a.request_id =&ccr_req_id

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;

/

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

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;

/

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>