DBA Blogs

nulls with NOT and booleans

Tom Kyte - Sun, 2018-03-04 08:26
Hi Tom, I've always understood it to be the case that a=b and a!=b both evaluate to FALSE if one or both is NULL. However, I'm seeing some odd behavior which I'm trying to distill and understand. <code> DECLARE v_Var1 VARCHAR2(12) := 'A'; ...
Categories: DBA Blogs

Problem with Oracle 11g at startup

Tom Kyte - Sun, 2018-03-04 08:26
Hello, i have problem with my Oracle 11G XE I can log into db with : sqlplus "sys as sysdba" Then to modify : alter system set processes=5000 scope=spfile; I do : shutdown immediate; And then startup but receive this error : ORA-04031: ...
Categories: DBA Blogs

Analytical function

Tom Kyte - Sun, 2018-03-04 08:26
Hi TOM group, Since yesterday I read the new version of Oracle 18c some of the elements like Analytical functions(As you said calculations), while reading I didn't find any examples to better understand about it like Rank_*...etc. I read the only the...
Categories: DBA Blogs

Pivoting via SQL

Tom Kyte - Sun, 2018-03-04 08:26
Hai, Tom sir...I have table with columns like this...I have 6 columns in my table with data in below.I need to write an SQL code to get the in below output.Out i mentioned at the ending.Please look at and give me the sol. Create table t3 ( ...
Categories: DBA Blogs

SQL with Apache Spark, easy!

Kubilay Çilkara - Sat, 2018-03-03 03:10
Reading about cluster computing developments like Apache Spark and SQL I decided to find out.

What I was after was to see how easy is to write SQL in Spark-SQL. In this micro-post I will show you how easy is to SQL a JSON file.

For my experiment I will use my chrome_history.json file which you can download from your chrome browser using the extension www.JSON-XLS.com. To run the SQL query on PySpark on my laptop I will use the PyCharm IDE. After little bit of configuration on PyCharm, setting up environments (SPARK_HOME), there it is: It only takes 3 lines to be able SQL query a JSON document in Spark-SQL.

(click image to enlarge)



Think of the possibilities with SQL, the 'cluster' partitioning and parallelisation you can achieve

Links:
Apache Spark: https://spark.apache.org/downloads.html
PyCharm: https://www.jetbrains.com/pycharm/
Categories: DBA Blogs

Check row was skipped by FOR UPDATE SKIP LOCKED

Tom Kyte - Fri, 2018-03-02 19:46
Hi Tom, Logic of the SP is to return one Batch ID at a time to application for processing and same Batch ID should not be returned back to other instance if that batch is picked by an instance. And the application server is in a multi clustered en...
Categories: DBA Blogs

Wait event acknowledge over PGA limit

Tom Kyte - Fri, 2018-03-02 19:46
Hi Tom, we just upgraded our RAC oracle (two nodes) from 12.1 to 12.2. During our load testing, we found the top wait event is acknowledge over PGA limit. I increased pga_aggregate_target from 500m to 2g. Still saw this wait event. I checke...
Categories: DBA Blogs

Can't create materialized view using left join and group by

Tom Kyte - Fri, 2018-03-02 19:46
I'm trying to create a materialized view that joins two tables then groups by various ids but I keep running into this error: <code>ORA-12015: Neither ROWIDs and nor primary key constraints are supported for queries.</code> I tried this: ...
Categories: DBA Blogs

Error in Encrypting a CLOB data type using dbms_crypto package

Tom Kyte - Fri, 2018-03-02 19:46
Hi Tom, I've shared a function's script which basically tries to encrypt a CLOB datatype using the bdms_crypto package. The function is created successfully. But it throws me a run time error stating 'ORA-01405: fetched column value is NULL'. I...
Categories: DBA Blogs

Oracle GoldenGate Application Adapters compatible with trails release 12.3

Tom Kyte - Fri, 2018-03-02 01:26
Hi, Is there a release available of Oracle GoldenGate Application Adapters that is compatible with trail file format 12.3 (for Linux x86-64)? On OTN, GoldenGate Application Adapters release 12.2 is downloadable, however, this release requires set...
Categories: DBA Blogs

Datapump SQL minitoring with SQL (not PL/SQL)

Tom Kyte - Fri, 2018-03-02 01:26
I'm on 11.2 and 12.1 and would like to monitor datapump sessions with pure SQL (I am not allowed to create objects on the databases). I can monitor using: <code> select ss.sid , s.job_name , j.state --, j.job_mode --, j.degree , dbms_rcvm...
Categories: DBA Blogs

Node.js vs PL/SQL

Tom Kyte - Fri, 2018-03-02 01:26
We currently have a read-only application written using MOD PL/SQL. Our agile development group is recommending using angular JS for the AX/AI development, and NodeJS on a Happi framwork. the database is currently Oracle 11.2.0.4. We hope to upgrade ...
Categories: DBA Blogs

Timestamp of oracle and ORA-30088: datetime/interval precision is out of range

Tom Kyte - Fri, 2018-03-02 01:26
Hi Tom I am trying to store timestamp as below. Timstmp(12) YYYY-MM-DD-hh.mm.ss.nnnnnnnnnnnn So i am trying to create the table as below. <code>create table t23 (ts0 timestamp(0) , ts3 timestamp(3) , ts6 timestamp(6) ...
Categories: DBA Blogs

Conditional compilation in query

Tom Kyte - Fri, 2018-03-02 01:26
I am trying to use conditional compilation in a sql query of a cursor. Below is the code <code>create or replace procedure p_env_check(i_env in number, o_cur out sys_refcursor) is begin open o_cur for...
Categories: DBA Blogs

Mview creation from view

Tom Kyte - Thu, 2018-03-01 07:06
The MV creation using a view is forcing to use minimum 1 table even if that table is a dummy. Creating MV directly on view is failing with ORA error ORA-12015. Further if I were to use DUAL instead of dummy table it gives a different ORA error ORA...
Categories: DBA Blogs

calculate how many minutes employee worked for a day in hourly breakup

Tom Kyte - Thu, 2018-03-01 07:06
I am having data like Empcode attdate intime outtime 12345 03-JAN-2018 00:00:00 03-JAN-2018 10:56:00 03-JAN-2018 16:44:00 12345 03-JAN-2018 00:00:00 03-JAN-2018 20:00:00 03-JAN-2018 23:...
Categories: DBA Blogs

Getting started with Performance Diagnosis

Tom Kyte - Thu, 2018-03-01 07:06
Mostly we don't get database performance problem, so never felt a need to learn much about performance. Oracle take good care of itself. But one day db server got restarted, then i got performance problem calls from users. At this point i got bli...
Categories: DBA Blogs

Database hang and we cannot switch logfile or shutting down the DB

Tom Kyte - Thu, 2018-03-01 07:06
Hi Experts, During the issue time database hang and we cannot switch logfile or shutting down using IMMEDIATE. All the Redo logfiles are showing status ACTIVE and still its showing ACTIVE after 1 Week. \ Why the Redo logfile is still ACTIVE...
Categories: DBA Blogs

Error on auto execute of job

Tom Kyte - Thu, 2018-03-01 07:06
i am getting this error everyday at 10 AM everyday kindly help me on this ,thanks in advance Errors in file /u01/TBCCDDB/diag/rdbms/tbccd/TBCCD/trace/TBCCD_j003_19550.trc: ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_3196" ORA...
Categories: DBA Blogs

Statistics on Materlized View taking plus 10 hours

Tom Kyte - Thu, 2018-03-01 07:06
I'm trying to update the stats on a materlized view I created: <code> BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => 'CSPRD', tabname => 'mv_llattrdata_shrinked', cascade ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs