ERROR: $opatch lsinventory OPATCH_JAVA_ERROR : An exception of type “OPatchException” has occurred: Can not get a list of inventory on this home. ERROR: OPatch failed because of Inventory problem. Solution:…
Read MoreISSUE: Unable to connect sqlplus after cloning oracle_home Solution: OSDBA_GROUP should be mentioned while cloning ORACLE_HOME as mentioned below $ORACLE_HOME/perl/bin/perl clone.pl ORACLE_BASE=”/scratch/aime/clone” ORACLE_HOME=”/u01/app/oracle/dbhome” OSDBA_GROUP=dba OSOPER_GROUP=oper -defaultHomeName OR Take Backup of…
Read MoreORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA ERRORCODE = 1 ERRORCODE_END $ Solution ========= s_instLocalListener is not correct in database server context file 1. Take Backup of Context…
Read MoreERROR: ORA-17627: ORA-01041: internal error. hostdef extension doesn’t exist ORA-19849: error while reading backup piece from service PROD ORA-03113: end-of-file on communication channel ORA-19558: error de-allocating device ORA-19557: device error,…
Read More1) Log in as the user that owns the application tier installation (this is usually applmgr or oracle) 2) Source the run file system environment and the $FMW_HOME/SetWebtier.env file Note:…
Read MorePlease find the steps done below for one type of block but can be used for any block. create table demo.wcc99(n number, c varchar2(4000)) nologging tablespace DISC pctfree 99…
Read MorePlease run the below command from <Agent base>/agent_inst/bin of the agent monitoring the target host. ./emctl config agent addinternaltargets ./emctl secure agent ./emctl config agent listtargets ./emctl stop agent ./emctl secure agent…
Read MoreTransferring statistics between database In general, development DB usually will have only portion of the data when we compared to Production database. In such a scenario, when we fix any production issues, obviously we make the changes in Dev DB and test the code and move to Prod DB. While testing the code in Dev DB, if we want to compare the execution plan between Dev and Prod, then we can copy the Prod DB statistics into Dev DB and forcast the optimizer behaviour in development server. DBMS_STATS has an ability to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. Source database : orcl Source schema : sales Target database : oradev Target schema : sales Now our goal is to copy the statistics from sales@orcl to sales@ordev. step1. First create a stat table in the source database. The statistics table is created in SYSTEM schema. SQL> connect system/password@orcl Connected. SQL> EXEC DBMS_STATS.create_stat_table(‘SYSTEM’,’STATS_TABLE’); PL/SQL procedure successfully completed. SQL> Step2. Export the sales schema statistics. SQL> EXEC DBMS_STATS.export_schema_stats(‘SALES’,’STATS_TABLE’,NULL,’SYSTEM’); PL/SQL procedure successfully completed. SQL> Step3. Export the STATS_TABLE by using expdp or exp utility and move the dump file to target(ordev) server. Step4. Import the dump file into target database by using impdp or imp utility. Here i imported the dump file in system schema at target server. Step5. Import the statistics into application schema(sales@ordev). Please remember, previous step, we imported the stats_table content into system schema by using impdp method. But this step, we are importing the statistics into relevant data dictionary table by using dbms_stats pacakge. SQL> EXEC DBMS_STATS.import_schema_stats(‘SALES’,’STATS_TABLE’,NULL,’SYSTEM’); PL/SQL procedure successfully completed. SQL> Step6. Drop the stats_table in target server. SQL> EXEC DBMS_STATS.drop_stat_table(‘SYSTEM’,’STATS_TABLE’); PL/SQL procedure successfully completed.…
Read MoreHistogram Stats in Columns The METHOD_OPT parameter is probably the most misunderstood parameter in the DBMS_STATS.GATHER_*_STATS procedures. It’s most commonly known as the parameter that controls the creation of histograms Histogram is collecting statistics on columns for better query selectivity and optimal execution plan The METHOD_OPT parameter syntax is made up of multiple parts. The first two parts are mandatory and are broken down in the diagram below. FOR ALL INDEXED COLUMNS limits base column gathering to only those columns that are included in an index. FOR ALL HIDDEN COLUMNS limits base column statistics gathering to only the virtual columns that have been created on a table. FOR ALL COLUMNS is column statistics gathering on all columns in the table The SIZE part of the METHOD_OPT syntax controls the creation of histograms AUTO means Oracle will automatically determines the columns that need histograms based on the column usage information (SYS.COL_USAGE$) SKEWONLY automatically creates a histogram on any column that shows a skew in its data distribution. An integer (SIZE) value indicates that a histogram will be created with at most the specified number of buckets. Must be in the range [1,254]. To force histogram creation it is recommend that the number of buckets be left at 254. Note SIZE 1 means no histogram will be created. Collecting histograms stats for only a set of columns Example: Below is the DBMS_STATS.GATHER_TABLE_STATS command in action. The only column that has a histogram created on it is the CUST_ID, even though several of the columns in the SALES table were used in the where clause of queries executed on the system. BEGIN dbms_stats.Gather_table_stats(‘SH’, ‘SALES’, – method_opt => ‘FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID’); END; / PL/SQL procedure successfully completed. SELECT column_name, num_distinct, histogram FROM user_tab_col_statistics WHERE table_name = ‘SALES’;
Read MoreHow to create SQL profiles using the SQLT utility. Oracle has a utility called SQLT to create profiles. To create profiles we do not need to install this utility it can be done by downloading and unzipping the utility. Refer to metalink note 215187.1. Once the utility is unzipped, you can call the profiler function by running this SQL. You need to be logged in as SYS. @sqlt/utl/coe_xfr_sql_profile.sql SQL> @sqlt/utl/coe_xfr_sql_profile.sql Parameter 1: SQL_ID (required) This will ask sqlid as input parameter. Once the sqlid is provided, it will list all the plan hash values and their elapsed times. Enter value for 1: crj0huynfjdz5 PLAN_HASH_VALUE AVG_ET_SECS ————— ———– 129850372 248.461 3675459451 2151.575 664690662 14074.004 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: Once the plan hash value is given, it creates the files necessary to create the profile. Enter value for 2: 129850372 Execute coe_xfr_sql_profile_crj0huynfjdz5_129850372.sql on TARGET system in order to create a custom SQL Profile with plan 129850372 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed. SQL> If you execute the file coe_xfr_sql_profile_crj0huynfjdz5_129850372.sql, the profile for that hash value is created.
Read More