DBA Blogs

Custom pivot with count and sum summaries and horizontal sorting

Tom Kyte - Fri, 2018-04-06 23:26
Hello Team, Good Day! I have linked livesql script for data creation. Data basically looks like this. <code> 1 symnum NUMBER 22 2 symname VARCHAR2 100 3 remnum NUMBER 22 4 remname VARCHAR2 32 5 grade NUMBER 22 </code> ...
Categories: DBA Blogs

Move data within partitions

Tom Kyte - Fri, 2018-04-06 23:26
Hi Tom. I'm working on a database with a partitioned table (by date). There are weekly partitions plus a edge partition where values higher than a certain value are stored. The last partition has never been used and is empty. Counting recor...
Categories: DBA Blogs

Soundex

Tom Kyte - Fri, 2018-04-06 05:06
Buen dia Connor, espero estes bien. Estoy realizando una practica e investigacion sobre chatbots, en mi algoritmo estoy buscando por dos metodos el parseo o proceso de las palabras para hacer el NLP y darles sentido, se que existe la funcion soun...
Categories: DBA Blogs

How to start a job on two conditions with DBMS_SCHEDULER : another job has finished and we are on monday ?

Tom Kyte - Fri, 2018-04-06 05:06
Hi, I have a job (JOB1) which runs every night at 0:30. I have another job (JOB2) which must be run after JOB1 completes but only once a week, on monday. For the moment, I manage this by starting JOB2 on monday at 4:00 because most of the time J...
Categories: DBA Blogs

Oracle EMEA Identity & Security Partner Forum 2018 #OPNCloudSecurity

          ...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Question: Anything Wrong With Query Performance? (Straight To You)

Richard Foote - Thu, 2018-04-05 00:21
I have a query that runs pretty darn efficiently, here’s the setup: So the query basically returns 1000 rows based on the CODE column and it does so using an index on CODE. The CBO has got the costings for this just about spot on. For 1000 rows returned, it does so with just 1006 […]
Categories: DBA Blogs

Virtual Workshop: Developing Microservices Using DevOps

Get Hands On with your Oracle Cloud Trial The Oracle Cloud Developing Microservices using DevOps workshop will walk you through the Software Development Lifecycle (SDLC) for a Cloud Native...

We share our skills to maximize your revenue!
Categories: DBA Blogs

To generate list of unique Random Numbers based on the number count required

Tom Kyte - Mon, 2018-04-02 09:46
Hi, My requirement is to generate the list of random numbers based on the total count provided. for instance, if total count is 100, i have to generate 100 unique random numbers. Below is the sample code i used. Could you please check and let me ...
Categories: DBA Blogs

Group by is failing on remote database query

Tom Kyte - Mon, 2018-04-02 09:46
Hello, My issue is the good old ORA02070 and I've found different workarounds but none of them fits my particular problem. It happens when I try to do a group by on remote database query. I need to compare data between the local and remote dat...
Categories: DBA Blogs

DB stuck at check point not complete.

Tom Kyte - Mon, 2018-04-02 09:46
Our DB stuck and check poin not complete. As per the logs DBA says.. ELECT for update running on the db causing the high number of block changes and caused the issue After this issue we are unable to generate ASH or AWR for that time. How to iden...
Categories: DBA Blogs

Not all standby Redo logs are being used in Physical standby database

Tom Kyte - Mon, 2018-04-02 09:46
Hi I wanted to know how does Oracle physical standby database decides how many standby Redo to be used. As We have a Standby which have multiple Standby Logs but its is only using 2 of them for each thread. We tried to switch the logs on both...
Categories: DBA Blogs

character set us7ascii missing from dbca

Tom Kyte - Mon, 2018-04-02 09:46
I am trying to create a database in oracle 12.1.0.2 using dbca. THe character set us7ascii is not in the list of character sets.
Categories: DBA Blogs

New functions not yet documented - TO_DOG_YEAR

Tom Kyte - Sun, 2018-04-01 15:26
Over the past few months I've been working on a project using oracle advanced analytics to detect IBS in Dogs. I've written a short blog post about this here (http://www.oralytics.com/2018/04/predicting-ibs-in-dogs-using-oracle-18c.html) I've noti...
Categories: DBA Blogs

BIG NEWS: I’ve Been Selected On “Journey To Mars” Program !!

Richard Foote - Sat, 2018-03-31 17:56
After many months of nervous waiting, knowing that my chances were extremely slim at best, I have just received my confirmation letter from NASA saying that I’ve been one of the lucky ones selected to participate in the “Journey To Mars” program !! I’m soooo damn excited !!! Planning is now under way for what […]
Categories: DBA Blogs

CPU utilization

Tom Kyte - Fri, 2018-03-30 08:46
How can I reduce CPU utilization of Oracle database?
Categories: DBA Blogs

Oracle DBAs and Meltdown & Spectre (M&S) vulnerability Patch

Pakistan's First Oracle Blog - Thu, 2018-03-29 21:01
So what Oracle DBAs need to do regarding Meltdown & Spectre (M&S) vulnerability patch? 



Well, they should ask the sysadmins to install the patch to the affected versions. They need to get a maintenance window for that. They need to take full backup of Oracle infrastructure and databases before that patching and they should get some baseline of OS metrics to compare it with post patch status of the system. 

Not much there is to do for Oracle DBAs in this regard as this vulnerability is in hardware and is mainly related to sysadmins. Nonetheless, Oracle DBAs should avail this opportunity and install latest CPU. 

The vulnerability is in the chipset itself, unrelated to OS. These vulnerabilities exist at the hardware layer and provide attackers with a way to essentially read the memory used by other processes. Because of the nature of this exploit, the database itself is not currently thought to be a vector in the risk, in-fact the real "fix" for this issue relies on fixing architecture at the chip-set level. 

To mitigate the risk currently without replacing your chips, OS vendors are releasing patches that fundamentally change interactions with memory structures by processes. This is why we're seeing in "Addendum to the January 2018 CPU Advisory for Spectre and Meltdown (Doc ID 2347948.1)" Oracle is releasing patches for Oracle VM (virtual machines are particularly susceptible to this exploit as one "VM" can read the memory processes of another, making this particularly deadly to cloud computing) and Oracle Enterprise Linux. We do understand that Oracle is exploring the possibility that there may be additional patches needed for Oracle Enterprise and Standard edition DBs themselves.


Only for Exadata, It is needed to apply the latest Exadata 12.2.1.1.6 software bundle (the full version number is 12.2.1.1.6.180125.1). Spectre / Meltdown patches are included into it.


The best course of action regarding this would be to get a word from Oracle support for any database related patch. 

Categories: DBA Blogs

Move a Datafile from one ASM Diskgroup to Another Diskgroup

Pakistan's First Oracle Blog - Thu, 2018-03-29 20:49
Following are steps to move a datafile from one ASM diskgroup to another diskgroup in the same ASM instance:




For this example, let's suppose the full path of datafile to be moved is +DATA/test/datafile/test.22.121357823 and datafile number is 11.

Step 1: From RMAN, put datafile 11 offline:

SQL "ALTER DATABASE DATAFILE ''+DATA/test/datafile/test.22.121357823'' OFFLINE";

Step 2: Backup Datafile 11 to Copy using RMAN:

$ rman target /
BACKUP AS COPY DATAFILE 11 FORMAT '+DATA_NEW';

--- Make note the path and name of the generated datafile copy.

Step 3: From RMAN, switch datafile 11 to copy:

SWITCH DATAFILE "+DATA/test/datafile/test.22.121357823" TO COPY;

Step 4: From RMAN, Recover Datafile 11:

RECOVER DATAFILE 11;

Step 5: From RMAN, put datafiles online:

SQL "ALTER DATABASE DATAFILE ''+DATA_NEW/'' ONLINE";

Step 6: From SQLPlus, verify if datafile 11 was correctly switched and was online:

sqlplus / as sysdba
SQL> select file_id,file_name,online_status from dba_data_files where file_id in (11);
Categories: DBA Blogs

SELECT INTO clause variable initialization when Too Many Rows exception raised

Tom Kyte - Wed, 2018-03-28 20:06
Hi, I created a Table and inserted two rows with duplicate values in 2 columns. I written an anonymous block to (SELECT INTO)fetch the data into 3 variables by passing 2 Values in WHERE Clause, but the values in these columns are not unique so ...
Categories: DBA Blogs

Using resource manager group to restrict DOP

Tom Kyte - Wed, 2018-03-28 20:06
Hi, We are planning to user Oracle Resource Manager Group to restrict DOP(degree of parallelism) to some fixed number in our database. We have around 10 schemas and we need to restrict DOP in those schemas. There is definitely benefit by using the...
Categories: DBA Blogs

Filling nulls in hierarchical query

Tom Kyte - Wed, 2018-03-28 20:06
Recently in the Oracle magazine of nov-06 you explained how to fill up null values with the previous non-null value of a column. I got a similar problem, but on hierachical query. I need to fill up null columns from its parent non-null value instea...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs