Find weblogic version from admin console

 Using the Oracle WebLogic Server Administration Console

• Use the left hand menu to navigate to Environment and Servers. Then, click the [Monitoring] tab. You should see a screen similar to the one below:

From SSH/ Putty console you can see also:

Go to MW home which is oracle home for your weblogic/ Fusion Middlewire

$cd $ORACLE_HOME/oui/bin

$pwd

$ pwd
/u01/fmw/app/oracle/product/12.1.0.2/oui/bin

$ sh viewInventory.sh | grep Distribution

   Distribution: WebLogic Server for FMW 12.2.1.4.0
   Distribution: OPatch 13.9.4.2.4
   Distribution: OPatch 13.9.4.2.5

Queries on OEM Repository to find some valuable information

Querying Oracle Management Repository (OEM repository) to dig out for a valuable information.  Here we can see very few examples.


How to find Available Target Types in OEM?

SQL> select distinct target_type,type_display_name 

from mgmt_targets order by 1;


TARGET_TYPE                    TYPE_DISPLAY_NAME
------------------------------ ----------------------------------------
cluster                        Cluster
composite                      Group
has                            Oracle High Availability Service
host                           Host
j2ee_application               Application Deployment
j2ee_application_cluster       Clustered Application Deployment
j2ee_application_domain        Domain Application Deployment
metadata_repository            Metadata Repository
oracle_apache                  Oracle HTTP Server
oracle_beacon                  Beacon
oracle_bi_publisher            Oracle BI Publisher
oracle_cloud                   Cloud
oracle_coherence               Oracle Coherence Cluster
oracle_coherence_cache         Oracle Coherence Cache
oracle_coherence_node          Oracle Coherence Node
oracle_database                Database Instance
oracle_dbsys                   Database System
oracle_em_service              EM Service
oracle_emd                     Agent
oracle_emrep                   OMS and Repository
oracle_emsvrs_sys              EM Servers System
oracle_home                    Oracle Home
oracle_listener                Listener
oracle_oms                     Oracle Management Service
oracle_oms_console             OMS Console
oracle_oms_pbs                 OMS Platform
oracle_pdb                     Pluggable Database
oracle_si_network              Systems Infrastructure Network
oracle_si_server_map           Systems Infrastructure Server
osm_cluster                    Cluster ASM
osm_instance                   Automatic Storage Management
osm_proxy                      Asm Proxy
rac_database                   Cluster Database
weblogic_cluster               Oracle WebLogic Cluster
weblogic_domain                Oracle WebLogic Domain
weblogic_j2eeserver            Oracle WebLogic Server
weblogic_nodemanager           Oracle WebLogic Node Manager


How to find all registered targets in OEM for a particular target type?

So I want to find all the targets registered in OEM repository whose target type is "rac_database"
in other words, basically I want to find all the rac databases registered in my OEM.

SQL> select target_name,target_type,target_guid 

from mgmt_targets where target_type='rac_database';

TARGET_NAME                    TARGET_TYPE
------------------------------ ------------------------------
HCMPRD                     rac_database
HCMSTG                     rac_database


How to find a particular target information registered in OEM?

SQL> select * from mgmt_targets where target_name='HCMPRD';


How to find Important Matrics related to a particular Target?

SQL> select * from mgmt$metric_daily 

where target_name = 'HCMPRD' and trunc(rollup_timestamp) >= trunc(sysdate-2);


How to find the daily growth of a database(last 7 days) from OEM repository?

SQL> select rollup_timestamp, average
from sysman.mgmt$metric_daily
where target_name = 'HCMPRD'
and column_label = 'Used Space(GB)'
and trunc(rollup_timestamp) >= trunc(sysdate-7)
order by rollup_timestamp;

ROLLUP_TIME    AVERAGE
---------   ----------
18-NOV-20    2794.44
19-NOV-20    2794.45
20-NOV-20    2794.50
21-NOV-20    2794.54
22-NOV-20    2794.65
23-NOV-20    2794.74

How to find database user accounts password is going to expire in next 15 days?

select distinct Target_name "Target_Database",host_name,username,profile,expiry_date
from sysman.mgmt$db_users
where expiry_date is not null  and username not like '%SYS%'
and trunc(expiry_date-sysdate) between 0 and 15;

Target_Database              HOST_NAME               USERNAME        PROFILE      EXPIRY_DATE
-------------------- ----------------------------- ----------- ------------  ------------
HCMDE2              hcmdev2.example.com     U_KMPSMST        SECURE       10-DEC-20
HCMDE2              hcmde2.example.com      U_SEBUAU         SECURE       26-NOV-20
HCMST1              hcmtst1.example.com     U_VANSHUI        SECURE       26-NOV-20
HCMST1              hcmtst1.example.com     U_SDHAGAY        SECURE       10-DEC-20


How to find perticular file system usage details from all target hosts:

with FSsize as
(select rollup_timestamp, lower(target_name) hostname, round((average/1024),0) size_gb , key_value filesystem
from SYSMAN.mgmt$metric_daily
where metric_name ='Filesystems'
  and rollup_timestamp > trunc(sysdate-2)
  and metric_column = 'size'),
FSAvailable as
(select rollup_timestamp, lower(target_name) hostname, round((average/1024),0) size_gb , key_value filesystem
from SYSMAN.mgmt$metric_daily
where metric_name ='Filesystems'
  and rollup_timestamp > trunc(sysdate-2)
  and metric_column = 'available'),
FSpctAvailable as
(select rollup_timestamp, lower(target_name) hostname, round(average,2) pctAvailable , key_value filesystem
from SYSMAN.mgmt$metric_daily
where metric_name ='Filesystems'
  and rollup_timestamp > trunc(sysdate-2)
  and metric_column = 'pctAvailable'
)
select
   a.rollup_timestamp ts#
   , a.hostname
   , a.filesystem "Mounted on"
   , a.size_gb "FS_Size_GB"
   , (a.size_gb-b.size_gb) "FS_Used_GB"
   , b.size_gb "FS_Available_GB"
   , c.pctavailable "Fs_Free_%"
   , 100-c.pctavailable "Fs_Used_%"
from FSsize a, FSAvailable b, FSpctAvailable c
where c.hostname = a.hostname and c.hostname = b.hostname
and c.rollup_timestamp = a.rollup_timestamp and c.rollup_timestamp = b.rollup_timestamp
and c.filesystem = a.filesystem and c.filesystem = b.filesystem
and c.filesystem = '/u01';


How to find CPU Utilization of one host for last 24 hours?

SELECT a.collection_time, a.value
FROM sysman.gc_metric_values a 

WHERE TRUNC(a.collection_time) between TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND a.entity_type = 'host'
AND metric_group_name = 'Load'
AND metric_column_name = 'cpuUtil'
AND a.entity_name like 'oratesthost.example.com' order by a.collection_time;

COLLECTION_TIME          VALUE
---------------------- ----------
24-NOV-2020 16:57:06      4.149
24-NOV-2020 17:02:06      4.317
24-NOV-2020 17:07:06      4.003
24-NOV-2020 17:12:06      3.093
24-NOV-2020 17:17:06      2.897
24-NOV-2020 17:22:06      3.117
24-NOV-2020 17:27:06      2.782
24-NOV-2020 17:32:06      2.546
24-NOV-2020 17:37:06      2.061

...

How to find Memory Utilization of one host for last 24 hours?

SELECT a.collection_time, a.value
FROM sysman.gc_metric_values a WHERE TRUNC(a.collection_time) between TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND a.entity_type = 'host'
AND metric_group_name = 'Load'
AND metric_column_name = 'memUsedPct'
AND a.entity_name like 'oratesthost.example.com' order by a.collection_time;

COLLECTION_TIME           VALUE
-------------------- ----------
...
24-NOV-2020 23:52:06     27.879
24-NOV-2020 23:57:06     27.841
25-NOV-2020 00:02:06     27.865
25-NOV-2020 00:07:06     27.874
25-NOV-2020 00:12:06     27.877
25-NOV-2020 00:17:06     27.878
25-NOV-2020 00:22:06     27.881
25-NOV-2020 00:27:06     27.876
25-NOV-2020 00:32:06     27.877
..

How to find Requests Per Minute for any java application?

SELECT a.collection_time, a.value  
FROM sysman.gc_metric_values a WHERE  TRUNC(a.collection_time) between TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND a.entity_type = 'weblogic_j2eeserver'
AND metric_group_name = 'server_servlet_jsp'
AND metric_column_name = 'service.throughput'
AND a.entity_name like '%/TST_WEB_webdomain/testwebdomain/hcmapp1%' order by a.collection_time;

COLLECTION_TIME           VALUE
-------------------- ----------
24-NOV-2020 16:37:03    707.332
24-NOV-2020 16:52:03    468.336
24-NOV-2020 17:07:03    560.999
24-NOV-2020 17:22:03    421.933
24-NOV-2020 17:37:03    385.066
...


How to find Archive log summary for all Oracle databases from oem?

select distinct database_name "DATABASE_NAME",instance_name "INSTANCE_NAME",log_mode "LOG_MODE",host_name "HOST_NAME"
from mgmt$db_dbninstanceinfo order by LOG_MODE;

DATABASE_NAME        INSTANCE_NAME    LOG_MODE         HOST_NAME
-------------------- ---------------- ------------ --------------------------------
EBSPRD                 EBSPRD             ARCHIVELOG   oraprdhost.example.com
EBSVAL                 EBSVAL             ARCHIVELOG   oravalhost.example.com
HCMPRD                 HCMPRD             ARCHIVELOG   oraprdhost.example.com
....

How to find Backout information details?

select blackout_name "BLACKOUT_NAME",created_by "CREATED_BY",start_time "START_TIME",target_name "TARGET_NAME",
target_type "TARGET_TYPE",status "STATUS"
from SYSMAN.MGMT$BLACKOUT_HISTORY where STATUS in('Partial Blackout','Started');

Interval partitioning

I received a request to add a partition automatically on every data based on date. Assume up to 2000 one partition but after for each date partition to be created based on a date column.

Here is the example:

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , time_id       DATE
  , quantity_sold NUMBER(3)
  )
 PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
 (PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
  );

-- Your test data like some thing like this
insert into sales values(9,'9-Jan-2005',100);
commit;

-- To see all partition tables

select * from USER_TAB_PARTITIONS where table_name='SALES';

More on Table partition...

A perfect network setup in Oracle Cloud Infrastructure (OCI)

Background:
A Virtual Cloud Network is made up of the following components.

Compartment
Subnets
Route Tables
Internet Gateways
Dynamic Routing Gateways (DRG)
Security Lists
DHCP Options
Local Peering Gateways
Service Gateways

Let us set up a typical network setup to access OCI from your laptop:

Note: You should have an account in OCI to access it. after creating cloud account in OCI, use your URL and use your tenancy at end part of the URL

e.g., Here my OCI url with my tenant name.

https://console.ap-mumbai-1.oraclecloud.com/?tenant=tapati

Step-1:  create a compartment
Step-2: Create network settings:
Step 2.1: Now create security list before creating subnet
Step 2.2: Next create Route Table
Step 2.3: Create subnet now
Step 2.4: Add rules to Route Table

Step-1:  create a compartment

Cloud à Identity à Compartment

Create compartment à give your compartment name in the shown window like below

Then, click on create Compartment

Post creation:

Post creation of compartments, now you can proceed for your network settings.

This compartment concept is very new in OCI cloud to segregate your infrastructure as per requirement. Example, your DEV/TEST compartments can be separated to your production environment. In this way you will have purely different network setups based on environment type.

Let us proceed create Network settings.

Step-2 : Create network settings:

Cloud à Networking à Virtual cloud Networks

Virtual Cloud Networks à select your compartment à 


Click “Create Virtual Cloud network’”

Now click on “Create Virtual cloud Network”, post creation

Step 2.1 Now create security list before creating Subnet

Security list à  Create security list

Click on “create security list”

Post creation of security list

Step 2.2 : Next create Route Table

Click “Route Table”

STEP 2.3 : Create Subnet now

CHOOSE all options as selected in the screenshot

Click “Create Subnet”

CREATED one more subnet with another IP range

Now we need internet gateway to connect to OCI network from internet

Internet Gateway ß click

Click “Create Internet Gateway”

Step 2.4 : add rules to Route Table

Add router rules

Note: Here we used 0.0.0.0/0 to access via internet without any barrier

But in reality, we need to specify specific IP address as per the customer

e.g., to access visa we need to add visa IP address here.

Now your OCI cloud network setup is ready. you can create VMs or other Infrastructure and assign create network. Use Private key to access VMs like Linux from your local machine.

ORA-20005: object statistics are locked

While gather stats for a index, I got below error. It is not a problem, Some reason, may be statistics collection stopped. But once you unlocked, you able to do. Please go through this workaround:

Command Executed:

execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname => 'IDX_DOJ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 26193
ORA-06512: at line 1


SQL> col owner for a15
SQL> col TABLE_NAME for a30
SQL> col STATTYPE_LOCKED for a30
SQL> select owner,table_name,STATTYPE_LOCKED 
from dba_ind_statistics where index_name='IDX_DOJ' and owner='SCOTT';

OWNER           TABLE_NAME                STATTYPE_LOCKED
--------------- --------------------- ------------------------------
SCOTT       EMPLOYEE                       ALL

SQL>

Remember:
STATTYPE_LOCKED – ALL -> stats are locked for the table
STATTYPE_LOCKED – NULL – > stats are not locked

Now unlock the table to collect statistics:

SQL> EXEC DBMS_STATS.unlock_table_stats('SCOTT','EMPLOYEE');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname => 'IDX_DOJ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

SQL>

In case of table, you can do like below:

SQL> EXEC DBMS_STATS.unlock_table_stats('SCOTT','TEST');

PL/SQL procedure successfully completed.

SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='TEST' and owner='SCOTT';


OWNER      TABLE_NAME STATTYPE_LOCKED
---------- ---------- ----------------------------------
SCOTT    TEST

Similarly we can unlock stats for a schema also.

SQL> EXEC DBMS_STATS.unlock_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

ORA-01194: file 1 needs more recovery to be consistent

Due to some reason, I lost some of archive logs while recovering a database.
I received below error and you may may have seen also the same.

The scenario is "Recovering an Oracle Database with missing Redo/ archive log".

RMAN> recover database;
Starting recover at 02-MAR-20
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 67 is already on disk as file /u03/fast_recovery_area/ORCL/archivelog/2020_02_26/o1_mf_1_67_bgzcn05f_.arc
archived log for thread 1 with sequence 69 is already on disk as file /u03/fast_recovery_area/ORCL/archivelog/2020_02_26/o1_mf_1_69_bgzdqo9n_.arc
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u03/oradata/ORCL/datafile/o1_mf_system_bh914cx2_.dbf'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/02/2020 08:44:21
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 68 and starting SCN of 624986 found to restore
RMAN>


The natural thing to check first when trying to open the database after an incomplete recovery is the fuzziness and PIT (Point In Time) of the datafiles from SQLPlus:

SQL> select fuzzy, status, checkpoint_change#,
     to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as  
     checkpoint_time, count(*)
      from v$datafile_header
     group by fuzzy, status, checkpoint_change#, checkpoint_time
     order by fuzzy, status, checkpoint_change#, checkpoint_time;
FUZZY STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME        COUNT(*)
----- ------- ------------------ -------------------- ----------
NO    ONLINE              647929 26-FEB-2020 16:58:14          1
YES   ONLINE              551709 26-FEB-2020 15:59:43          4
SQL>

The fact that there are two rows returned and that not all files have FUZZY=NO indicates that we have a problem and that more redo is required before the database can be opened with the RESETLOGS option.

But our problem is that we don’t have that redo and we’re desperate to open our database anyway.

Recovering Without Consistency:

Again, recovering without consistency is not supported and should only be attempted as a last resort.

Opening the database with the data in an inconsistent state is actually pretty simple.  We simply need to set the “_allow_resetlogs_corruption” hidden initialization parameter and set the undo management to “manual” temporarily:

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> alter system set undo_management='MANUAL' scope=spfile;
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
..
Database mounted.
SQL>

Now, will the database open? The answer is still: “probably not”.  Giving it a try we get:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2663], [0], [551715], [0], [562781], [], [], [], [], [], [], []
Process ID: 4538
Session ID: 237 Serial number: 5621
SQL>

Seems there  is another problem in database.  Actually the situation is better than previous. Here we may take it forward.

Of-course ORA-00600 error is too pathetic but here that a datafile has a recorded SCN that’s ahead of the database SCN.  The current database SCN is shown as the 3rd argument (in this case 551715) and the datafile SCN is shown as the 5th argument (in this case 562781).  Hence a difference of :

562781 - 551715 = 11066

In this example, that’s not too large of a gap.  But in a real system, the difference may be more significant.  Also if multiple datafiles are ahead of the current SCN you should expect to see multiple ORA-00600 errors.

The solution to this problem is quite simple: roll forward the current SCN until it exceeds the datafile SCN.  The database automatically generates a number of internal transactions on each startup hence the way to roll forward the database SCN is to simply perform repeated shutdowns and startups.  Depending on how big the gap is, it may be necessary to repeatedly shutdown abort and startup – the gap between the 5th and 3rd parameter to the ORA-00600 will decrease each time.  However eventually the gap will reduce to zero and the database will open:

SQL> connect / as sysdba
Connected to an idle instance.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
..
Database mounted.
Database opened.
SQL>

RMAN-06053: unable to perform media recovery because of missing log - Fix

During creating a duplicate database, I faced below error but for me consistent recovery is not required. We need to open the database but how?

Error Message:

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/04/2020 11:19:22
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
....
RMAN-06025: no backup of archived log for thread 1 with sequence 652548 and starting SCN of 26787010633 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 652547 and starting SCN of 26787010507 found to restore
RMAN-00567: Recovery Manager could not print some error messages

Recovery Manager complete.

From the error, it is seen, required archivelog is not available with backup.

Root-cause of this problem:

The backup is not sufficient to complete the duplicate command, its an old backup.
more log files are needed to complete.

-- Recover up to available log sequence 652547 but not including log 652547.

$ rman auxiliary / catalog catuser/Pas$w0rd@catdb

RUN
{
allocate auxiliary channel c0 device type disk;
SET UNTIL SEQUENCE 652547 THREAD 1;
duplicate database 'TESTDB' to 'DEMODB' nofilenamecheck;
}

If you are facing same archivelog missing errors, then change the script once gain to that log sequence and run the command after starting the database in nomount.

attaching last few lines:

RMAN-06025: no backup of archived log for thread 1 with sequence 652304 and starting SCN of 26786392356 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 652303 and starting SCN of 26786382438 found to restore
RMAN-00567: Recovery Manager could not print some error messages

-- Again tried after failure

rman auxiliary / catalog catuser/Pas$w0rd@catdb

RUN
{
allocate auxiliary channel c0 device type disk;
SET UNTIL SEQUENCE 652303 THREAD 1;
duplicate database 'TESTDB' to 'DEMODB' nofilenamecheck;
}

Captured last few lines when successfully completed.

input datafile copy RECID=228 STAMP=1034165624 file name=/u05/oradata/DEMODB/datafile/o1_mf_test_data_h5zdp8oo_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=232 STAMP=1034165624 file name=/u05/oradata/DEMODB/datafile/o1_mf_users_h5zjbptz_.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database enable block change tracking

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 04-Mar-2020 12:13:55

RMAN> 
RMAN> 


-- verify

SQL> select name,open_mode from v$database;

NAME                 OPEN_MODE
-----------------    --------------------
DEMODB               READ-WRITE

Rollback to restore point in Dataguard environment - RAC database

Step : Create Restore point and start Testing

-- In DR database
DGMGRL> show database crmdr;
DGMGRL> edit database crmdr set state = 'APPLY-OFF';
SQL> CREATE RESTORE POINT load_test GUARANTEE FLASHBACK DATABASE;
DGMGRL> edit database crmdr set state = 'APPLY-ON';

-- in Prod database
SQL> CREATE RESTORE POINT load_test GUARANTEE FLASHBACK DATABASE;

Step : Rollback after Testing done

-- Rollback -- In Prod database
$ srvctl stop   database -d crmprd
$ srvctl start  instance -d crmprd -i crmprd1 -o mount
SQL> flashback database to restore point load_test;
SQL> alter database open resetlogs;
$ srvctl stop   database -d crmprd
$ srvctl start  database -d crmprd

-- Rollback -- In DR database
DGMGRL> edit database crmdr set state = 'APPLY-OFF';
SQL> flashback database to restore point load_test;
DGMGRL> edit database crmdr set state = 'APPLY-ON';

Export and Import schema Statistics in Oracle Database

There may be times that you wish to save you database statistics. Perhaps to use in a test system or prior to a code release that may ultimately change your statistics and change you query plans for the worse. By exporting the statistics, they can be saved for future importing.

Source Database : srcdb ( host- example01)
Target database : trgdb ( host - example02)

1) To generate schema statistics and import them into a separate database:

SQL> select instance_name,host_name,status from v$instance;

INSTANCE_NAME    HOST_NAME                      STATUS
---------------- ------------------------------ ------------
srcdb           example01    OPEN

SQL> 

On the source host, start SQL*Plus and connect to the source database as administrator.
Create a table to hold the schema statistics.

For example, execute the following PL/SQL program to create user statistics table opt_stats:

SQL> BEGIN
  DBMS_STATS.CREATE_STAT_TABLE ( 
    ownname => 'SCOTT'
,   stattab => 'opt_stats'
);
END;
/

PL/SQL procedure successfully completed.

2) Gather schema statistics.

For example, manually gather schema statistics as follows:

-- generate representative workload
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

3) Use DBMS_STATS to export the statistics.

For example, retrieve schema statistics and store them in the opt_stats table created previously:

SQL> BEGIN
  DBMS_STATS.EXPORT_SCHEMA_STATS (
    ownname => 'SCOTT'
,   stattab => 'opt_stats'
);
END;
/

PL/SQL procedure successfully completed.


4) Use Oracle Data Pump to export the contents of the statistics table.

For example, run the expdp command at the operating schema prompt:

SQL> create or replace directory dump_dir as '/u09/backup/export';

Directory created.

$ expdp DIRECTORY=dump_dir DUMPFILE=SCOTT_stat.dmp logfile=SCOTT_stat_expdp.log TABLES=SCOTT.opt_stats

e.g.,
[oracle@example01 export]$ expdp DIRECTORY=dump_dir DUMPFILE=SCOTT_stat.dmp logfile=SCOTT_stat_expdp.log TABLES=SCOTT.opt_stats

Export: Release 12.2.0.1.0 - Production on Wed Feb 5 12:40:39 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA DIRECTORY=dump_dir DUMPFILE=SCOTT_stat.dmp logfile=SCOTT_stat_expdp.log TABLES=SCOTT.opt_stats
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.125 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SCOTT"."OPT_STATS"                      1.534 MB   10486 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u03/work/export/SCOTT_stat.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 5 12:44:09 2020 elapsed 0 00:03:24


5) Transfer the dump file to the target database host.

$ scp gmohapatra@example01:/u03/work/export/*.dmp .

6) Log in to the target host, and then use Oracle Data Pump to import the contents of the statistics table.

SQL> create or replace directory dump_dir1 as '/u08/backup/import';

Directory created.

For example, run the impdp command at the operating schema prompt:

$ impdp DIRECTORY=dump_dir1 DUMPFILE=SCOTT_stat.dmp logfile=impdp_SCOTT_stat.log TABLES=SCOTT.opt_stats 
e.g.,
[oracle@example02 import]$ impdp DIRECTORY=dump_dir1 DUMPFILE=SCOTT_stat.dmp logfile=impdp_SCOTT_stat.log TABLES=SCOTT.opt_stats

Import: Release 12.2.0.1.0 - Production on Wed Feb 5 13:30:16 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA DIRECTORY=dump_dir1 DUMPFILE=SCOTT_stat.dmp logfile=impdp_SCOTT_stat.log TABLES=SCOTT.opt_stats
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."OPT_STATS"                      1.534 MB   10486 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Feb 5 13:30:43 2020 elapsed 0 00:00:20

[oracle@example02 import]$

7) On the target host, start SQL*Plus and connect to the target database.
Use DBMS_STATS to import statistics from the user statistics table and store them in the data dictionary.

The following PL/SQL program imports schema statistics from table opt_stats into the data dictionary:

SQL> BEGIN
  DBMS_STATS.IMPORT_SCHEMA_STATS(
    ownname => 'SCOTT'
,   stattab => 'opt_stats'
);
END;
/  

PL/SQL procedure successfully completed.

SQL> select name from v$database;

NAME
---------
trgdb

SQL>

-- If you want to lock the statistics, below dbms api can be used.

SQL> exec dbms_stats.LOCK_SCHEMA_STATS('SCOTT');

PL/SQL procedure successfully completed.

SQL>

Note: You can do export and import statistics of whole database or for few tables also.