DBA Blogs

Generate Random number and compare it for uniqueness in database

Tom Kyte - Tue, 2019-04-30 16:06
Hi Tom, I have a situation where I have to create a function which will return a unique number (Suffix R and 7 numbers) which will than be compared with data present in database with a particular column which is primary key. if the number generate...
Categories: DBA Blogs

Issue Global temporary table

Tom Kyte - Tue, 2019-04-30 16:06
Hi Tom I have Stored procedure return some data, <code>create PROCEDURE "SP1" (CV_1 IN OUT SYS_REFCURSOR) /*CREATE GLOBAL TEMPORARY table TT_TABL2 ( ORDER_ID NUMBER, REQ_ID NUMBER, TXN_ID NUMBER, ...
Categories: DBA Blogs

Oracle Insert

Tom Kyte - Tue, 2019-04-30 16:06
Hi Tom, How exactly big insert or update getting processed in oracle? Suppose I have to insert >=10GB more records in this table will this much of insert floods the db buffer cache or how it affect the SGA. Is it the same case with update...
Categories: DBA Blogs

problem to drop tables

Tom Kyte - Tue, 2019-04-30 16:06
hi i have a problem when i am trying to drop tables i get this error Error dropping CITIZEN: ORA-04098: trigger 'SYS.DELETE_ENTRIES' is invalid and failed re-validation thanks israel
Categories: DBA Blogs

May 17th AZORA Meetup – Last until September!

Bobby Durrett's DBA Blog - Tue, 2019-04-30 14:28
#meetup_oembed .mu_clearfix:after { visibility: hidden; display: block; font-size: 0; content: " "; clear: both; height: 0; }* html #meetup_oembed .mu_clearfix, *:first-child+html #meetup_oembed .mu_clearfix { zoom: 1; }#meetup_oembed { background:#eee;border:1px solid #ccc;padding:10px;-moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;margin:0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 12px; }#meetup_oembed h3 { font-weight:normal; margin:0 0 10px; padding:0; line-height:26px; font-family:Georgia,Palatino,serif; font-size:24px }#meetup_oembed p { margin: 0 0 10px; padding:0; line-height:16px; }#meetup_oembed img { border:none; margin:0; padding:0; }#meetup_oembed a, #meetup_oembed a:visited, #meetup_oembed a:link { color: #1B76B3; text-decoration: none; cursor: hand; cursor: pointer; }#meetup_oembed a:hover { color: #1B76B3; text-decoration: underline; }#meetup_oembed a.mu_button { font-size:14px; -moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;border:2px solid #A7241D;color:white!important;text-decoration:none;background-color: #CA3E47; background-image: -moz-linear-gradient(top, #ca3e47, #a8252e); background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0, #a8252e), color-stop(1, #ca3e47));disvplay:inline-block;padding:5px 10px; }#meetup_oembed a.mu_button:hover { color: #fff!important; text-decoration: none; }#meetup_oembed .photo { width:50px; height:50px; overflow:hidden;background:#ccc;float:left;margin:0 5px 0 0;text-align:center;padding:1px; }#meetup_oembed .photo img { height:50px }#meetup_oembed .number { font-size:18px; }#meetup_oembed .thing { text-transform: uppercase; color: #555; }
AZORA Meetup before summer heats up! Two presentations – Friday, May 17th

Friday, May 17, 2019, 12:00 PM

Republic Services – 2nd Floor Conference Rooms
14400 N 87th St (AZ101 & Raintree) Scottsdale, AZ

8 AZORAS Attending

AZORA Meetup before the summer heats up! We will be featuring two presentations on Friday May 17th meeting. Logistics are the same at friendly Republic Services, an afternoon with other Oracle types between Noon till 4pm. We will follow typical agenda of Lunch – Learn – Learn some more – Let us call it a weekend! Here are the details: Meeting Agend…

Check out this Meetup →

This is our last Meetup until our Summer break. Come check out two great presentations.

Doug Hood from Oracle will talk about the Oracle In-Memory database feature. We appreciate Oracle providing us with this technical content to support AZORA.

AZORA’s own Stephen Andert will be sharing a non-technical presentation on Networking. He just gave the same talk at the national Oracle user group meeting called Collaborate 19 so it will be great to have him share with his local user group.

Looking forward to seeing you there.

Bobby

P.S. AZORA is the Arizona Oracle User Group, and we meet in the Phoenix, Arizona area.

Categories: DBA Blogs

OGG-01298 could not find column TRANSACTION

VitalSoftTech - Mon, 2019-04-29 19:39
Question: When upgrading GoldenGate from 11 to 12c, on starting up the Replicat I get the following error. What caused this and how is it resolved? OGG-01298 Column function diagnostic message: could not find column “TRANSACTION”. Answer: The OGG-01298 error is returned on the Replicat startup after the GoldenGate is upgraded to 12c. This happens […]
Categories: DBA Blogs

.NET Core Connection String to RAC database

Tom Kyte - Mon, 2019-04-29 02:46
Hello, Ask Tom Team. We have a .Net Core app connecting to a Oracle 18c RAC database. We are not using tnsnames.ora file (there is no oracle client installed on the client-server side). We are using managed .Net Core oracle client and the followin...
Categories: DBA Blogs

Stored Procedure behaves randomly - sometimes takes 15 minutes where it should take 15 milli-seconds.

Tom Kyte - Mon, 2019-04-29 02:46
We developed a procedure to be used in database-A and in database-A. In this procedure we send an input parameter on the basis of which a select statement searches the data from a table located in remote database-B. The selected columns are set in Ou...
Categories: DBA Blogs

Cluster Waits Rac database

Tom Kyte - Mon, 2019-04-29 02:46
Hello, Ask Tom Team. I have a two-nodes RAC database running 18.4.0 and I'm running a load test (lots of inserts). Basically, two tables are being hit. These tables are using identity column and have a relationship (fk). I'm checking OEM 13c and t...
Categories: DBA Blogs

High commit wait on RAC database

Tom Kyte - Mon, 2019-04-29 02:46
Hello, Ask Tom Team. I have a two-nodes RAC database running 18.4.0 and I'm running a load test (lots of inserts). Basically, two tables are being hit. These tables are using identity column and have a relationship (fk). I'm checking OEM 13c and t...
Categories: DBA Blogs

Redo log file size and Database Performance

Tom Kyte - Mon, 2019-04-29 02:46
Hi there, We have a database setup as follows 1) SQL server -- Where transactions are happening here 2) Oracle(DSS)-- we are transferring the incremental data from SQL server(NOT ALL THE TABLES ONLY 22 TABLES) with the help of a 24/7 running ...
Categories: DBA Blogs

Redo logs Sizing

Tom Kyte - Mon, 2019-04-29 02:46
Hello, Ask Tom Team. What is the best practice: small redo log groups or have a few but with a bigger size? Thanks in advanced. Regards,
Categories: DBA Blogs

RMAN - Full vs. Incremental - performance problem

Tom Kyte - Thu, 2019-04-25 22:46
Hi. I am testing RMAN. I have run an incremental 0 and an incremental 1 cumulative test without having any database activity in-between these test. I am using OmniBack media manager with RMAN. I have three databases. Here are the timing results ...
Categories: DBA Blogs

Subtract time from a constant time

Tom Kyte - Wed, 2019-04-24 10:06
Hello there, I want to create a trigger that will insert a Time difference value into a table Example: I have attendance table Sign_in date; Sign_out date; Late_in number; Early_out number; Now I want to create a trigger that will insert la...
Categories: DBA Blogs

Clob column in RDBMS(oracle) table with key value pairs

Tom Kyte - Wed, 2019-04-24 10:06
In our product in recent changes, oracle tables are added with clob column having key value pairs in xml/json format with new columns. <b>example of employee:(Please ignore usage of parenthesis) </b> 100,Adam,{{"key": "dept", "value": "Marketi...
Categories: DBA Blogs

current value of sequence

Tom Kyte - Wed, 2019-04-24 10:06
Hi. Simple question :-) Is it possible to check current value of sequence? I though it is stored in SEQ$ but that is not true (at least in 11g). So is it now possible at all? Regards
Categories: DBA Blogs

Partner Webcast – Continuous Integration & Delivery on Oracle SOA Cloud Service

Oracle SOA Cloud Service provides an integration platform as a service (iPaaS) so that you can quickly provision your new platform, start developing and deploying your APIs and integration projects...

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

Bitmap Index On Column With 212552698 Distinct Values, What Gives? (I’d Rather Be High)

Richard Foote - Mon, 2019-04-22 21:45
In my previous post on Indexing The Autonomous Warehouse, I highlighted how it might be necessary to create indexes to improve the performance and scalability of highly selective queries, as it might on any Data Warehouse running on an Exadata platform. In the post, I created a Bitmap Index and showed how improve SQL performance […]
Categories: DBA Blogs

Understanding Nested Lists Dictionaries of JSON in Python and AWS CLI

Pakistan's First Oracle Blog - Sat, 2019-04-20 03:01

After lots of hair pulling, bouts of frustration, I was able to grasp this nested list and dictionary thingie in JSON output of AWS cli commands such as describe-db-instances and others. If you run the describe-db-instances for rds or describe-instances for ec2, you get a huge pile of JSON mumbo-jumpo with all those curly and square brackets studded with colons and commas. The output is heavily nested.


For example, if you do :

aws rds describe-db-instances

you get all the information but heavily nested within. Now if you only want to extract or iterate through, say VPCSecurityGroupId of all database instances, then you have to traverse all that nested information which comprises of dictionary consisting of keys which have values as arrays and those arrays have more dictionaries and so on.

After the above rant, let me try to ease the pain a bit by explaining this. For clarity, I have just taken out following chunk from describe-db-instance output. Suppose, the only thing you are interested in is the value of VpcSecurityGroupId from  following chunk:

mydb=rds.describe_db_instances(DBInstanceIdentifier=onedb)
mydb= {'DBInstances':
          [
            {'VpcSecurityGroups': [ {'VpcSecurityGroupId': 'sg-0ed48bab1d54e9554', 'Status': 'active'}]}
          ]
       }

The variable mydb is a dictionary with key DBInstances. This key DBInstances has an array as its value. Now the first item of that array is another dictionary and the first key of that dictionary is VpcSecurityGroups. Now the value this key VpcSecurityGroups another array. This another array's first item is again a dictionary. This last dictionary has a key VpcSecurityGroupId and we want value of this key.

If your head has stopped spinning, then read on and stop cursing me as I am going to demystify it now.

If you want to print that value just use following command:

mydb['DBInstances'][0]['VpcSecurityGroups'][0]['VpcSecurityGroupId']

So the secret is that if its a dictionary, then use key name and if its an array then use index and keep going. That's all there is to it. Full code to print this using Python, boto3 etc is as follows:

import boto3
import click

rds = boto3.client('rds',region_name='ap-southeast-2')
dbs = rds.describe_db_instances()

@click.group()
def cli():
    "Gets RDS data"
    pass

@cli.command('list-database')
@click.argument('onedb')
def list_database(onedb):
    "List info about one database"
    mydb=rds.describe_db_instances(DBInstanceIdentifier=onedb)
    #print(mydb)
    #Following line only prints value of VpcSecurityGroupId of RDS instance
    print(mydb['DBInstances'][0]['VpcSecurityGroups'][0]['VpcSecurityGroupId'])
    #Following line only prints value of OptionGroup of RDS instance
    print(mydb['DBInstances'][0]['OptionGroupMemberships'][0]['OptionGroupName'])
    #Following line only prints value of Parameter Group of RDS instance
    print(mydb['DBInstances'][0]['DBParameterGroups'][0]['DBParameterGroupName'])

if __name__ == '__main__':
    cli()


I hope that helps. If you know any easier way, please do favor and let us know in comments. Thanks.

Categories: DBA Blogs

Migrating Oracle Database & Non Oracle Database to Oracle Cloud

You can directly move / migrate various source databases into different target cloud deployments running the Oracle Cloud. Oracle automated tools for migration will move on premise database to the...

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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs