Wednesday, October 18, 2023

DATAPUMP Schema Level EXPORT & IMPORT.--- for remapping the schema with another.


DATAPUMP Schema Level EXPORT & IMPORT.--- for remapping the schema with another.

 

 create an directory at OS level dp_17_10_2023; automatically log file and dmp files will be created in this directory

 SQL> create directory datapump_schema as 'D:\dp_17_10_2023';

 Directory created.

 Grant permission to schema:

 SQL> grant read,write on directory datapump_schema to varshini;

Grant succeeded.

 SQL> grant read,write on directory datapump_schema to Ranesh;

Grant succeeded.

 

EXPORT and SCHEMA.

expdp directory=datapump_schema dumpfile=varshini.dmp logfile=varshini_bkp.log schemas=varshini


 Export: Release 19.0.0.0.0 - Production on Tue Oct 17 09:14:05 2023 Version 19.19.0.0.0

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

 Username: / as sysdba

 

Export another schema  

expdp directory=datapump_schema dumpfile=Ranesh.dmp logfile=Ranesh_bkp.log schemas=Ranesh

 

 IMPORT and SCHEMA for remapping. before import need to remove that Schema - Ranesh

 

impdp directory=datapump_schema dumpfile=varshini.dmp logfile=IMP_varshini.log remap_schema=varshini:Ranesh

- No comments:

Automatic SQL Tuning Adviser

 Automatic SQL Tuning Adviser:

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

Optimizer --- Will generate and pick execution plan

Suppose Stale Table or Wrong statistics of a table 

1. Statistical Analysis 
2. Accessing Path (Using Index or not)

 

High Level steps for SQL Tuning Adviser:

How to find the SQL ID:

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

select sql_id from v$sql where sql_text like 'select * from Ranesh';

 

Create Tuning Task:

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

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'abc52qbabz2ac',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_3',

description => 'Tuning task1 for statement abc52qbabz2ac');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

 

Execute Tuning Task:

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

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_3');

 

Status of Tuning Task:

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

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_3';

 

Display the Recommendation:

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

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_3') from dual;

 

Drop the Tuning Task:

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

execute dbms_sqltune.drop_tuning_task('my_tuning_task_1');

 

Find out state Tables:

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

set lines 160 pages 2000

col owner format a15

col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('RANESH')

AND stale_stats='YES'

ORDER BY owner;

 

Gather table state:

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

execute dbms_stats.gather_table_stats(ownname =>'RANESH',tabname =>'RANESH',estimate_percent =>100);

 

 

Execution logs from SQL Tuning Adviser:

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

1. Run SQL statement and capture the SQL ID:

 

[oracle@oraclelab3 ~]$ ps -ef|grep smon

oracle    4941     1  0 Nov12 ?        00:00:16 ora_smon_TESTDB

oracle    8631  5466  0 00:55 pts/1    00:00:00 grep --color=auto smon

[oracle@oraclelab3 ~]$ sqlplus Ranesh/Ranesh

 

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 24 00:55:21 2021

Version 19.3.0.0.0

 

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

 

Last Successful login time: Wed Nov 24 2021 00:16:14 +05:30

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> select * from Ranesh;

 

      STNO STNAME

---------- ---------------

         1 Ranesh

         2 John

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

 

9 rows selected.

 

2. Validate the table and make sure there table is not in stale state:

 

SQL> set lines 160 pages 2000

col owner format a15

col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('RANESH')

AND stale_stats='YES'

ORDER BY owner;SQL> SQL> SQL> SQL> SQL>   2    3    4    5

 

no rows selected

 

SQL> select sql_id from v$sql where sql_text like 'select * from Ranesh';

 

SQL_ID

-------------

abc52qbabz2ac

 

SQL>

 

3. Create Tuning Task for the SQL ID:

 

[oracle@oraclelab3 ~]$ ps -ef|grep smon

oracle    4941     1  0 Nov12 ?        00:00:16 ora_smon_TESTDB

oracle    8049  5048  0 00:48 pts/0    00:00:00 grep --color=auto smon

[oracle@oraclelab3 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 24 00:58:04 2021

Version 19.3.0.0.0

 

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

 

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> DECLARE

  2  l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'abc52qbabz2ac',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_1',

  3    4    5    6    7    8    9  description => 'Tuning task1 for statement abc52qbabz2ac');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/ 10   11   12

 

PL/SQL procedure successfully completed.

 

SQL> 

 

4. Run the Tuning Task and check the status:

 

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_1');

 

PL/SQL procedure successfully completed.

 

SQL>

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_1';

 

TASK_NAME

--------------------------------------------------------------------------------

STATUS

-----------

my_tuning_task_1

COMPLETED

 

SQL> 

 

5. Review the recommendation provided by Tuning Task:

 

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_1') from dual;

SQL> SQL> SQL>

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_1')

----------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : my_tuning_task_1

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status  : COMPLETED

Started at         : 11/24/2021 00:58:28

Completed at       : 11/24/2021 00:58:28

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_1')

----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------

Schema Name: RANESH

SQL ID     : abc52qbabz2ac

SQL Text   : select * from Ranesh

 

-------------------------------------------------------------------------------

There are no recommendations to improve the statement.

 

-------------------------------------------------------------------------------

 

SQL>

 

6. Delete Some rows from table and table will become in stale state:

 

SQL> select * from Ranesh;

 

      STNO STNAME

---------- ---------------

         1 Ranesh

         2 John

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

 

9 rows selected.

 

SQL>

 

SQL> delete from Ranesh where STNO=10;

 

7 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from Ranesh;

 

      STNO STNAME

---------- ---------------

         1 Ranesh

         2 John

 

SQL> set lines 160 pages 2000

col owner format a15

SQL> SQL> col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('RANESH')

AND stale_stats='YES'

ORDER BY owner;SQL> SQL> SQL>   2    3    4    5

 

OWNER           TABLE_NAME                               NUM_ROWS LAST_ANALYZED

--------------- ----------------------------------- ------------- -----------------------------------

RANESH          RANESH                                         9 24-NOV-2021 00:28:55

 

SQL>

 

7. Now you can generate the new Tuning Task and see the recommendation from it:

 

SQL> DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'abc52qbabz2ac',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_2',

description => 'Tuning task1 for statement abc52qbabz2ac');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/  2    3    4    5    6    7    8    9   10   11   12

 

PL/SQL procedure successfully completed.

 

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_2');

 

PL/SQL procedure successfully completed.

 

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_2';

 

TASK_NAME

----------------------------------------------------------------------------------------------------

STATUS

-----------

my_tuning_task_2

COMPLETED

 

 

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_2') from dual;SQL> SQL> SQL>

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

----------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : my_tuning_task_2

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status  : COMPLETED

Started at         : 11/24/2021 01:02:18

Completed at       : 11/24/2021 01:02:18

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------

Schema Name: RANESH

SQL ID     : abc52qbabz2ac

SQL Text   : select * from Ranesh

 

-------------------------------------------------------------------------------

FINDINGS SECTION (1 finding)

-------------------------------------------------------------------------------

 

1- Statistics Finding

---------------------

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

----------------------------------------------------------------------------------------------------

  Optimizer statistics for table "RANESH"."RANESH" are stale.

 

  Recommendation

  --------------

  - Consider collecting optimizer statistics for this table.

    execute dbms_stats.gather_table_stats(ownname => 'RANESH', tabname =>

            'RANESH', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

  Rationale

  ---------

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

----------------------------------------------------------------------------------------------------

    The optimizer requires up-to-date statistics for the table in order to

    select a good execution plan.

 

-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

 

1- Original

-----------

Plan hash value: 2356778634

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |         |     9 |    63 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| RANESH |     9 |    63 |     3   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

-------------------------------------------------------------------------------

 

SQL>

 

8. In this above Tuning task has given recommendation that table is in state stat and gather stats on the table

 

SQL> execute dbms_stats.gather_table_stats(ownname => 'RANESH', tabname =>'RANESH', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

PL/SQL procedure successfully completed.

 

SQL>

 

9. Now the table is not in stale state then you can generate the new tuning task which will not give any recommendation since table is upto date.

 

SQL> DECLARE

  2  l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

  3    4    5  sql_id => 'abc52qbabz2ac',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_3',

description => 'Tuning task1 for statement abc52qbabz2ac');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

  6  END;

/  7    8    9   10   11   12

 

PL/SQL procedure successfully completed.

 

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_3');

 

PL/SQL procedure successfully completed.

 

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_3';

 

TASK_NAME

----------------------------------------------------------------------------------------------------

STATUS

-----------

my_tuning_task_3

COMPLETED

 

 

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_3') from dual;

SQL> SQL> SQL>

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_3')

----------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : my_tuning_task_3

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status  : COMPLETED

Started at         : 11/24/2021 01:06:05

Completed at       : 11/24/2021 01:06:05

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_3')

----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------

Schema Name: RANESH

SQL ID     : abc52qbabz2ac

SQL Text   : select * from Ranesh

 

-------------------------------------------------------------------------------

There are no recommendations to improve the statement.

 

-------------------------------------------------------------------------------

 

SQL>

 

10. Drop those tuning tasks:

 

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_1');

 

PL/SQL procedure successfully completed.

 

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_2');

 

PL/SQL procedure successfully completed.

 

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_3');

 

PL/SQL procedure successfully completed.

 

SQL>

 

- No comments:
Subscribe to: Posts (Atom)

Table import and export.

Create Data Pump Directory. The first step in Oracle Data Pump is to create an OS level directory which will be used by Oracle for perform...

  • ORACLE CLOUD EBS INSTALLTION
      CLOUD EBS INSTALLTION   login as: opc Authenticating with public key "rsa-key-20201105" from agent Last login: Thu Nov 19 08...
  • How to Generate fmx from fmb in R12
      1) Login to application server. 2) Go to the directory $AU_TOP/forms/US 3) Place “.fmb” file in binary mode or search for existing --> ...
  • ORA-01157 and ORA-01110
    ORA-01157  is raised when Database Writer (DBWR) is unable to find and lock a Datafile. This may be due to various reasons like - Datafile ...