Feed aggregator

sql query optimization

Tom Kyte - Sun, 2017-01-22 13:06
Hi Tom how can i reduce select statement and how optimize the select query please help on this issue tom?thanks SELECT pa_num, partner_id, product_line FROM IF_LSP_PANUM_MAP ilp WHERE icp.country_code=i_workflow_keys AND NOT EXISTS(SELECT 1 ...
Categories: DBA Blogs

Pl/SQL For loop

Tom Kyte - Sun, 2017-01-22 13:06
Hi Tom, Below is the code structure of what i am trying to do declare lowerl number:= 1; upperl number:= 3; num varchar2(10); begin for i in lowerl..upperl loop num:=num||to_char(lowerl); if i=3 then upperl:=5; end if; end loop; mes...
Categories: DBA Blogs

Global Session Parameter must be true when bulk inserting over a database link using a local function

Tom Kyte - Sun, 2017-01-22 13:06
My actual use case is I need to transfer millions of records over a db link and need to make use of certain functions to transform the data. I am having issues when I try do a bulk insert across a database link and make use of a function inside ...
Categories: DBA Blogs

Binding variables change names each time. How to avoid hard parse?

Tom Kyte - Sun, 2017-01-22 13:06
I use an application, which is built with ODP.NET. This application works with binding variables, but for some reason it uses new names of these variable in each sql call by adding a sequential number at the end of each binding variable: For exam...
Categories: DBA Blogs

Calculate months with two date

Tom Kyte - Sun, 2017-01-22 13:06
Hi there, i am newbie for using function please help to solve it I have tried this code and got error as follow Error : Compilation failed,line 2 (13:32:38) PLS-00103: Encountered the symbol "(" when expecting one of the following: . @ % ; is a...
Categories: DBA Blogs

bind variable does not exist

Tom Kyte - Sun, 2017-01-22 13:06
I am getting below error message when trying to execute F_INSERT_ORDER_Details_DATA funtion <code>ORA-01006: bind variable does not exist ORA-06512: at "ADMIN.F_INSERT_ORDER_DETAILS_DATA", line 37 ORA-06512: at line 5</code> CREA...
Categories: DBA Blogs

How to alter system password in oracle 11g

Tom Kyte - Sun, 2017-01-22 13:06
I have installed oracle 11g before a couple of months but i forgot my system password and also i couldn't able to conn /as sysdba. Could you please suggest the process of resetting the password for user SYSTEM.
Categories: DBA Blogs

Printing a name with out using dbms_output.put_line

Tom Kyte - Sun, 2017-01-22 13:06
Hi Tom, Recently in one of the interview i was asked whether i can print any thing with out using dbms_output.put_line in Pl/SQL I am having a little experience in Pl/SQL , As per my knowledge i think we cannot. But please let me know if there ...
Categories: DBA Blogs

UNDO generated By Index blocks during INSERT

Tom Kyte - Sun, 2017-01-22 13:06
Hi, I am using version- 11.2.0.4 of oracle. I have a query running and its getting slow down during specific time period of the day(10PM to 9AM) which i can also verify from the data present in DBA_HIST_SQLSTAT. The number of rows generated from t...
Categories: DBA Blogs

SQL Bind Variable Support in ADF BC REST

Andrejus Baranovski - Sun, 2017-01-22 10:14
Is not that obvious from Oracle ADF BC REST developer guide how to provide value for bind variable defined directly in the View Object SQL statement. I did research around this and would like to post few hints to make your life easier, if you have same requirement - pass values from REST request to View Object required bind variables. This topic is especially useful, when you want to reuse existing ADF BC implementation for ADF BC REST access.

We are going to use View Object Row Finder. Oracle ADF BC REST developer guide explains how to use Row Finder with View Criteria. In our case we have different situation -  we would like to use Row Finder for required bind variables, referenced directly in SQL statement.

You can't define Row Finder without View Criteria. First trick is to define empty View Criteria, just to be able to define Row Finder - we are not going to use View Criteria functionality, our bind variables are referenced directly in SQL WHERE clause:


Once Row Finder is defined, you are going to see bind variables listed. Keep in mind - this doesn't means bind variables are referenced by Row Finder, they are just listed for possible use. Now main trick - go and define some dummy expression for each bind variable you want to use in REST request (make sure to uncheck - Save expression to groovy file):


This action would generate Groovy expression for each bind variable and list these bind variables under Row Finder. Go to source of View Object to see the structure:


Now remove Groovy expressions assigned to each bind variable - we don't need them, keep only bind variable names assigned to Row Finder:


Visually in the wizard is going to look like there are no changes made - but we keep bind variables under Row Finder tag now:


Bind variables are included directly into SQL statement WHERE clause:


ADF BC REST service is defined in regular way, no special tips here:


This is how REST call looks like: Departments?finder=RESTFilter;depNameVar=IT.locIdVar=1700. We include Row Finder name and two bind variables with values:


In the background we could check ADF BC log, where it prints SQL statement with both bind variables assigned with values:


Download and browse sample application ADFBCRestApp from my GitHub repository - jetcrud.

Next Chapter … Time for a Change

DBASolved - Sat, 2017-01-21 20:06

It has been about three months since I’ve published a post. Well this time it has been a mix of work and decision making on my next chapter in my career that has kept me from posting. Hopefully, in the near future I will have more time to publish articles on topics I really want to discuss.

For the people close to me, many of you already know what this change is. I appreciate that you have kept it quite while all the details were worked out.

Before I get into what the next chapter of my career is, I have to say thanks to the management and team members at Accenture Enkitec Group (Enkitec/AEG). It has been a pleasure to work with everyone at Enkitec/AEG over the last couple of years. You truely are a great group of individuals and I will miss working daily with many of you. AEG is such a brain trust of people, it is almost hard to leave! I know that this will continue to be the trend at AEG.

Now what is the next chapter?

As of late January 2017, I have decided to leave Enkitec/AEG in pursuit of an opportunity that I feel is a good mix of my technical skills and allow me to grow in a challenging direction. Come Feburary 6th, 2017, I will be starting my new role as Senior Principal Product Manager on the GoldenGate team at Oracle. This position will allow me to work with a technology I’m very interested in and help shape the path forward for it. After all we know “cloud” is the next best thing right? How are you getting your data into or out of that “cloud”? Well, I will be part of the team that will help provide a path to do that. I’ll have to keep you posted on the technology as it evolves.

Now that you know where I’m going, just a few house keeping items to take care of. I’ve already said thanks to my friends at Enkitec/AEG. Without your support on many fronts, I wouldn’t be where I’m at as I proceed on this new chapter. Again, I greatly appreciate it.

For the community as a whole, many of you know that I’m an ACE Director (ACED) or at least was an ACED … LOL. Well, just like many of former ACEDs, when you go to Oracle you have to give it up. So, as of January 20, 2017, I resigned my ACED status and now listed as an ACE Alumni.

Blog site! I plan on keeping dbasolved.com up and running. I will be posting more article on GoldenGate here after starting with Oracle. So keep checking back to see if I’ve posted anything new. After all, GoldenGate is starting to become a hot topic!

I hope you will join me in my excitement on this new chapter of my career with Oracle and the GoldenGate team! I look forward to seeing many of you out and about within the community as I take on this new chapter.

Enjoy!

about.me:http://about.me/dbasolved


Filed under: Golden Gate
Categories: DBA Blogs

12cR1 RAC Posts -- 4 : Adding a Disk of a different size

Hemant K Chitale - Sat, 2017-01-21 10:44
How does 12.1.0.2 ASM handle adding a disk of a different size to an existing DiskGroup ?

I currently have 4 disks of 5GB each in 2 DiskGroups

[oracle@collabn1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 21 23:48:00 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select group_number, disk_number, name, state, total_mb
2 from v$asm_disk
3 order by 1,2,3
4 /

GROUP_NUMBER DISK_NUMBER NAME STATE TOTAL_MB
------------ ----------- ------------------------------ -------- ----------
0 0 NORMAL 0
1 0 DATA_0000 NORMAL 5114
1 1 DATA_0001 NORMAL 5114
2 0 FRA_0000 NORMAL 5114

SQL>
SQL> select group_number, name
2 from v$asm_diskgroup
3 order by 1
4 /

GROUP_NUMBER NAME
------------ ------------------------------
1 DATA
2 FRA

SQL>


The DATA DiskGroup has 2 disks of 5GB each and the FRA DiskGroup has 1 disk of 5GB.  One disk (identified as DiskNumber=0) is not yet assigned.

What happens if I try to expand the DATA DiskGroup with a Disk of 10GB ?

[root@collabn1 dev]# fdisk /dev/sdf
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xff8b0ab7.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').

Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)

Command (m for help): p

Disk /dev/sdf: 12.9 GB, 12884901888 bytes
255 heads, 63 sectors/track, 1566 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xff8b0ab7

Device Boot Start End Blocks Id System

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1566, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-1566, default 1566):
Using default value 1566

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@collabn1 dev]#
[root@collabn1 dev]# /sbin/scsi_id -g -u -d /dev/sdf
1ATA_VBOX_HARDDISK_VB535deca9-9a295efe
[root@collabn1 dev]#
[root@collabn1 dev]# /sbin/scsi_id -g -u -d /dev/sdf
1ATA_VBOX_HARDDISK_VB535deca9-9a295efe
[root@collabn1 dev]# cd /etc/udev/rules.d
[root@collabn1 rules.d]# vi 99-oracle-asmdevices.rules
[root@collabn1 rules.d]# tail -1 99-oracle-asmdevices.rules
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VB535deca9-9a295efe", NAME="asm-disk5", OWNER="oracle", GROUP="dba", MODE="0660"
[root@collabn1 rules.d]#
[root@collabn1 rules.d]# /sbin/partprobe /dev/sdf1
[root@collabn1 rules.d]# /sbin/udevadm test /block/sdb/sdf1
run_command: calling: test
udevadm_test: version 147
This program is for debugging only, it does not run any program,
specified by a RUN key. It may show incorrect results, because
some values may be different, or not available at a simulation run.

parse_file: reading '/lib/udev/rules.d/10-console.rules' as rules file
parse_file: reading '/lib/udev/rules.d/10-dm.rules' as rules file
parse_file: reading '/lib/udev/rules.d/11-dm-lvm.rules' as rules file
parse_file: reading '/lib/udev/rules.d/13-dm-disk.rules' as rules file
parse_file: reading '/lib/udev/rules.d/40-isdn.rules' as rules file
parse_file: reading '/lib/udev/rules.d/40-redhat.rules' as rules file
parse_file: reading '/lib/udev/rules.d/42-qemu-usb.rules' as rules file
parse_file: reading '/lib/udev/rules.d/50-firmware.rules' as rules file
parse_file: reading '/lib/udev/rules.d/50-udev-default.rules' as rules file
parse_file: reading '/etc/udev/rules.d/55-usm.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-alias-kmsg.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-cdrom_id.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-fprint-autosuspend.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-net.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-pcmcia.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-alsa.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-input.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-serial.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-storage-tape.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-storage.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-v4l.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-raw.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-vboxadd.rules' as rules file
parse_file: reading '/lib/udev/rules.d/61-mobile-action.rules' as rules file
parse_file: reading '/lib/udev/rules.d/61-option-modem-modeswitch.rules' as rules file
parse_file: reading '/lib/udev/rules.d/61-persistent-storage-edd.rules' as rules file
parse_file: reading '/lib/udev/rules.d/64-device-mapper.rules' as rules file
parse_file: reading '/lib/udev/rules.d/64-md-raid.rules' as rules file
parse_file: reading '/lib/udev/rules.d/65-md-incremental.rules' as rules file
parse_file: reading '/lib/udev/rules.d/69-dm-lvm-metad.rules' as rules file
parse_file: reading '/lib/udev/rules.d/70-acl.rules' as rules file
parse_file: reading '/lib/udev/rules.d/70-cups-libusb.rules' as rules file
parse_file: reading '/lib/udev/rules.d/70-hid2hci.rules' as rules file
parse_file: reading '/etc/udev/rules.d/70-persistent-cd.rules' as rules file
parse_file: reading '/etc/udev/rules.d/70-persistent-net.rules' as rules file
parse_file: reading '/lib/udev/rules.d/71-biosdevname.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-cd-aliases-generator.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-net-description.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-persistent-net-generator.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-tty-description.rules' as rules file
parse_file: reading '/lib/udev/rules.d/78-sound-card.rules' as rules file
parse_file: reading '/lib/udev/rules.d/79-fstab_import.rules' as rules file
parse_file: reading '/lib/udev/rules.d/80-drivers.rules' as rules file
parse_file: reading '/lib/udev/rules.d/80-iosched.rules' as rules file
parse_file: reading '/lib/udev/rules.d/80-mpath-iosched.rules' as rules file
parse_file: reading '/lib/udev/rules.d/85-regulatory.rules' as rules file
parse_file: reading '/lib/udev/rules.d/88-clock.rules' as rules file
parse_file: reading '/lib/udev/rules.d/89-microcode.rules' as rules file
parse_file: reading '/etc/udev/rules.d/90-alsa.rules' as rules file
parse_file: reading '/lib/udev/rules.d/90-btrfs.rules' as rules file
parse_file: reading '/etc/udev/rules.d/90-hal.rules' as rules file
parse_file: reading '/lib/udev/rules.d/91-drm-modeset.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-dm-notify.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-keyboard-force-release.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-keymap.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-udev-late.rules' as rules file
parse_file: reading '/etc/udev/rules.d/98-kexec.rules' as rules file
parse_file: reading '/etc/udev/rules.d/99-oracle-asmdevices.rules' as rules file
parse_file: reading '/dev/.udev/rules.d/99-root.rules' as rules file
udev_rules_new: rules use 32448 bytes tokens (2704 * 12 bytes), 19085 bytes buffer
udev_rules_new: temporary index used 19500 bytes (975 * 20 bytes)
unable to open device '/sys/block/sdb/sdf1'
[root@collabn1 rules.d]#
[root@collabn1 rules.d]# /sbin/udevadm control --reload-rules
[root@collabn1 rules.d]# /sbin/start_udev
Starting udev: [ OK ]
[root@collabn1 rules.d]#
[root@collabn1 rules.d]# ls -l /dev/asm*
brw-rw----. 1 oracle dba 8, 17 Jan 22 00:07 /dev/asm-disk1
brw-rw----. 1 oracle dba 8, 33 Jan 22 00:07 /dev/asm-disk2
brw-rw----. 1 oracle dba 8, 49 Jan 22 00:07 /dev/asm-disk3
brw-rw----. 1 oracle dba 8, 65 Jan 22 00:05 /dev/asm-disk4
brw-rw----. 1 oracle dba 8, 81 Jan 22 00:05 /dev/asm-disk5


So I now have asm-disk5 as the new ASM Disk.  Let my try to add this disk.

SQL> set pages600
SQL> select group_number, disk_number, name, path, total_mb
2 from v$asm_disk
3 order by 1,2
4 /

GROUP_NUMBER DISK_NUMBER NAME
------------ ----------- ------------------------------
PATH
--------------------------------------------------------------------------------
TOTAL_MB
----------
0 0
/dev/asm-disk5
0

0 1
/dev/asm-disk4
0

1 0 DATA_0000
/dev/asm-disk1
5114

1 1 DATA_0001
/dev/asm-disk2
5114

2 0 FRA_0000
/dev/asm-disk3
5114


SQL>
SQL> alter diskgroup data add disk '/dev/asm-disk5';

Diskgroup altered.

SQL>
SQL> alter diskgroup data add disk '/dev/asm-disk5';

Diskgroup altered.

SQL> select group_number, name, total_mb
2 from v$asm_diskgroup
3 order by 1,2
4 /

GROUP_NUMBER NAME TOTAL_MB
------------ ------------------------------ ----------
1 DATA 22512
2 FRA 5114

SQL>
SQL> select group_number, name, type
2 from v$asm_diskgroup
3 order by 1,2
4 /

GROUP_NUMBER NAME TYPE
------------ ------------------------------ ------
1 DATA EXTERN
2 FRA EXTERN

SQL>
SQL> select group_number, name, compatibility, database_compatibility
2 from v$asm_diskgroup
3 order by 1
4 /

GROUP_NUMBER NAME
------------ ------------------------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY
------------------------------------------------------------
1 DATA
12.1.0.0.0
10.1.0.0.0

2 FRA
12.1.0.0.0
10.1.0.0.0


SQL>
SQL> select group_number, disk_number, name, path, total_mb
2 from v$asm_disk
3 order by 1,2,3
4 /

GROUP_NUMBER DISK_NUMBER NAME
------------ ----------- ------------------------------
PATH
--------------------------------------------------------------------------------
TOTAL_MB
----------
0 0
/dev/asm-disk4
0

1 0 DATA_0000
/dev/asm-disk1
5114

1 1 DATA_0001
/dev/asm-disk2
5114

1 2 DATA_0002
/dev/asm-disk5
12284

2 0 FRA_0000
/dev/asm-disk3
5114


SQL>


According to Oracle Support Document 1938950.1, adding a disk of a different size to an existing DiskGroup fails with an error ORA-15410 in 12.1.0.2.  However, that seems to apply to NORMAL or HIGH Redundancy and COMPATIBLE.ASM 12.1.0.2.   Here, I have EXTERNAL Redundancy and COMPATIBLE.ASM 12.1.0.0.0

Do I recommend Disks of different sizes ?  Absolutely *not* in Production.  This is a "play" environment in Virtual Machines on my desktop that I can destroy and recreate anytime.  I can monitor disk usage as well.
.
.
.

Categories: DBA Blogs

Rosetta Code

Bobby Durrett's DBA Blog - Sat, 2017-01-21 09:24

I have experimented with the Rosetta Code website as a way to learn more about computer technology. I have mentioned my exploration of the edX and OCW web sites in earlier posts so I thought I would mention Rosetta Code or RC as another way to learn.

The RC site displays code in a variety of programming languages so that you can compare the languages. They define certain tasks and people post code that performs the tasks. For example, the task could be to write a simple Hello World program that writes Hello World to the screen. The site shows how to do this in Java, Python, C, and others. At this moment there are 354 implementations of Hello World on the site.  I added this simple one for Oracle’s version of SQL: My Hello World

But, I can’t help wondering if this is just a fun game or is it really educational? I just finished a task this morning that was interesting to me. It is a draft task which means that people have not yet written enough example programs for the site managers to publish it as an official task. But anyone can see it. I’ve written the Python and PL/SQL examples. This 4 squares problem is just a puzzle or game but it was interesting to think about solving it in a declarative way with SQL and not a procedural way with Python or any other regular language. Maybe there is value in looking at these tasks and thinking about how SQL and relational database thinking could be applied?

Anyway, I just thought I would document what I have done with the RC site. Be careful because once you start working up example code for the site it can become addictive! I am still thinking about what the real value of the site is but it has helped me exercise my programming muscles and think a bit about SQL. Check it out if you think it has value.

Bobby

Categories: DBA Blogs

Is there any difference in the execution plan (for better or worse) the order of tables in the from clause?

Tom Kyte - Sat, 2017-01-21 00:46
Is there any difference doing: SELECT a.col1, b.col1 FROM table1 a, table2 b WHERE <join_and/or_filter_clauses> and SELECT a.col1, b.col1 FROM table2 b, table1 a WHERE <join_and/or_filter_clauses> Are the sizes of the tables importante ...
Categories: DBA Blogs

Query on SCAN Listener

Tom Kyte - Sat, 2017-01-21 00:46
I have a two node RAC (Oracle 12c) on linux. I have 5 SCAN listeners configured. Currently, if I do a srvctl config scan I notice that out of the 5 scan listeners, one scan is running in one node and the remaining 4 in the another node. Is it n...
Categories: DBA Blogs

Removing a datafile associated with a tablespace that is no more in the database

Tom Kyte - Sat, 2017-01-21 00:46
I have a situation where there is a datafile which is associated to a tablespaces say ADMIN_TBS. but the tablespace is dropped by a script and the file is still present. Can anyone suggest how i can remove that file. I cannot risk to manually remove ...
Categories: DBA Blogs

Parsing of query

Tom Kyte - Sat, 2017-01-21 00:46
Hi when can be parse calls total are higher than the execution totals for a query in DBA_HIST_SQLSTAT Please provide me possible scenarios Thanks Sai
Categories: DBA Blogs

shared pool issue

Tom Kyte - Sat, 2017-01-21 00:46
Hi Tom& Team, Thanks much for your valuable expertise answers with explanation. Since past one month users queries are getting error out with "ora-04031-unable allocate xxx bytes of shared memory" very frequently and for that fix had to bounce ...
Categories: DBA Blogs

FAST_START_MTTR_TARGET parameter for OLTP

Tom Kyte - Sat, 2017-01-21 00:46
We have database using OLTP system (document management system OPENTEXT). There is a lot of transactions occur inside database. Sequentially, we get a lot of messages as - Thread 1 cannot allocate new log, sequence 13124 Private strand flush n...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator