Formatting Query Results

--- The Print Table Procedure from Tom Kyte:
--- Reference: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1035431863958

Using this you can have the SPOOL vertically instead of the standard way... 

1.) Compile the procedure:

create or replace procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
execute immediate
'alter session set
nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
/

2.) Example to use:

[oracle@hqsdboitt01bt / SID : kdbpp]$ sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 18 12:02:43 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SYS @  > set serveroutput on
SYS @  > exec print_table('select * from v$instance');
INSTANCE_NUMBER               : 1
INSTANCE_NAME                 : kdbpp
HOST_NAME                     : hqsdboitt01bt.kclx.kneip.com
VERSION                       : 11.2.0.4.0
STARTUP_TIME                  : 16-nov-2016 18:28:11
STATUS                        : OPEN
PARALLEL                      : NO
THREAD#                       : 1
ARCHIVER                      : STOPPED
LOG_SWITCH_WAIT               :
LOGINS                        : ALLOWED
SHUTDOWN_PENDING              : NO
DATABASE_STATUS               : ACTIVE
INSTANCE_ROLE                 : PRIMARY_INSTANCE
ACTIVE_STATE                  : NORMAL
BLOCKED                       : NO
-----------------

PL/SQL procedure successfully completed.

Flashback Database – Enable/Disable

Hi, this is the quick one, sometimes we forget the basic stuffs, so …
https://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV582

--- Enable Flashback Database:

select FLASHBACK_ON from v$database;

1.) Choose the DIRECTORY:
/u01/oradata/KDBPP/FRA

2.) SHUT Immediate;

3.) Startup mount;

4.) Enable Archivelog Mode, if needed

alter database archivelog;

5.) Define the FRA - Size:
alter system set db_recovery_file_dest_size=50g scope=both;

6.) Define the FRA - Directory:
alter system set db_recovery_file_dest='/u01/oradata/KDBPP/FRA' scope=both;

7.) Enable FLASHBACK on DATABASE:
alter database flashback on;

8.) Open database:
alter database open;

-- Disable:
1.) Shutdown Database;
shut immediate;

2.) Startup mount;

3.) alter database flashback off;

4.) If needed, disable the Archivelog
alter database noarchivelog;

5.) alter database open;

select FLASHBACK_ON from v$database;

--------------------------------------------------------------------
Note:

If you face this issue (ORA-38781: cannot disable media recovery - 
have guaranteed restore points) trying put back in NOARCHIVELOG mode.

You need drop the active restore point before, so :

a.) SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
        GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
        FROM V$RESTORE_POINT; 

b.) drop restore point <RESTORE POINT NAME>;

c.) if you are in MOUNT state you can execute:
alter database noarchivelog;