DBA Blogs

Can you shed some light on this ora error [session idle bit]

Tom Kyte - 2 hours 36 min ago
SYS@XYZ> select sum(ksmchsiz) ||' bytes' "ToSHRPOOLMem" from x$ksmsp; ^C ^C select sum(ksmchsiz) ||' bytes' "ToSHRPOOLMem" from x$ksmsp * ERROR at line 1: ORA-00603: ORACLE server session ...
Categories: DBA Blogs

How to restore a packages from rman backups

Tom Kyte - 2 hours 36 min ago
Hello, Is it possible to restore packages from rman backups? I know the export method that can do it, but I want to know if extended rman can do such a thing?
Categories: DBA Blogs

Encryption

Tom Kyte - 2 hours 36 min ago
HI Tom - facing this issue of incorrect string sizing while decrypting.Usage of trim doesnt solve the issue. Below are the codes. 1.CREATE OR REPLACE FUNCTION CRYPT( P_STR IN VARCHAR2 ) RETURN VARCHAR2 AS ...
Categories: DBA Blogs

Oracle Systems Partner Webcast-Series: SPARC value for Partners

  February...

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

Oracle database backup

Tom Kyte - Wed, 2018-02-21 15:46
Hi Developers, I am using Oracle 10g. I need to take backup of my database. I can take a back-up of tables, triggers etc using sql developers' Database Backup option but there are multiple users created in that database. Can you please support ...
Categories: DBA Blogs

How do you purge stdout files generated by DBMS_SCHEDULER jobs?

Tom Kyte - Wed, 2018-02-21 15:46
When running scheduler jobs, logging is provided in USER_SCHEDULER_JOB_LOG and USER_SCHEDULER_JOB_RUN_DETAILS. And stdout is provided in $ORACLE_HOME/scheduler/log. The database log tables are purged either by default 30 days (log_history attribute)....
Categories: DBA Blogs

V$SQL history

Tom Kyte - Wed, 2018-02-21 15:46
How many records/entry are there in v$sql,v$ession. and how they flush like Weekly or Space pressure. Thanks
Categories: DBA Blogs

Dynamic SQL in regular SQL queries

Tom Kyte - Wed, 2018-02-21 15:46
Hi, pardon me for asking this question (I know I can do this with the help of a PL/SQL function) but would like to ask just in case. I'm wondering if this doable in regular SQL statement without using a function? I'm trying to see if I can write a ...
Categories: DBA Blogs

Adding hash partitions and spreading data across

Tom Kyte - Wed, 2018-02-21 15:46
Hi, I have a table with a certain number of range partitions and for each partitions I have eight hash subpartitions. Is there a way to increase the subpartitions number to ten and distributing evenly the number of rows? I have tried "alter tabl...
Categories: DBA Blogs

Bug when using 1 > 0 at "case when" clause

Tom Kyte - Wed, 2018-02-21 15:46
Hello, guys! Recently, I've found a peculiar situation when building a SQL query. The purpose was add a "where" clause using a "case" statement that was intented to verify if determined condition was greater than zero. I've reproduced using a "wit...
Categories: DBA Blogs

Difference between explain and execute plan and actual execute plan

Tom Kyte - Wed, 2018-02-21 15:46
Hi, I have often got questions around explain plan and execute plan. As per my knowledge, explain plan gives you the execute plan of the query. But I have also read that Execute plan is the plan which Oracle Optimizer intends to use for the query and...
Categories: DBA Blogs

Early morning RMOUG post

Bobby Durrett's DBA Blog - Wed, 2018-02-21 06:44

Well, it is early Wednesday morning here at the Westin hotel in Denver where the RMOUG Training Days conference is being held. I can’t sleep anyway so I thought I would write-up some of my impressions of yesterday’s presentations.

I appreciate all the effort people put in making their presentations. Since I have done Toastmasters I’ve learned to appreciate more what goes into being an effective speaker. But, the nature of my work is that I have to be critical of everything people say about technology. Maybe I should say “I have to think critically” instead of “be critical”. The problem with the type of work we do is that it involves a lot of money and that inevitably obscures the truth about the technical details of how things work. So, I want to just sit back and applaud but my technical side wants to tear apart every detail.

A nice perk of being a RMOUG presenter is that I got to attend the pre-conference workshops for free as well as the rest of the talks. In past conferences that I have spoken at that was not the case. So, I went to a four-hour Snowflake workshop. I have read a fair amount on Snowflake so much that the speaker presented was familiar. I wonder how people who had no Snowflake background perceived the talk? Being a nuts and bolts Oracle person I would have liked to dig in more to Snowflake internals and discuss its limitations. Surely any tool has things it does better and things that it does not do so well because of the choices that the developers made in its design. I’m interested in how Snowflake automatically partitions data across files on S3 and caches data in SSD and RAM at the compute level. At least, that is what the information on the web site suggests. But with cloud computing it seems that people frown upon looking under the covers. The goal is to spin up new systems quickly and Snowflake is fantastic at that. Also, it seems to get great performance with little effort. No tuning required! Anyway, it was a good presentation but didn’t get into nuts and bolts tuning and limitations which I would have liked to see.

I spent the rest of the day attending hour-long presentations on various topics. AWS offered a 3 hour session on setting up Oracle on RDS but since I’ve played with RDS at work I decided to skip it. Instead I went to mostly cloud and Devops sessions. I accidentally went to an Oracle performance session which was amusing. It was about tuning table scans in the cloud. The speaker claimed that in Oracle’s cloud you get sub-millisecond I/O which raised a bunch of questions in my mind. But the session was more about using Oracle database features to speed up a data warehouse query. It was fun but not what I expected.

I was really surprised by the Devops sessions. Apparently Oracle has some free Devops tools in their cloud that you can use for on premise work. My office is working with a variety of similar tools already so it is not something we would likely use. But it could be helpful to someone who doesn’t want to install the tools yourself. I’m hopeful that today’s Devops session(s) will fill in more details about how people are using Devlops with databases. I’m mostly interested in how to work with large amounts of data in Devops. It’s easy to store PL/SQL code in Git for versioning and push it out with Flywaydb or something like it. It is hard to make changes to large tables and have a good backout. Data seems to be Devops’s Achilles heel and I haven’t seen something that handles it well. I would love to hear about companies that have had success handling data changes with Devops tools.

Well, I’ve had one cup of coffee and Starbucks doesn’t open for another half hour but this is probably enough of a pre-dawn RMOUG data dump. Both of my talks are tomorrow so today is another day as a spectator. Likely it will be another day of cloud and Devops but I might sneak an Oracle performance talk in for one session.

Bobby

Categories: DBA Blogs

Strange dependency in user_dependency: view depends on unreferenced function

Tom Kyte - Tue, 2018-02-20 21:26
Dear Team, I will try to simplify the scenario we have, using a simple test case: <code> SQL> create table test_20 ( a number) 2 / Table created. SQL> SQL> create or replace function test_function (p_1 in number) 2 return num...
Categories: DBA Blogs

Report for employee attendance

Tom Kyte - Tue, 2018-02-20 21:26
I am sorry for asking this seemingly trivial question, but I have been struggling with it for some time, my deadline is approaching and I can't find any answers for it. I have 3 tables: Calendar table: <code>CREATE TABLE "CJ_CAL" ( "CAL_ID...
Categories: DBA Blogs

Using SELECT * combined with WITH-CLAUSE - Bad Practice? View gets compiled with static columns list

Tom Kyte - Tue, 2018-02-20 21:26
Hey guys, I have a question regarding clean SQL Code / Bad Practice around the use of wildcards in SELECT-Statements. In the provided example I have a base-query with a huge list of columns selected and two (or more) sources I need to have combin...
Categories: DBA Blogs

ORA-01502 with hardcoded index hints

Tom Kyte - Tue, 2018-02-20 03:06
We have EBS 12.1.3, which has several indexes, that we disable for bulk loading at night and rebuild them once done. While they are disabled some queries get ORA-01502 because of hard coded index hint on these unusable indexes, although we have skip...
Categories: DBA Blogs

SUBPARTITION ELIMINATION

Tom Kyte - Tue, 2018-02-20 03:06
Table with 10 years worth of data Has two key date columns. Queries are always by one of the dates. <b>But never both</b>. So how to get partition elimination to work for both when really can only partition by 1 column. **Thought was - since ...
Categories: DBA Blogs

Issue with DBMS_CRYPTO Encryption method for larger dataset

Tom Kyte - Tue, 2018-02-20 03:06
Table Structure <code> CREATE TABLE TEST_PAYLOAD ( CNTNT_PAYLOAD_OID NUMBER(20) NOT NULL, CNTNT_PAYLOAD CLOB, BLOB_CNTNT_PAYLOAD BLOB, ENCRYPT_BLOB BLOB, DECRYPT_BLOB BLOB, CLOB_CNT_PAYLO...
Categories: DBA Blogs

data encryption package

Tom Kyte - Tue, 2018-02-20 03:06
Tom: Would you please provide some examples for how to use dbms_obfuscation_toolkit ? -Thx -Reji
Categories: DBA Blogs

Primary and foreign key in temporal tables

Tom Kyte - Tue, 2018-02-20 03:06
I have product and supplier table. Both tables have start and end date columns (temporal validity) Relation between product and supplier table is managed via a link table product_supplier_lnk table. I need to know how to manage primary and foreign...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs