DBA Blogs

Help with v$statname and v$sysstat

Tom Kyte - Tue, 2019-04-16 19:06
Tom, Can you please provide info on how can I find the full table scan and index table scan activities in the database using v$statname and v$sysstat? Do I need to set TIMED_STATISTICS=TRUE before running queries against v$sysstat?...
Categories: DBA Blogs

Check your hints carefully

Bobby Durrett's DBA Blog - Tue, 2019-04-16 16:32

Back in 2017 I wrote about how I had to disable the result cache after upgrading a database to 11.2.0.4. This week I found one of our top queries and it looked like removing the result cache hints made it run 10 times faster. But this did not make sense because I disabled the result cache. Then I examined the hints closer. They looked like this:

/*+ RESULT CACHE */

There should be an underscore between the two words. I look up hints in the manuals and found that CACHE is a real hint. So, I tried the query with these three additional combinations:

 
/*+ RESULT */
 
/*+ CACHE */
 
/*+ RESULT_CACHE */

It ran slow with the original hint and with just the CACHE hint but none of the others. So, the moral of the story is to check your hints carefully because they may not be what you think they are.

Bobby

Categories: DBA Blogs

What is the Cloud?

VitalSoftTech - Tue, 2019-04-16 09:52
Cloud technology is a word that is being used a lot when it comes to online services. The term, which refers to a network of online servers, is more than just a buzz word. Companies are quickly discovering that cloud functions can help them run applications, deliver services, or simply store their extra data. Most […]
Categories: DBA Blogs

Create Object with Column type attributes

Tom Kyte - Mon, 2019-04-15 06:26
Hi Tom, Please help me on one of our prod issue. We have an object and all the attributes in the object(EMP_OBJ) are columns of a table(Lets say EMP) in diff schema. So when we defined the object we gave the datatype and size of the object attr...
Categories: DBA Blogs

STATS_BINOMIAL_TEST does not work

Tom Kyte - Mon, 2019-04-15 06:26
Dear Tom, I tried to run this query, following example here https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions150.htm: <code></code> SELECT AVG(DECODE(cust_gender, 'M', 1, 0)) real_proportion, STATS_BINOMIAL_TEST ...
Categories: DBA Blogs

issue with exponent value with number column

Tom Kyte - Mon, 2019-04-15 06:26
Hi , i am summing up the number column based other columns. But while doing sum small value converted into exponent. for exp. to_comm Number(10); 0.0000474 converted into 4.74E-5. I can change the setting of client to see small value ...
Categories: DBA Blogs

Insert trigger that do an update if record exists

Tom Kyte - Mon, 2019-04-15 06:26
I have a table: <code>create table test_tbl (id number, text varchar2(50));</code> with this data in it: <code>insert into test_tbl values (1,'Text 1'); insert into test_tbl values (2,'Text 2');</code> Now I want to insert a record, but ...
Categories: DBA Blogs

ORA-14692: STORE AS clause is not allowed for extended character type column

Tom Kyte - Mon, 2019-04-15 06:26
I just want to exp and imp,but when I imp the dmp file to another database,the failer message occured below: ORA-14692: STORE AS clause is not allowed for extended character type column. what should I do?
Categories: DBA Blogs

setting isolation level after gather stats worked; but did not work before it. Why ?

Tom Kyte - Mon, 2019-04-15 06:26
Hi, Please see below :- <code> SQL> create table t (x int); Table created. SQL> insert into t values (1); 1 row created. SQL> alter session set isolation_level=serializable; ERROR: ORA-01453: SET TRANSACTION must be first stateme...
Categories: DBA Blogs

global index in partitioned table

Tom Kyte - Fri, 2019-04-12 05:06
I have a question about a global index on a history table that has been partitioned into weeks from 1 to 53 with subpartitions from 1 to 4 all this into a list type partitioning. The question is that local and global indexes have been created. What w...
Categories: DBA Blogs

Update an ordered list value with consecutive numbers

Tom Kyte - Fri, 2019-04-12 05:06
I need to reset an ordered list to be consecutive numbers (ints) while maintaining the original ordering. 2,4,6 needs to become 1,2,3, as does -6, 53, 5498. I tried using rownum: <code>update T1 set SIBLING_ORDER = rownum where PARENT_ID...
Categories: DBA Blogs

Configuration of redo log and standby redo log in single instance standby database for RAC primary database

Tom Kyte - Fri, 2019-04-12 05:06
Hello, I would like your support to configure correctly at the level of redo log and standby redo log in a single instance standby database, whose primary base is RAC, so that there is no inconvenience when performing the change of roles. At pres...
Categories: DBA Blogs

Getting lowest record from duplicates

Tom Kyte - Fri, 2019-04-12 05:06
Hi Tom, Following is the View definition. <code>SELECT C.VERSION, C.DOW, C.DELV_TYPE_CODE, C.CURR_DELVPT_SYS_ID, C.EMP_ID, C.ZIP5, C.ZIP4, C.ZIP2, ...
Categories: DBA Blogs

ServiceManager Daemon fails to start on reboot?

DBASolved - Thu, 2019-04-11 21:23

If you have been working with Oracle GoldenGate 12c (12.3.0.1.x) or 18c (18.1.0) recently, you may have setup the ServiceManager as a daemon process. This is a great option for setting up the ServiceManager. The benefit is provides is when you host is rebooted it comes back online and allows you to see all of your managers; however, there is a small issue with the process … not really the process but the environment where it runs.

If you are like me, you like to setup your environment variables for quick reference; I do this with $OGG_HOME a lot, especially setting up new enviornments. Having this enviornment variable set actually causes a problem with the ServiceManager. If the $OGG_HOME variable is set for the Oracle user environment; the ServiceManager will not restart upon reboot. This leads you to try to execute ServiceManager from the $OGG_HOME/bin directory, like so:

$ cd $OGG_HOME/bin
$ ./ServiceManager &

When you do this, you will be met with a message similar to this:

$ Service Manager is terminating because it cannot load the inventory from ‘/opt/app/oracle/product/18.1.0/oggcore_1/etc/conf/deploymentRegistry.dat

So what does this message mean? The ServiceManager is trying to find the deploymentRegistry.dat file. This file has all of the configurations that ServiceManager needs to start (BTW – DO NOT EDIT THIS FILE DIRECTLY). You will also notice that the directory structure after $OGG_HOME is not correct. That is because the deploymentRegistry.dat file is located in the $DEPLOYMENT_HOME for the ServiceManager.

So how do you fix this issue? The answer is quite simple. Just unset $OGG_HOME within the envrionment. Then ServiceManager can be started from $OGG_HOME/bin. If you remove/rename $OGG_HOME from your oracle profile (.bash_profile, .bashrc, etc…), the ServiceManager will restart on reboot as well.

When starting the ServiceManager from $OGG_HOME/bin, the output should look something similar to this:

[oracle@db18c_ogg18c bin]$ ./ServiceManager &
[1] 464
[oracle@db18c_ogg18c bin]$ Oracle GoldenGate Service Manager for Oracle
Version 18.1.0.0.0 OGGCORE_18.1.0.0.0_PLATFORMS_180928.0432

Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.

 

Linux, x64, 64bit (optimized) on Sep 28 2018 17:31:51
Operating system character set identified as US-ASCII.

 

[1]+ Done ./ServiceManager

With the ServiceManager stared, you can now access the HTML5 web page or the associated REST APIs.

Enjoy!!!

Categories: DBA Blogs

Regular expression to find rows with characters that are not letters, numbers or keyboard symbols

Tom Kyte - Thu, 2019-04-11 10:46
Hi, This is the table t2 I have, Sl.No. Junk 1. Cigarette use ? last used 4/2017 ? NS at best; 2. test]]]]]]] 3. [[[[test 4. [CDATA[]] Now I want to write query to get only the 1st row which have junk chara...
Categories: DBA Blogs

How lob columns are transferred by Oracle Net Services.

Tom Kyte - Thu, 2019-04-11 10:46
Hi Oracle manual states: 'Starting with Oracle Database 11g, Oracle Net Services optimized bulk data transfer for components, such as Oracle SecureFiles LOBs and Oracle Data Guard redo transport services. The SDU size limit, as specified in the n...
Categories: DBA Blogs

How to check the Table structure of both the data bases & merge into single database.

Tom Kyte - Thu, 2019-04-11 10:46
Two Banks are running on Oracle database for the same application software. Now those 2 Banks merged and need to merge the Oracle Databases also.. How to check the Table structure of both the data bases & merge into single database.
Categories: DBA Blogs

Order of Update statements

Tom Kyte - Wed, 2019-04-10 16:26
Hello, Thanks for taking up this question. I have noticed a strange behavior in Oracle database (11g R2). The database I am working on has 2 instances (RAC implementation). I am executing a script containing multiple update statements. They ar...
Categories: DBA Blogs

Execute procedure in anonymous block returns ORA-06550 & PLS-00222

Tom Kyte - Wed, 2019-04-10 16:26
Hi, I created these objects: ------------------------------- <code> create table mwallet.tb_test (test_id number GENERATED ALWAYS AS IDENTITY, test_name varchar2(50) ); </code> ------------------------------- <code> create or replace pr...
Categories: DBA Blogs

How would you implement this? Returning name format based on country code

Tom Kyte - Wed, 2019-04-10 16:26
Here's the situation. System is used in several countries. User logs in and can only be at one country at a time. There's id in the system, which belongs to one country. There are functions/procedure in the system, which are country specific. F...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs