How To Export And Import Statistics In Oracle

How To Export And Import Statistics In Oracle

Step 1:
If you wish to save your statistics of schema or table, which you can use later during any query issue Or if you wish copy the statistics from production database to development , then this method will be helpful.

Here i will take export of statistics of a table ARVIND.TEST from PROD and import into TEST

DEMO:


create a table to store the stats:

--- ARVIND is the owner of the stats table, STAT_TEST - name of the stats table

PROD> exec DBMS_STATS.CREATE_STAT_TABLE('ARVIND','STAT_TEST','SYSAUX');

PL/SQL procedure successfully completed.

SQL>  select owner,table_name from dba_tables where table_name='STAT_TEST';
SQL> /

OWNER      TABLE_NAME
------------ ------------
ARVIND      STAT_TEST

SQL>

PROD> exec dbms_stats.export_table_stats(ownname=>'ARVIND', tabname=>'TEST', stattab=>'STAT_TEST', cascade=>true);

PL/SQL procedure successfully completed.


Now take expdp of this stats table(STAT_TEST)


[oracle@PROD]$ expdp dumpfile=stats.dmp logfile=stats.log tables=ARVIND.STAT_TEST directory=DIR1

Export: Release 12.1.0.2.0 - Production on Fri Dec 28 10:09:26 2019

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

Username: ARVIND/ARVIND@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "ARVIND"."SYS_EXPORT_TABLE_01":  ARVIND/********@orcl dumpfile=stats.dmp logfile=stats.log tables=ARVIND.STAT_TEST directory=DIR1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
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 "ARVIND"."STAT_TEST"                           19.24 KB      19 rows
Master table "ARVIND"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARVIND.SYS_EXPORT_TABLE_01 is:
  /home/oracle/DIR1/stats.dmp
Job "ARVIND"."SYS_EXPORT_TABLE_01" successfully completed at Fri Dec 28 10:12:33 2019 elapsed 0 00:03:00



Move the dump file from PROD to TEST machine and import the same


[oracle@TEST]$ impdp dumpfile=stats.dmp logfile=stats.log tables=ARVIND.STAT_TEST directory=DIR1 table_exists_action=REPLACE

Import: Release 12.1.0.2.0 - Production on Fri Dec 28 10:12:42 2019

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

Username: ARVIND/XXXXXX@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "ARVIND"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ARVIND"."SYS_IMPORT_TABLE_01":  ARVIND/********@orcl dumpfile=stats.dmp logfile=stats.log tables=ARVIND.STAT_TEST directory=DIR1 table_exists_action=REPLACE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ARVIND"."STAT_TEST"                           19.24 KB      19 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 "ARVIND"."SYS_IMPORT_TABLE_01" successfully completed at Fri Dec 28 10:14:36 2019 elapsed 0 00:01:42


Now import the statistics in database:


SQL> set lines 200
SQL> set pagesize 200
SQL> col table_name for a12
SQL> col owner for a12
SQL> select owner,table_name,last_analyzed from dba_tables where table_name='TEST';

OWNER      TABLE_NAME   LAST_ANAL
------------ ------------ ---------
ARVIND      TEST   05-Dec-19


SQL> exec dbms_stats.import_table_stats(ownname=>'ARVIND', tabname=>'TEST',  stattab=>'STAT_TEST', cascade=>true);

PL/SQL procedure successfully completed.

SQL> select owner,table_name,last_analyzed from dba_tables where table_name='TEST';

OWNER      TABLE_NAME   LAST_ANAL
------------ ------------ ---------
ARVIND      TEST   28-Dec-19


We can observe after import_table_stats, last_analyzed date has been updated.

We can do export/import statistics at schema level or database level:

Note : use expdp to export the newly created stats table from source to target database.

Database Statistics

1. Log onto the database

sqlplus '/ as sysdba'
2. Import the database statistics

SQL> exec dbms_stats.IMPORT_DATABASE_STATS('<enter the name of the stats table>','<enter an identifier>','<enter the owner of the stats table>');
For example; exec dbms_stats.IMPORT_DATABASE_STATS('MYSTATS_TABLE','STATS_TEST_MOVE','MYUSER');



For schema:

Log onto the database

sqlplus '/ as sysdba'
2. Create a table to hold the exported statistics.

SQL> exec DBMS_STATS.CREATE_STAT_TABLE('<table owner>','<enter a name for the stats table>','<tablespace to store the stats table');
For example; exec DBMS_STATS.CREATE_STAT_TABLE('MYUSER','MYSTATS_TABLE','MYTABLESPACE');

Automate recyclebin purging in oracle

Automate recyclebin purge in oracle

Setup this simple scheduler job as sysdba to purge the objects in the recycbin.
This is one of the most space cosuming location that often dba's forget to cleanup and the
objects get piled up occupying lot of space. Based on how long you want to save these dropped object setup a job under scheduler to run below plsql block either daily, weekly or monthly. 

 I suggest to run weekly.


--For user_recyclebin purge--
-- plsql --

declare
VSQL varchar2(500);
VSQL1 varchar2(500);
Vcnt number(5);
begin

select count(*) into Vcnt from user_recyclebin;

/***  Optional if you would like to keep record count of objects purged
-- Uncomment if you would like to keep this
insert into SYS.PURGE_STATS (obj_count) values (Vcnt);
commit;
**/

if Vcnt>0 then
VSQL1:='purge user_recyclebin';
execute immediate VSQL1;
dbms_output.put_line('DBA RECYCLEBIN has been purged.');
end if;
end;
/



--For dba_recyclebin purge--
-- plsql --


declare
VSQL varchar2(500);
VSQL1 varchar2(500);
Vcnt number(5);
begin

select count(*) into Vcnt from dba_recyclebin;

/***  Optional if you would like to keep record count of objects purged
-- Uncomment if you would like to keep this
insert into SYS.PURGE_STATS (obj_count) values (Vcnt);
commit;
**/

if Vcnt>0 then
VSQL1:='purge dba_recyclebin';
execute immediate VSQL1;
dbms_output.put_line('DBA RECYCLEBIN has been purged.');
end if;
end;
/

Prerequisites

The database object must reside in your own schema or you must have the DROP ANY ... system privilege for the type of object to be purged, or you must have the SYSDBA system privilege. To perform the PURGE DBA_RECYCLEBIN operation, you must have the SYSDBA or PURGE DBA_RECYCLEBINsystem privilege.

Data Pump Exit Codes

oracle@Linux01:[/u01/oracle/DPUMP] $ exp atoorpu file=abcd.dmp logfile=test.log table=sys.aud$
About to export specified tables via Conventional Path ...
. . exporting table                           AUD$     494321 rows exported
Export terminated successfully without warnings.

oracle@qpdbuat211:[/d01/oracle/DPUMP] $ echo $?
0


oracle@Linux01:[/u01/oracle/DPUMP] $ imp atoorpu file=abcd.dmp logifle=test.log
LRM-00101: unknown parameter name 'logifle'

IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
IMP-00000: Import terminated unsuccessfully

oracle@Linux01:[/u01/oracle/DPUMP] $ echo $?
1

Can be used in export shell scripts for status verification:

if test $status -eq 0
then
 echo "export was successfull."
else
 echo "export was not successfull."
fi

Also check below page fore reference :

https://docs.oracle.com/database/121/SUTIL/GUID-34D0DEE7-3530-42DC-BE01-C2588CC73CE5.htm#SUTIL3834

Setup Oracle database using Docker container.

Step-1:

Install docker container. Based on your windows OS Version. if you are using windows 7 will need docker engine and Kitematic 

if you are on windows 10 or higher use : Docker Community Edition (Download)

Docker Instructions : https://docs.docker.com/install/

Once installed you will see Docker Quickstart Terminal and Kitematic.

open docker machine and then Kitematic.

Search for oracle XE 11g by Seth89. Download and install the below shown container

Once installed You will have oracle database installed and ready to use. you can ignore the below error message  /docker-entrypoint-init.d/cache: no such file or dir.

Another way to confirm successful installation is you will see Unauthorized image in web preview section

Make sure you have bridge network configured. As below

Check if below configured ports are configured.

How to connect to oracle database in the container :

Based on the IP Address and port configured in above image. Use those credentials to connect to DB.

Username : System

Password : oracle

Or to connect via sqlplus use as below:

Optional settings :

To configure or change volumes use. Volumes will help you to copy files over or attach a local working directory to container:

Restore archivelogs from RMAN backup

Restore archive logs from RMAN backup

rman> restore archivelog from logseq=37501 until logseq=37798 thread=1;

or

rmna> restore archivelog between sequence 37501 and 37798 ;