How to find the list of unusable index on the full database:-

set pagesize 9000
set linesize 2000
set long 20000

select dbms_metadata.get_ddl('INDEX', index_name, owner)
from all_indexes
where owner in(SELECT OWNER from (select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE')))
AND INDEX_NAME IN(SELECT INDEX_NAME from (select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE')));

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

Schema Wise

set pagesize 232
set linesize 232
set feedback off
set echo off
set verify off
select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE') where OWNER='&schema_name';
spool off


===============
Generating the unusable command:

[oracle@xxxx swarn]$ more 2.sql
spool /u01/oracle/swarn/unusable_cmd.sql
set pagesize 232
set linesize 232
set feedback off
set echo off
set verify off
set heading off
select 'alter index '||OWNER||'.'||index_name||' unusable; 'from (select OWNER, INDEX_NAME from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE'));
spool off
====================

Introduction to Oracle 18C new features

 Source: Internet 

Author: User

The Oracle 18c was released in 2018-02-16 or with Oracle's cloud first concept, and 18c is now available on cloud and engineered Systems. Oracle 18c claims to be an autonomous database that reduces the work of many DBAs, and does a lot of DBA work worry about their work being impacted? In fact, the Oracle 18c does not reflect the particularly obvious, but this is a future trend and signal!

Looking at some of the official documentation, add some new features to share with you, 18C continues the characteristics of the 12C cloud database, and from 18C began to change the future version of the naming method.

Before you know the new features of 18C, you need to have a basic understanding of 12C. June 26, 2013, Oracle released the Oralce 12C. Cloud computing design. 12C introduces the new features of CDB and PDB in the multi-tenancy environment (multitenant environment) introduced in Oracle 12C database (NOTE: Multi-tenancy Technology (English: multi-tenancy technology) or multi-tenancy technology, is a software architecture technology that explores and implements how to share the same system or program components in a multiuser environment, and still ensures the isolation of data between users, allowing a database container (CDB) to host multiple pluggable databases (PDB).

CDB is all called container database, the Chinese translation is a data base container, the PDB is all called pluggable database, you can plug and Unplug databases. Before Oracle 12C, the instance and database were one-to-one or many-on relationships (RAC): an instance could be associated with only one database, and the database could be loaded by multiple instances. The instance and database cannot be a one-to-many relationship. When you enter Oracle 12C, the instance and database can be a one-to-many relationship.

After 12C has a basic understanding of the features, you can see some of the new features added after 18C, and here are some new practical features added.

(a) Availability

2. Oracle Data Guard multi-instance Redo Apply Supports Use of the Block change Tracking Files for RMAN Backups
   Now, Multiple-instance Redo Apply (also called MIRA), you can also support the backup method of BCT (Block change Tracking). This is a very efficient way of incrementally backing up large databases, where the master and slave are RAC (real-time application clusters), and backups take place from the library.
4. Automatic Correction of Non-logged Blocks at a Data Guard Standby Database
   Automatically corrects bad block problems caused by nologging. Two new standby logging modes are added (mainly to speed up the main library loading data):
   One is Standby nologging for Data availability, that is, the commit of the loading operation will be delay until all Standby apply data.
   sql> ALTER DATABASE SET STANDBY nologging for DATA availability;
   One is the Standby nologging for load performance, which is similar to the previous one, but when the Load data is encountered, the network bottleneck is not sent first, which guarantees the loading performance, but the loss of data, but the loss of data, will be retrieved from the primary again.
   sql> ALTER DATABASE SET STANDBY nologging for LOAD performance;
6. Shadow Lost Write Protection
   Create a Shadow tablespaces (Note: Big file tablespace) to provide protection. (Note: At this point you can not need ADG to provide additional lost write protection), write lost shadow protection, can be opened at the table space, database, data file level, to proactively check in advance and prevent write loss.
8. Backups from Non-cdbs is usable after migration to CDB
   The original non-cdb can be migrated in this manner as a PDB to the currently existing CDB.
10. Scalable sequences
    Adaptive sequences are designed to reduce competition under high concurrent DML, by building discontinuous sequences, beating and mitigating split contention on similar indexes, which is an enhancement from Oracle optimization best practices.
12. user-defined sharding Method
    Supports user-defined shards. The feature that existed in the beta version of 12.2 was canceled in the official release. Now, the release was released again.
14. Consistency levels for Multi-shard Queries
    Provides multishard_query_data_consistency initialization parameters that can be set before execution to avoid SCN synchronization when querying across shards.
16. Manual Termination of Run-Away queries
    Now, you can manually kill a statement and keep opening the session:
    ALTER SYSTEM CANCEL SQL.

(b) Big Data and data warehousing

1. Approximate top-n Query processing
   Note: In 18c, Approx_count and Approx_sum are added to match the use of Approx_rank.
2. LOB support with IMC, Big Data Sqllob
   The object also supports in memory.

III) Database Overall

2. Copying a PDB in an Oracle Data Guard environment has 2 new parameters to facilitate the creation of the PDB in the ADG environment.
   One is standby_pdb_source_file_directory, automatically looking for ADG data file path (note, before 18c, if you insert a PDB into a STANDBY environment in the CDB, you need to manually copy the files to the PDB path The other is standby_pdb_source_file_dblink to automatically find the ADG file path when remote clone is convenient (note: Before 18c, if it is a local clone, you do not have to copy the data file, but the remote clone, Need to be copied manually).
4. PDB Lockdown Profile Enhancements
   You can now create a PDB lockdown profile in application root and CDB root.
   You can now create another PDB lockdown profile based on a PDB lockdown profile.
   18c contains three default lockdown Profile:private_dbaas,saas,public_dbaas
6. refreshable PDB switchoverpdb Refresh
   supported the switchover. The switchover is divided into two scenarios in which the planned kernel is unplanned. Planned, can be cut back, mainly for the balance of the CDB load. Unplanned, mainly for PDB master failure, do not switch the entire CDB.
8. PDB Snapshot carouselpdb Snapshot
   Backup turntable, reserved 8 copies by default, backed up every 24 hours.
   ALTER pluggable DATABASE SNAPSHOT MODE every HOURS;
10. New Default location of Oracle Database Password File
    The password file path is already in oracle_base, not oracle_home, in order to remove changes to the oracle_home.
12. Read-only Oracle Home
    can be in DBCA or roohctl-enable to process read Only Oracle home installation, run Orabasehome command can check whether the current Oracle home is read only, if this command output results and Oracle_ho ME, it means that Oracle Home is read-write. If the output is oracle_base/homes/home_name, it means that ORACLE HOME is read-only. Set Oracle_home to read-only, the change file will be created in $ORACLE _base, which facilitates standardization, distribution sharing, rolling upgrades, and more.
14. Online merging of partitions and subpartitions
    Support Online consolidation of partitions, enhanced online maintainability. Note: You need to use the ONLINE keyword.
16. Concurrent SQL execution with SQL performance Analyzerspa
    Can be run in parallel (by default or serial) to help you complete the SPA test faster, this feature is useful when upgrading.

(d) Performance

1. Automatic in-memory
   The automatic in memory will evict infrequently accessed IM columns from memory in the case of Heat Map.
2. Database in-memory support for External Tables
   External tables Support IM features.
3. Memoptimized Rowstore
   There is a memoptimize pool area in the SGA, the size is set by the Memoptimize_pool_size parameter, and when Fast lookup is turned on, the memory area can be used for quick lookups. When Fast lookup is turned on, you need to add a keyword to the build table statement: When querying based on a primary key, you can use fast lookup. Memoptimized Rowstore will greatly improve the high-frequency query based on the primary key in the Internet of things.

(v) RAC and Grid

1. ASM Database Cloning
   ASM database cloning supports multi-tenant data, a feature that provides a native clone of data based on ASM redundancy, which can replace storage-level-based cloning or replication synchronization
2. Converting Normal or High redundancy disk Groups to Flex disk Groups without Restricted Mount
   Encourage the transition to flex DiskGroup.
   (vi) Security
3. Integration of Active Directory Services with Oracle Database
   and Microsoft's AD combination. Before 18c, you need to use Oracle Enterprise User Security (EUS) to interact, and now you can use centrally managed users (CMU) to directly connect AD users and groups and Ora CLE's users and role are mappiing.

These are the main features that are involved in the release of Oracle 18c, and more new features that need to be discovered and summed up in our practice, but from the perspective of Oracle's development strategy, later versions will become smarter, with cloud computing and artificial intelligence in the spring breeze, we wait and see, Expect Oracle to bring more surprises to us!

Introduction to Oracle 18C new features

dba_scheduler

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select NAME from V$DATABASE;

begin
        DBMS_SCHEDULER.create_job(
                job_name                => 'DoDSMaintenance',
                job_type                => 'stored_procedure',
                job_action      => 'DoDSMaintenance',
                start_date              => systimestamp,
                end_date                => null,
                repeat_interval => 'freq=daily; byhour=6; byminute=0; bysecond=0;',
                enabled         => true,
                auto_drop       => false,
                comments        => 'Do daily maintenance on the DS data');
end;
/



quit


select job,schema_user,last_date,next_date,total_time,interval,failures,what from dba_jobs where broken ='N' order by log_user;


select job, log_user,LAST_DATE,NEXT_DATE,NEXT_SEC from dba_jobs;


 col job for 999
col log_user for a14
col priv_user for a14
col schema_user for a14
col what for a49
set lines 120
select job, log_user, priv_user, schema_user, what, broken, failures from dba_jobs;