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

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

How to avoid functions being called twice in a single sql-statement?

Tom Kyte - Wed, 2019-04-10 16:26
Hi We have merge statements which get their values from non deterministic functions. Some of the updates merge does are not 'real' in a sence that actual values do not change. We would prefer so that these updates are not made at all but do not...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs