DBA Blogs

Does the context switch account for the recursive calls

Tom Kyte - Sun, 2017-11-12 14:26
Hi Tom, Here is what i did trying to understand the enhancements of 12c. Here i was trying to understand the enhancements of WITH clause. I have created the table and compiled the below function. <code> CREATE TABLE lnd_numbers AS SELECT ...
Categories: DBA Blogs

In sql how can I update a value , and then reuse the updated value and re-update it

Tom Kyte - Sun, 2017-11-12 14:26
Hi Gurus I need to write in SQL something which previously was done in PL/SQL if possible. I have a Invoice Line Description e.g. 'ABC Mon Tue' for which I need to translate certain words. I also have a lookup(fnd_lookups) which stores the...
Categories: DBA Blogs

Versioning Data Model

Tom Kyte - Sat, 2017-11-11 01:46
Hi AskTom team, I'd like your ideas about the data model design and/or Oracle features that I could take advantage of to achieve the design goals described below. <u>Background:</u> I'm in the early stages of designing a data model for a bra...
Categories: DBA Blogs

Union all query missing lines

Tom Kyte - Sat, 2017-11-11 01:46
Hello Tom and Tom, Linked live sql shows a condensed and "moved-to-dual" query we are using with a far resemblance on our database. It's a couple of nested "union all" statements, where we would expect the outermost union (UNION2) to deliver the u...
Categories: DBA Blogs

Grant select on a View with grant option does not work

Tom Kyte - Sat, 2017-11-11 01:46
Hi, I have Schema_1 that owns table_1, table_2, table_3. Schema_1 creates View_1 using table_1, Schema_1 Creates View_2 using table_2, Schema_1 Creates View_3 using table_3. Schema_2 Creates View_4 using View_1, View_2 and View_3. Then ...
Categories: DBA Blogs

Identify patterns and create groups

Tom Kyte - Fri, 2017-11-10 07:35
I have data that looks like this: <code>create table t (a varchar2(30), b date); insert into t values (NULL,TO_DATE('2003/05/03 16:02:44', 'yyyy/mm/dd hh24:mi:ss')); insert into t values (NULL,TO_DATE('2003/05/03 17:02:44', 'yyyy/mm/dd hh24:mi...
Categories: DBA Blogs

optimistic search for most recent records

Tom Kyte - Fri, 2017-11-10 07:35
Hi, I have very large table which constantly grows. The search is executed by ID column, which is part of PK. <code> create table TEST ( ID varchar2(20) primary key, VALUE varchar2(20), CREATED_TS timestamp default := systimes...
Categories: DBA Blogs

selecting table column based on lookup table

Tom Kyte - Fri, 2017-11-10 07:35
Hi I am trying to get columns from a table only if that column value is set as "YES" in another lookup table. Please help me to get the query for the same. I have a lookup table like this: create table cust_bug_lookup(Title varchar2(100), ...
Categories: DBA Blogs

Partitioned table performance

Tom Kyte - Fri, 2017-11-10 07:35
We have a partitioned table with more than 200 columns and 60 indexes. It has 10 foreign keys with related indexes and the remaining indexes are global style. It partitioned in a yearly basis and sub-partitioned in company. Now, we're have perfor...
Categories: DBA Blogs

there is a Bug using MERGE and DUAL together

Tom Kyte - Fri, 2017-11-10 07:35
Consider please the follwing simple table: <code>create table table_1 (c1 varchar2(100), c2 varchar2(100));</code> If we apply the following MERGE command now (attend please the WHERE clause), we get: <code> merge into table_1 tb using (se...
Categories: DBA Blogs

How to hire a Lead Oracle DBA

Tom Kyte - Fri, 2017-11-10 07:35
Hi I'm a Junior Oracle DBA in the new company that I joined in. Our Lead Oracle DBA resigned and my company is screening for new applicants. The boss of our department might ask me to interview the potential Oracle Lead DBA candidate and a...
Categories: DBA Blogs

Formatting negative values to sort correctly but keep the formatting

Tom Kyte - Fri, 2017-11-10 07:35
I have an old and a new query. I need help with the new one. The old query works fine. For the new one, I can't seem to find a way to format two columns (latitude and longitude, I need 6 digits after the decimal) in such a way as they sort correctly....
Categories: DBA Blogs

Oracle Block Size

Tom Kyte - Thu, 2017-11-09 10:06
Hi Tom, I would be very grateful if you could share your thoughts on Oracle block size. "rule of thumb" is Oracle Database block sizes (2 KB or 4 KB) for online transaction processing (OLTP) or mixed workload environments and larger block size...
Categories: DBA Blogs

Where clause mix of AND and OR with()

Tom Kyte - Thu, 2017-11-09 10:06
Hello, I need to mix and or in where cluse: like: and con1 and (con2 or con3 or con4)... t_where := t_where || ' and a.field1 = ''' || l_1 || '''' || ' ( ' || 'a.field2 = ''' || l_2 || '''' || ' or ' || ......
Categories: DBA Blogs

sql loader and date

Tom Kyte - Thu, 2017-11-09 10:06
hi!!! i am using sqlloader, i have a table T in my database T (empno, start_date date, resign_date date) my data file has data like this (date format IN THE DATAFILE is 'YYYYMMDD') 1, 19990101,20001101 2, 19981215,20010315 3, 19950520...
Categories: DBA Blogs

Tabuler row no

Tom Kyte - Thu, 2017-11-09 10:06
In oracle forms 12c, I created data block with tabula, I changed the otem row number displayed to 10 row, I need to get current selected row no in this tabuler on form at run time not current_record, O mean this number between 1 to 10, I tried get ...
Categories: DBA Blogs

SGA_target is greater than the total physical memory on the server (Windows)

Tom Kyte - Thu, 2017-11-09 10:06
HI Tom, We have a databases running on 11.2.0.3 with memory parameters set as below: This is a windows server 2008 R2. SQL> show parameter sga NAME TYPE VALUE ------------------------------------ --...
Categories: DBA Blogs

What´s your oppinion about the DBA job in the future

Tom Kyte - Thu, 2017-11-09 10:06
Hi Tom, First, i would like to thank you for your site in web. I learn a lot of things with the doubts of my coleages registered in this site. I learn too how to explain to the developers using examples (like you). Well, i would like to know wh...
Categories: DBA Blogs

List of Networking Concepts to Pass AWS Cloud Architect Associate Exam

Pakistan's First Oracle Blog - Wed, 2017-11-08 16:31
Networking is a pivotal concept in cloud computing. Knowing it is a must to be a successful Cloud Architect. Of course you won't be physically peeling the cables to put RJ45 connectors on but you must know various facets of logical networking.


You never know what exactly gonna be in the exam but that's what exams are all about. In order to prepare for AWS Cloud Architect Associate exam you must thoroughly read and understand the following from AWS documentation:


Before you read above, it would be very beneficial if you also go and learn following networking concepts:

  • LAN
  • WAN
  • IP addressing
  • Difference between IPV4 and IPV6
  • CIDR
  • SUBNET
  • VPN
  • NAT
  • DNS
  • OSI Layers
  • TCP
  • UDP
  • ICMP
  • Router, Switch
  • HTTP
  • NACL
  • Internet Gateway
  • Virtual Private Gateway
  • Caching, Latency
  • Networking commands like Route, netstat, ping, tracert etc
Feel free to add any other network concept in comments which I might have missed.
Categories: DBA Blogs

IS JSON is not working for Nested Jsons

Tom Kyte - Wed, 2017-11-08 15:46
Hi Team, In one of our table we have column which holds JSON format text. data will be inserted to this column from a file we receive from Vendor. While Inserting the data to this column we dont't validate whether its in JSON format or not but bef...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs