Category: Database

This section related to Oracle database different version

Oracle 23c Is out

Posted on by Osama Mustafa in Database

Oracle Database 23c Free Version Now Available to Developers.

The new Oracle Database 23c Free – Developer Release is a free version of the trusted Oracle Database used by businesses of all sizes around the globe. Obtaining the only converged database that works with any data model and any task type is as easy as downloading it from the internet with no oracle.com user account or license click-through requirements.

If you’re looking for a free database to use for developing data-driven applications, look no further than Oracle Database 23c Free – Developer Release. Users can upgrade to other Oracle Database products at any moment because of its backwards compatibility with Oracle Database Enterprise Edition and Oracle Database cloud services.

Documentation here

Download:

Regards

Osama

Generate 10046 and 10053 trace

Posted on by Osama Mustafa in Database

who didn’t face an issue with database or query and wants to know more about it ? what is going on behind that query or application ?

Oracle provides different method to do that, one of them is to enable and generate trace called 10046, the idea from using this kind of trace is that we can track the execution plan for the session and provide more information such as bin variable, more information about wait time parse and a lot of other information related to performance issues.

to generate the trace you have to follow these steps , you can use “SYS” user or any other user depends on the session, be notice that you should turn off the trace to complete gathering information, same as the below

spool check.out 
set timing on 
alter session set tracefile_identifier='NAME_OF_TRACE'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10046 trace name context forever, level 12'; 
######
Run the query or the code here
#####
select 'close the cursor' from dual; 
alter session set events '10046 trace name context off'; 
spool off 
exit; 


Important hint :-

  • exit is very important to complete and close the trace.
  • you can change the name of the trace depends on what you want
  • Close the trace after you finished to complete gathering information.
  • We select from dual to ensure ensure the previous cursor is closed.

For Trace 10053 which is also provide information but it’s can be generated only for Hard parse SQL, which mean you should add Oracle Hint to the query to ensure the hard parse will be working.

spool check.out 
set timing on 
alter session set tracefile_identifier='NAME_OF_THE_TRACE'; 
alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10053 trace name context forever'; 

run the problematic statement 

select 'close the cursor' from dual; 
alter session set events '10053 trace name context off'; 
spool off 
exit;

Important hint :-

  • exit is very important to complete and close the trace.
  • you can change the name of the trace depends on what you want
  • Close the trace after you finished to complete gathering information.
  • We select from dual to ensure ensure the previous cursor is closed.

Now you can use the tkprof to make the trace more readable, tkprof located under $ORACLE_HOME/bin, Run the following command after generate the above trace 

tkprof <trace-file> <output file> <Username/password>

cheers

Thank you

TOAD :ORA-12170 when trying to connect using TOA

Posted on by Osama Mustafa in Database

The following error appeared when you are trying to conenct to database using toad applicaion: –

 

1.png

Make sure of the following :-

  • Database is up and running
  • Listener is up and database is registered using.

lsnrctl status

if the above steps is done and still facing the same issue then do the following :-

  • Right Click on my computer and choose properties.
  • Advance system settings.
  • Environment Variable.

check the below entry is exsits, if not add it by press on new and follow the same steps by adding TNS_ADMIN and the location of the tnsnames.ora, sqlnet.ora into the 2nd box like the picture below.

2.png

3.png

 

Thanks

Osama

Where is the DBA in the DevOps tools ?

Posted on by Osama Mustafa in Database

  • Database administrator job has been changed and it’s not like before any more, In the past The DBA responsibility was limited within database which mean troubleshooting, backup , Performance tuning, high availability .. etc after few years new layer has been added to the responsibility which is application server layer for example weblogic ,Oracle embedded the weblogic with most of their products such as enterprise manager cloud, E-business suits, as i remember after three years another layer added to DBA knowledge which is virtualization and regarding to this virtualization  made the DBA life more easier and not like before, solution providing another layer of backup and server management.

However now everything has been changed and the market working under new term which is CLOUD including different vendor AWS, Azure, and Oracle, As DBA your knowledge shouldn’t stop by only Database, within cloud there is new tools that can help the DBA with their daily Job and it’s called Devops.

DBA usually review each change request to ensure that it is well thought out,They are in charge of monitoring their databases and keeping them available and high-performing, manage access to and the overall security of the platform.

Database automation frees the DBA from the bottleneck of the past that delayed application releases since there is different environment production, Pre-Production, Development or Testing, the DevOps Automation relieved of the pressures of constantly having to juggle and merge various teams’ database changes, are now free to help their organisations take bigger steps forward in ongoing innovation.

But what is the Devops ? And How can i use to make my Job Easier

DevOps is the combination of cultural philosophies, practices, and tools that increases an organization’s ability to deliver applications and services at high velocity: evolving and improving products at a faster pace than organizations using traditional software development and infrastructure management processes. This speed enables organizations to better serve their customers and compete more effectively in the market.

Benefits of DevOps
  • Speed
  • Rapid Delivery
  • Reliability
  • Scale
  • Security

DevOps Practices

  • Continuous Integration
  • Continuous Delivery
  • Microservices
  • Infrastructure as Code
  • Monitoring and Logging
  • Communication and Collaboration
DevOps practices for DBA and Data Team focus on the Tools, practices and techniques that are useful for the Data team to use for Development activities, Testing setup and execution, Collaboration techniques and Deployment tools and techniques. The aim of this site is to further the practice and help the DBA’s and Data team to use these practices to become productive and become part of the delivery team and embrace the devops movement.
DevOps Tools
  • Docker
  • Jenkins
  • Ansible
  • Puppet
  • Nagios
  • Monit
  • Behat
  • Chef
There is more tools for sure each one of these tools having it’s own purpose for example Docker is one of the powerful container solutions, A container image is a lightweight, stand-alone, executable package of a piece of software that includes everything needed to run it: code, runtime, system tools, system libraries, settings Available for both Linux and Windows based apps, containerized software will always run the same, regardless of the environment. Containers isolate software from its surroundings, for example differences between development and staging environments and help reduce conflicts between teams running different software on the same infrastructure.
Cheers
Osama Mustafa 

Import One table to the new name in the same schema

Posted on by Osama Mustafa in Database
The following case just want to share it in case anyone need it, development wants to import the existing table in the siebel schema into the new name to check the data integrity:-

Oracle Provide you with Attribute called REMAP_TABLE Read More about it here.

Just Run the following command and change the variable depend on your environment :-

  • directory : The name of existing dump
  • dumpfile : The name of dump file
  • remap_table : Allows you to rename tables during an import operation
  • tables : the name of the table you want to restore it.

impdp directory=DMP_BAK dumpfile=31-01-2017_PRDSBL.dmp  remap_table=siebel.CX_PERMITS:CX_PERMITS_NEW tables=CX_PERMITS

Thanks
Osama  

DBMS_REDEFINITION to create partition for existing table

Posted on by Osama Mustafa in Database
to do this oracle provide you with package called DBMS_REDEFINITION for more information about it here

In this post i will show you how to partition existing table for SIEBEL application which is my case holding huge number of records.

  • Create the same structure for the original table but without any constraint just columns like the below with new name and sure choose the partition you want to use, in case any of the constraint has been created on the new table , error will be generated while the package running and you should recreate the table again without any constraint :-

CREATE TABLE SIEBEL.S_CASE_NEW
(
  ROW_ID                         VARCHAR2(15 CHAR)  ,
  CREATED                        DATE           DEFAULT sysdate                ,
  CREATED_BY                     VARCHAR2(15 CHAR)  ,
  LAST_UPD                       DATE           DEFAULT sysdate                ,
  LAST_UPD_BY                    VARCHAR2(15 CHAR)  ,
  MODIFICATION_NUM               NUMBER(10)     DEFAULT 0                      ,
  CONFLICT_ID                    VARCHAR2(15 CHAR) DEFAULT ‘0’  ,
  ASGN_USR_EXCLD_FLG             CHAR(1 CHAR)   DEFAULT ‘N’                    ,
  BU_ID                          VARCHAR2(15 CHAR) DEFAULT ‘0-R9NH’  ,
  CASE_DT                        DATE            ,
  CASE_NUM                       VARCHAR2(100 CHAR)  ,
  CHGOFCCM_REQ_FLG               CHAR(1 CHAR)   DEFAULT ‘N’                    ,
  CLASS_CD                       VARCHAR2(30 CHAR)  ,
  LOCAL_SEQ_NUM                  NUMBER(10)     DEFAULT 1                      ,
  NAME                           VARCHAR2(100 CHAR)  ,
  PR_REP_DNRM_FLG                CHAR(1 CHAR)   DEFAULT ‘N’                    ,
  PR_REP_MANL_FLG                CHAR(1 CHAR)   DEFAULT ‘N’                    ,
  PR_REP_SYS_FLG                 CHAR(1 CHAR)   DEFAULT ‘N’                    ,
  STATUS_CD                      VARCHAR2(30 CHAR)  ,
  ASGN_DT                        DATE,
  CLOSED_DT                      DATE,
  CURR_APPR_SEQ_NUM              NUMBER(10),
  DB_LAST_UPD                    DATE,
  REWARD_AMT                     NUMBER(22,7),
  REWARD_EXCH_DATE               DATE,
  APPLICANT_ID                   VARCHAR2(15 CHAR),
  APPR_TEMP_ID                   VARCHAR2(15 CHAR),
  AUDIT_EMP_ID                   VARCHAR2(15 CHAR),
  CATEGORY_TYPE_CD               VARCHAR2(30 CHAR),
  CITY                           VARCHAR2(50 CHAR),
  COUNTRY                        VARCHAR2(30 CHAR),
  CRIME_SUB_TYPE_CD              VARCHAR2(30 CHAR),
  CRIME_TYPE_CD                  VARCHAR2(30 CHAR),
  DB_LAST_UPD_SRC                VARCHAR2(50 CHAR),
  DESC_TEXT                      VARCHAR2(2000 CHAR),
  MSTR_CASE_ID                   VARCHAR2(15 CHAR),
  ORG_GROUP_ID                   VARCHAR2(15 CHAR),
  PAR_CASE_ID                    VARCHAR2(15 CHAR),
  PRIORITY_TYPE_CD               VARCHAR2(30 CHAR),
  PR_AGENCY_ID                   VARCHAR2(15 CHAR),
  PR_AGENT_ID                    VARCHAR2(15 CHAR),
  PR_DISEASE_ID                  VARCHAR2(15 CHAR),
  PR_POSTN_ID                    VARCHAR2(15 CHAR),
  PR_PROD_INT_ID                 VARCHAR2(15 CHAR),
  PR_PRTNR_ID                    VARCHAR2(15 CHAR),
  PR_SGROUP_ID                   VARCHAR2(15 CHAR),
  PR_SUBJECT_ID                  VARCHAR2(15 CHAR),
  PR_SUSPCT_ID                   VARCHAR2(15 CHAR),
  PS_APPL_ID                     VARCHAR2(15 CHAR),
  REWARD_CURCY_CD                VARCHAR2(20 CHAR),
  SERIAL_NUM                     VARCHAR2(100 CHAR),
  SOURCE_CD                      VARCHAR2(30 CHAR),
  STAGE_CD                       VARCHAR2(30 CHAR),
  STATE                          VARCHAR2(10 CHAR),
  SUBJECT_NAME                   VARCHAR2(100 CHAR),
  SUBJECT_PH_NUM                 VARCHAR2(40 CHAR),
  SUB_STATUS_CD                  VARCHAR2(30 CHAR),
  SUB_TYPE_CD                    VARCHAR2(30 CHAR),
  TERRITORY_TYPE_CD              VARCHAR2(30 CHAR),
  THREAT_LVL_CD                  VARCHAR2(30 CHAR),
  TYPE_CD                        VARCHAR2(30 CHAR),
  X_APP_BIRTH_DATE               DATE,
  X_APP_BIRTH_DT_HIJRI           VARCHAR2(10 CHAR),
  X_APP_FATHER_NAME_A            VARCHAR2(50 CHAR),
  X_APP_FATHER_NAME_E            VARCHAR2(50 CHAR),
  X_APP_FAX                      VARCHAR2(15 CHAR),
  X_APP_FIRST_NAME_A             VARCHAR2(50 CHAR),
  X_APP_FIRST_NAME_E             VARCHAR2(50 CHAR),
  X_APP_FULL_NAME                VARCHAR2(100 CHAR),
  X_APP_GENDER                   VARCHAR2(30 CHAR),
  X_APP_GFATHER_NAME_A           VARCHAR2(50 CHAR),
  X_APP_GFATHER_NAME_E           VARCHAR2(50 CHAR),
  X_APP_LAST_NAME_A              VARCHAR2(50 CHAR),
  X_APP_LAST_NAME_E              VARCHAR2(50 CHAR),
  X_APP_MAIL                     VARCHAR2(50 CHAR),
  X_APP_MOBILE                   VARCHAR2(15 CHAR),
  X_APP_MOTHER_F_NAME_A          VARCHAR2(50 CHAR),
  X_APP_MOTHER_F_NAME_E          VARCHAR2(50 CHAR),
  X_APP_MOTHER_L_NAME_A          VARCHAR2(50 CHAR),
  X_APP_MOTHER_L_NAME_E          VARCHAR2(50 CHAR),
  X_APP_TYPE                     VARCHAR2(30 CHAR),
  X_APPLICANT_CLASSIFICATION     VARCHAR2(30 CHAR),
  X_APPLICANT_NAT_ID_NO          VARCHAR2(15 CHAR),
  X_APPLICANT_TITLE              VARCHAR2(30 CHAR),
  X_APPLICANT_TYPE               VARCHAR2(50 CHAR),
  X_ATTACHMENT_FLG               VARCHAR2(5 CHAR),
  X_CANCEL_DESC                  VARCHAR2(300 CHAR),
  X_CANCEL_REASON                VARCHAR2(30 CHAR),
  X_CASE_COPY_FLG                VARCHAR2(30 CHAR),
  X_CASE_HIJRI_DATE              VARCHAR2(30 CHAR),
  X_CHECK_EXISTS_FKG             VARCHAR2(15 CHAR),
  X_CHECK_EXISTS_FLG             VARCHAR2(30 CHAR),
  X_COMMERCIAL_NAME              VARCHAR2(300 CHAR),
  X_COMMERCIAL_REG_NO            VARCHAR2(40 CHAR),
  X_COPY_SERIAL_NUM              VARCHAR2(100 CHAR),
  X_CREATED_DATE_HEJRI           VARCHAR2(30 CHAR),
  X_CREATED_GRG                  VARCHAR2(30 CHAR),
  X_CREATED_HIJRI                VARCHAR2(10 CHAR),
  X_CRED_EXP_DT_HIJRI            VARCHAR2(10 CHAR),
  X_CRED_EXPIRY_DATE             DATE,
  X_CRED_ISSUE_DATE              DATE,
  X_CRED_ISSUE_DT_HIJRI          VARCHAR2(10 CHAR),
  X_CRED_NO                      VARCHAR2(30 CHAR),
  X_CRED_TYPE                    VARCHAR2(30 CHAR),
  X_CRS_NO                       VARCHAR2(15 CHAR),
  X_DLV_DATE                     DATE,
  X_DLV_DATE_HIJRI               VARCHAR2(10 CHAR),
  X_DLV_USER_ID                  VARCHAR2(15 CHAR),
  X_DOCUMENT_SORUCE              VARCHAR2(30 CHAR),
  X_EST_OWNERSHIP_TYPE           VARCHAR2(30 CHAR),
  X_EST_TYPE                     VARCHAR2(30 CHAR),
  X_GIS_DATA_LOAD                VARCHAR2(15 CHAR),
  X_GIS_DATA_STATUS              VARCHAR2(10 CHAR),
  X_INV_TYPE                     VARCHAR2(30 CHAR),
  X_IS_UPLOADED                  VARCHAR2(30 CHAR),
  X_LAND_ORG_TYPE                VARCHAR2(30 CHAR),
  X_LAND_STATUS                  VARCHAR2(30 CHAR),
  X_LAND_TYPE                    VARCHAR2(30 CHAR),
  X_MUNICIPAL_NAME               VARCHAR2(30 CHAR),
  X_NATIONALITY                  VARCHAR2(30 CHAR),
  X_ORG_END_REG_DATE             DATE,
  X_ORG_END_REG_HIJRI_DATE       VARCHAR2(10 CHAR),
  X_ORG_REGISTRATION_DATE        DATE,
  X_ORG_REGISTRATION_HIJRI_DATE  VARCHAR2(10 CHAR),
  X_ORGANIZATION_NAME            VARCHAR2(200 CHAR),
  X_ORIGINAL_ORG_ID              VARCHAR2(15 CHAR),
  X_PAPER_FLG                    CHAR(1 CHAR)   DEFAULT ‘N’,
  X_PAYMENT_DT                   DATE,
  X_PAYMENT_FLAG                 VARCHAR2(30 CHAR),
  X_PAYMENT_NO                   VARCHAR2(10 CHAR),
  X_PR_EMP_ID                    VARCHAR2(15 CHAR),
  X_PR_ENG_OFFICE_ID             VARCHAR2(15 CHAR),
  X_PROC_DESC                    VARCHAR2(100 CHAR),
  X_PROC_FLG                     VARCHAR2(30 CHAR),
  X_PROC_TYPE                    VARCHAR2(30 CHAR),
  X_PROXY_ISSUE_AUTHORITY        VARCHAR2(30 CHAR),
  X_PROXY_NO                     VARCHAR2(10 CHAR),
  X_QX_CRED_EXP_DT_HIJRI         DATE,
  X_REGISTRATION_DATE            DATE,
  X_REGISTRATION_HIJRI_DATE      VARCHAR2(10 CHAR),
  X_REGISTRATION_NO              VARCHAR2(30 CHAR),
  X_REJECT_DESC                  VARCHAR2(300 CHAR),
  X_REJECT_REASON                VARCHAR2(30 CHAR),
  X_RELATION_TYPE                VARCHAR2(30 CHAR),
  X_RETURN_DATE                  DATE,
  X_RETURN_DATE_HIJRI            VARCHAR2(10 CHAR),
  X_RETURN_NOTES                 VARCHAR2(100 CHAR),
  X_RETURN_REASON                VARCHAR2(30 CHAR),
  X_SCHEMA_STATUS                VARCHAR2(30 CHAR),
  X_SECURITY_FLG                 VARCHAR2(30 CHAR),
  X_SELECT_FLG                   CHAR(1 CHAR)   DEFAULT ‘N’,
  X_STRIPPED_FIRST_NAME          VARCHAR2(50 CHAR),
  X_STRIPPED_FULL_NAME           VARCHAR2(200 CHAR),
  X_STRIPPED_LAST_NAME           VARCHAR2(50 CHAR),
  X_STRIPPED_MOTHER_FIRST        VARCHAR2(50 CHAR),
  X_STRIPPED_MOTHER_FULLNAME     VARCHAR2(50 CHAR),
  X_STRIPPED_MOTHER_LASTNAME     VARCHAR2(50 CHAR),
  X_STRIPPED_SECOND_NAME         VARCHAR2(50 CHAR),
  X_STRIPPED_THIRD_NAME          VARCHAR2(50 CHAR),
  X_TO_BU_ID                     VARCHAR2(15 CHAR),
  X_UPDATED_GRG                  VARCHAR2(30 CHAR),
  X_UPDATED_HIJRI                VARCHAR2(10 CHAR),
  COR_TYPE                       VARCHAR2(30 CHAR),
  CORR_CAS_CAT                   VARCHAR2(30 CHAR),
  PRIMARY_EMPLOYEE               VARCHAR2(30 CHAR),
  SUBMIT_TO_STATUS               VARCHAR2(15 CHAR),
  X_DOCUMENT_TYPE                VARCHAR2(30 CHAR),
  X_SURVEYOR_NAME                VARCHAR2(30 CHAR),
  X_OLD_STATUS                   VARCHAR2(30 CHAR),
  X_APPLICANT_ORG_ID             VARCHAR2(15 CHAR),
  X_APPLICANT_ROW_ID             VARCHAR2(15 CHAR),
  X_GIS_TOKEN                    VARCHAR2(100 CHAR),
  X_NEW_PERMIT_FLG               CHAR(1 CHAR)   DEFAULT ‘Y’,
  X_TRANSACTION_MOD              VARCHAR2(30 CHAR),
  X_TRANSACTION_STATUS           VARCHAR2(30 CHAR),
  X_CASE_CAT                     VARCHAR2(100 CHAR),
  X_CASE_COPY_SERIAL             NUMBER(10),
  X_GIS_PARAMETER                VARCHAR2(300 CHAR),
  X_GIS_ROWIDS                   VARCHAR2(100 CHAR),
  READING_FLAG                   CHAR(1 CHAR)   DEFAULT ‘N’,
  X_GIS_MUNICIPAL                VARCHAR2(30 CHAR),
  X_ORG_DELEGATE_NAME            VARCHAR2(200 CHAR),
  X_PR_POS_ORG_ID                VARCHAR2(15 CHAR),
  X_ORGANIZATION_STRIPPED_NAME   VARCHAR2(200 CHAR),
  X_CITIZEN_REVIEW               CHAR(1 CHAR),
  X_ALLOWED_USAGE                VARCHAR2(50 CHAR),
  X_AUTHORIZATION_DATE           DATE,
  X_AUTHORIZATION_HIJRI_DATE     VARCHAR2(10 CHAR),
  X_AUTHORIZATION_NO             VARCHAR2(20 CHAR),
  X_CIVIL_APPROVAL_DATE          DATE,
  X_CIVIL_APPROVAL_HIJRI_DATE    VARCHAR2(10 CHAR),
  X_CIVIL_APPROVAL_NO            VARCHAR2(20 CHAR),
  X_CIVIL_OFFICE                 VARCHAR2(25 CHAR),
  X_NEW_MUNICIPAL_NAME           VARCHAR2(30 CHAR),
  X_OLD_MUNICIPAL_NAME           VARCHAR2(30 CHAR),
  X_OLD_STATUS_CD                VARCHAR2(30 CHAR),
  X_RESTRICT_NUM                 VARCHAR2(30 CHAR),
  X_LAST_UPD_HIJRI               VARCHAR2(10 CHAR),
  X_APP_BIRTH_DATE_HIJRI         VARCHAR2(10 CHAR),
  X_FEES_EXCEPTION               VARCHAR2(30 CHAR),
  X_FINCL_NAME                   VARCHAR2(30 CHAR),
  X_IS_OWNER                     VARCHAR2(15 CHAR),
  X_MANAGER_ID                   VARCHAR2(15 CHAR),
  X_OWNERSHIP_TYPE               VARCHAR2(30 CHAR),
  X_PRINT_FLG                    CHAR(1 CHAR)   DEFAULT ‘N’,
  X_PRNT_FLG                     VARCHAR2(5 CHAR),
  X_SECRETARY_ID                 VARCHAR2(15 CHAR),
  X_UNDER_SECRETARY_ID           VARCHAR2(15 CHAR),
  X_VIEW_SEQUENCE                NUMBER(10)     DEFAULT 0,
  X_REGULATION_ACCPTNCE_FLG      VARCHAR2(7 CHAR),
  X_CONFIRM_FLAG                 VARCHAR2(7 CHAR),
  X_OCCUPATION_IN_RESIDENCE      VARCHAR2(30 CHAR),
  X_ROWNUM                       NUMBER(10),
  X_NUMBER_ARCHIVAL              VARCHAR2(15 CHAR),
  X_ATTACHMENT_PARAMETERS        VARCHAR2(500 CHAR),
  X_ATTACHMENT_ROW_IDS           VARCHAR2(500 CHAR),
  X_BP_ID                        VARCHAR2(15 CHAR),
  X_CONTROL_SUB_TYPE             VARCHAR2(30 CHAR),
  X_CONTROL_TYPE                 VARCHAR2(30 CHAR),
  X_DEPOSIT_ID                   VARCHAR2(15 CHAR),
  X_MALL_ID                      VARCHAR2(15 CHAR),
  X_NEW_DEPOSIT                  NUMBER(10),
  X_SOURCE_ID                    VARCHAR2(15 CHAR),
  X_STORE_ID                     VARCHAR2(15 CHAR),
  APPEALED_FLG                   CHAR(1 CHAR)   DEFAULT ‘N’                    ,
  CHANGED_FLG                    CHAR(1 CHAR)   DEFAULT ‘N’                    ,
  EVAL_ASSESS_ID                 VARCHAR2(15 CHAR),
  X_ARCHIEVING_TYPE              VARCHAR2(30 CHAR),
  X_ACTIVITY_ID                  VARCHAR2(15 CHAR),
  X_OLD_SERIAL_NUM               VARCHAR2(20 CHAR),
  X_OWNER_ORG_POSTN_ID           VARCHAR2(15 CHAR),
  X_PR_CNTR_POSTN_ID             VARCHAR2(15 CHAR),
  X_REPORT_URL                   VARCHAR2(500 CHAR),
  X_VIOLATION_ID                 VARCHAR2(15 CHAR),
  X_APPLICANT_SOURCE             VARCHAR2(50 CHAR)
)
PARTITION BY RANGE (created)(PARTITION S_CASE_2015 VALUES LESS THAN (TO_DATE(’01/01/2016′, ‘DD/MM/YYYY’)), PARTITION S_CASE_2016 VALUES LESS THAN (TO_DATE(’01/01/2017′, ‘DD/MM/YYYY’)), PARTITION S_CASE_2017 VALUES LESS THAN (MAXVALUE));

  • Now we should start the redefinition by running the following package 

 BEGIN
  DBMS_REDEFINITION.start_redef_table(
    uname      => ‘SIEBEL’,      
    orig_table => ‘S_CASE’,
    int_table  => ‘S_CASE_NEW’);
END;
/

  • Sync the both tables together 

BEGIN
  dbms_redefinition.sync_interim_table(
    uname      => ‘SIEBEL’,    
    orig_table => ‘S_CASE’,
    int_table  => ‘S_CASE_NEW’);
END;
/ 

  • After Running the both package above run the below scripts but run it from the server side, because it’s takes times & to avoid any interruption

SET SERVEROUTPUT ON
DECLARE
  l_errors  NUMBER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname            => ‘SIEBEL’,
    orig_table       => ‘S_CASE’,
    int_table        => ‘S_CASE_NEW’,
    copy_indexes     => DBMS_REDEFINITION.cons_orig_params,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => FALSE,
    num_errors       => l_errors,
    copy_statistics  => FALSE,
    copy_mvlog       => FALSE);
 
  DBMS_OUTPUT.put_line(‘Errors=’ || l_errors);
END;
/  

  • Finish the redefinition

BEGIN
  dbms_redefinition.finish_redef_table(
    uname      => ‘SIEBEL’,    
    orig_table => ‘S_CASE’,
    int_table  => ‘S_CASE_NEW’);
END;
/

  •  After finishing everything successfully, just drop the new table because now it’s became the old table 

DROP TABLE S_CASE_NEW; 

  • Run the below query to see if the partition has been successfully created  

SELECT partitioned
FROM   dba_tables
WHERE  table_name = ‘S_CASE’;

Thanks
Osama

Kill Datapump job that running inside Database

Posted on by Osama Mustafa in Database
even if you interrupt data bump job on shell therefore you have to do the following :-

as / as sysdba run the below query :

select * from dba_datapump_jobs

After checking the status of each one,

run the following :-

DECLARE
h1 NUMBER;
BEGIN
h1:=DBMS_DATAPUMP.ATTACH(‘JOB_NAME’,’OWNER_NAME’);
DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

Thanks 

When the last time the password has been changed ?

Posted on by Osama Mustafa in Database
Do you want to know what is the last time the DBA or developers change the password ?

Using the below table :-
Name
Null
Type
USER#       
NOT NULL
NUMBER
NAME        
NOT NULL
NUMBER        
TYPE#       
NOT NULL
VARCHAR2(30)  
PASSWORD    
        
NUMBER        
DATATS#     
NOT NULL
NUMBER        
TEMPTS#     
NOT NULL
DATE          
CTIME       
NOT NULL
DATE          
PTIME       
        
DATE          
EXPTIME     
        
DATE          
LTIME       
        
NUMBER        
RESOURCE$   
NOT NULL
VARCHAR2(38)  
AUDIT$      
        
NUMBER        
DEFROLE     
NOT NULL
NUMBER        
DEFGRP#     
        
NUMBER        
DEFGRP_SEQ# 
        
NUMBER        
ASTATUS     
NOT NULL
NUMBER        
LCOUNT      
NOT NULL
VARCHAR2(30)  
DEFSCHCLASS 
        
VARCHAR2(4000)
EXT_USERNAME
        
NUMBER        
SPARE1      
        
NUMBER        
SPARE2      
        
NUMBER        
SPARE3      
        
VARCHAR2(1000)
SPARE4      
        
VARCHAR2(1000)
SPARE5      
        
DATE
SPARE6      
        
NUMBER        
USER#       
NOT NULL
VARCHAR2(30)  
NAME        
NOT NULL
NUMBER        
TYPE#       
NOT NULL
NUMBER        
PASSWORD    
        
DATE          
DATATS#     
NOT NULL
DATE          
TEMPTS#     
NOT NULL
DATE          
CTIME       
NOT NULL
DATE          
PTIME       
        
NUMBER        
EXPTIME     
        
VARCHAR2(38)  
LTIME       
        
NUMBER        
RESOURCE$   
NOT NULL
NUMBER        
AUDIT$      
        
NUMBER        
DEFROLE     
NOT NULL
NUMBER        
DEFGRP#     
        
NUMBER        
DEFGRP_SEQ# 
        
VARCHAR2(30)  
ASTATUS     
NOT NULL
VARCHAR2(4000)
Referring to Oracle Documentation :-
     
NAME – name for user or role
TYPE# – 0 for role or 1 for user
CTIME – the date of creation
PTIME – the date the password was last changed
EXPTIME – the date the password has last expired
LTIME – the date the resource was last locked
LCOUNT – number of failed logon
You Can use this query :-

SELECT name, ctime, ptime FROM sys.user$ WHERE name = ‘USER-NAME’;

Thank you
Osama 

Change SCAN Listener from etc/host to DNS

Posted on by Osama Mustafa in Database
Changing SCAN LISTENER from etc/hosts on Linux to use DNS which mean the from one IP to 3 IPs

The SCAN listener will remain the same without any change therefore like i mentioned before the these steps only provide changing for SCAN to use DNS.

“I Will not post any output”

the configuration for the IP should be added to DNS and to check this on OS level you should nslookup command :-

On node #1 run the below command to check the Listener status and see the current configuration:-

srvctl status scan
srvctl status scan_listener
srvctl config scan
srvctl config scan_listener

On node #2 run the below command to check the Listener status and see the current configuration:-

srvctl status scan
srvctl status scan_listener
srvctl config scan
srvctl config scan_listener

The output should be the same for both.

Stop all the listeners on your RAC as root :-

srvctl stop scan_listener
srvctl stop scan
srvctl status scan
srvctl status scan_listener

Remove or comment any entire in /etc/hosts after done stop the listener.

Let’s modify the scan :-

srvctl modify scan -n
srvctl modify scan_listener –u

Start listener :-

srvctl start scan
srvctl start scan_listener
srvctl status scan
srvctl status scan_listener

Thanks
Osama

ORA-12154: TNS:could not resolve the connect identifier specified” error during DBCA

Posted on by Osama Mustafa in Database
While trying to install Oracle Database 11gR2 on Windows 2012R2 I faced the following error :-

ORA-12154: TNS:could not resolve the connect identifier specified” error during DBCA

After checking the logs and investigate about the issue  :-

The password was containing “@” after removed it from the password everything works perfectly.

Thanks
Osama