PLAN_TABLE_OUTPUT User has no SELECT privilege on V$SESSION

SQL> show user
USER is "SCOTT"


select * from table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION



*****

as sys or system 
grant below to scott !!

GRANT SELECT ON v_$session TO scott ;
GRANT SELECT ON v_$sql_plan_statistics_all TO scott ;
GRANT SELECT ON v_$sql_plan TO scott ;
GRANT SELECT ON v_$sql TO scott ;



===




select /*+ domtest */ count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  45sygvgu8ccnz, child number 0
-------------------------------------
select /*+ domtest */ count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49999 |  1464K|   273   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)


19 rows selected.

How to Enable Oracle Change Tracking (BCT)?

How to Enable Oracle Change Tracking (BCT)? ===

 

SHOW PARAMETER DB_CREATE_FILE_DEST   File will create here 
 

--To Enable : 
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING ;
 
--To check 
col filename for a70
select filename,status from v$block_change_tracking;
 
====
--to disable:
alter database disable block change tracking; 
 
--To check
col filename for a70
select filename,status from v$block_change_tracking;


or

set linesize 400
col FILENAME for a70
col NAME for a15
col BCT_STATUS for a15
col MB for 99999.99
select inst_id, dbid, name, db_unique_name, open_mode, log_mode, flashback_on, switchover_status, database_role,CONTROLFILE_TYPE,filename, BCT_STATUS, bytes/1024/1024 MB
             from (select inst_id, dbid, name, db_unique_name, open_mode, log_mode, flashback_on, switchover_status, database_role,CONTROLFILE_TYPE  from gv$database),
                  (select filename, status BCT_STATUS, bytes    from   v$block_change_tracking)
;

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

To enable !!!!

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> Select filename,status from V$block_change_tracking;

FILENAME
--------------------------------------------------------------------------------
STATUS
----------

DISABLED


SQL> SHOW PARAMETER DB_CREATE_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA01
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING ;

Database altered.

SQL> col filename for a70
select filename,status from v$block_change_tracking;SQL>

FILENAME
----------------------------------------------------------------------
STATUS
----------
+DATA01/T24TRMPC_LON3A/CHANGETRACKING/ctf.634.1212479513
ENABLED

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

[oracle@wcp12cr2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Dec 1 05:41:46 2023

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<




https://anuj-singh.blogspot.com/2021/10/rman-restore-database.html

Check the health of datafile !!! :)

set linesize 300 pagesize 100
col inst_id                      for 9999999                 heading 'Instance #'
col file_nr                      for 9999999                 heading 'File #'
col file_name                    for A70                     heading 'File name'
col checkpoint_change_nr         for 99999999999999          heading 'Checkpoint #'
col checkpoint_change_time       for A20                     heading 'Checkpoint time'
col last_change_nr               for 99999999999999          heading 'Last change #'
col SCNStatus for a15
SELECT
 fe.inst_id,
fe.CON_ID,  ---- for >12c
 fe.fenum file_nr,
 fn.fnnam file_name,
 TO_NUMBER (fe.fecps) checkpoint_change_nr,
 fe.fecpt checkpoint_change_time,
fe.fests last_change_nr,
NVL2(fe.fecps, '<-good ----------------------------------------------------------------------="" --------------------="" ----------------="" ---------------="" ----------="" --------="" -------="" ----="" -="" 03:27:07="" 05:41:46="" 0="" 10="" 11="" 12.1.0.1.0="" 12="" 12c="" 13="" 18="" 1982="" 1="" 2013="" 2023="" 2270166="" 2="" 3="" 4="" 5="" 64bit="" 65535="" 6="" 7="" 8="" 9="" advanced="" all="" analytics="" and="" application="" as="" below="" bitand="" by="" c="" change="" checkpoint="" con_id="" connected="" copyright="" database="" db="" dec="" decode="" ecover="" edition="" enterprise="" fe.fedup="" fe.fefnh="fn.fnnum" fe.fenum="" fe.fepax="0)" fe.festa="" fe.fetsn="" fe="" file="" fn.fnflg="" fn.fnfno="fe.fenum" fn.fnnam="" fn.fntyp="4" fn="" fri="" from="" ias_opss.dbf="" iasactivities.dbf="" iasjive.dbf="" iaswebcenter.dbf="" iau.dbf="" instance="" is="" kccfe="" kccfn="" last="" lus:="" max="" mds.dbf="" name="" not="" null="" ocs.dbf="" olap="" on="" online="" ood="" option="" options="" or="" oracle.="" oracle="" oradata="" orcl="" order="" partitioning="" production="" real="" recover="" release="" reserved.="" rights="" rows="" scn="" scnstatus="" selected.="" sql="" sqlplus="" status="" svctbl.dbf="" sysaux01.dbf="" sysdba="" system01.dbf="" system="" testing="" the="" this="" time="" to:="" to="" try="" undotbs01.dbf="" users01.dbf="" wcp12cr2="" webcenter_portlet.dbf="" where="" with="" x=""> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery




SQL> recover database until cancel;
Media recovery complete.


SQL> alter database open resetlogs;

Database altered.