DBA Blogs

Questions about the upper limit of records in one database block

Tom Kyte - Tue, 2016-08-16 19:26
Hi, team I was told that the last 3 characteristics of ROWID stands for the row number of database block. Theoretically, every database block could store as many as 65536 rows. I was wondering how many records could stores in one block (with the s...
Categories: DBA Blogs

Performance tuning for report query

Tom Kyte - Tue, 2016-08-16 19:26
Dear Tom, Thanks for your valuable info which is being provided by you for various questions on oracle to all the users which is also helping us in lot of ways... Now we have a problem with a report query which is almost taking 2 minutes 40 sec...
Categories: DBA Blogs

rlwrap with #GoldenGate GGSCI

DBASolved - Tue, 2016-08-16 10:45

Since I published posts on how to retrieve command history within GGSCI using the Oracle provided commands of “history” and “fc”, I was pinged a couple of times by some friends asking about “rlwrap” usage with GoldenGate. The answer is a simple yes, rlwrap can be used with Oracle GoldenGate.

What exactly is “rlwrap”?

According to the readme file at http://utopia.knoware.nl/~hlub/uck/rlwrap/#rlwrap, rlwrap is a ‘read-one wrapper’. Basically, it allows for the editing of keyboard input for any command.

How to install?

There are two ways to install “rlwrap”. The first way is manually, which requires you to run the configure and make commands; however, the easiest way I’ve found is by using a yum repository. Tim Hall (a.k.a Oracle-Base) documents this process quite effortlessly over at his blog. Thanks Tim!

Usage?

After you have “rlwrap” installed, you simply have to create an alias in your profile to use it. You can use it from the command line as well; however, the alias approach ensure that it is executed every time you run ggsci. The below example is what I have running in my .bash_profile for the Oracle user.

alias ggsci='rlwrap $OGG_HOME/ggsci'

The next time I login to use ggsci, rlwrap will automatically be ran and then I will be able to scroll through the commands I typed while in ggsci. Another nice thing about “rlwrap” is that when I logout of ggsci, and then back in, my command history is available still.

Although the “history” and “fc” commands are handy, it would be nice to see Oracle include “rlwrap” into the core product of Oracle GoldenGate …

Enjoy!

@dbasolved
http://about.me/dbasolved


Filed under: Golden Gate
Categories: DBA Blogs

dbms_parallel_execute.run_task error when parallel_level > 1

Tom Kyte - Tue, 2016-08-16 01:06
Hi, I am trying to use dbms_parallel_execute to execute a procedure in multiple threads. when the value of parallel_level = 1, the below code just works fine, but when the value of parallel_level > 1 then this below code fails with the error shown b...
Categories: DBA Blogs

Eliminating rows on condition

Tom Kyte - Tue, 2016-08-16 01:06
I have a table for Vehicle owners as follows. The table has 3 columns, Customer id, Vehicle Identification number, and whether the customer is a Primary or Secondary driver on the vehicle. Customer_ID, Vehicle_VIN, Relationship 0001, 12345678, P...
Categories: DBA Blogs

Function rendom_id

Tom Kyte - Tue, 2016-08-16 01:06
HI We do have store procedure and CURSOR is looking for random id per month.We can see result like one visit from 08/may/2016 2 - 09/may/2016 5 - 11/may/2016 20 - 12/may/2016 2 - 18/may/2016 Can you tell me why 20 visits get selected from...
Categories: DBA Blogs

Can I send messages on mobile phone from Oracle database?

Tom Kyte - Tue, 2016-08-16 01:06
Hi TOM, We are running an application on Oracle 8.1.5 on solaris and oas 4.081. We are using basic oracle users for authentication. We want to develop a new functionality in our system by which we will be able to send messages on the user's mobile...
Categories: DBA Blogs

Lost all Redo log file

Tom Kyte - Tue, 2016-08-16 01:06
Hi Tom, I am practising Recovery . CASE 1)I have Development TEST DB in NON-ARCHIVE Mode. NEVER backed up. All Redo log files including the active one ,are dropped from the OS. How to recover such a DB. I dont mind loosing Transacti...
Categories: DBA Blogs

Edit #GoldenGate commands from GGSCI

DBASolved - Mon, 2016-08-15 10:45

Ever get tired of typing the same command over and over again in Oracle GoldenGate GGSCI? Well, Oracle has provided a history recall function into GGSCI, I talked about his in an earlier post. This post on history recall can be found here.

In this post, lets take the command recall and how these commands can be edited. In Oracle GoldenGate 12c, Oracle has provided an GGSCI command called “FC”. The “FC” command is used to display/edit a previously issued GGSCI command and the execute it again. This command leverages the memory buffer the same was as the history command does within GGSCI.

Now the syntax for using this command is as follows:

FC [ n | -n | string ]

n – Displays the command from the line number provided

-n – Displays the command that was issued n lines before the current line

string – Displays the last command that starts with the specified string

Let’s take a look at an example of using “FC” within GGSCI.

In the below output, I have issued a series of commands and then listed these commands using this history function.

GGSCI (fred.acme.com) 6> history

GGSCI Command History

    1: info all
    2: stats extract egg12c
    3: info mgr
    4: info pgg12c
    5: info all
    6: history

Using the “FC” command, I can edit and execute the edited command. If you take a look at command 4 (info pgg12c), I want to replace “pgg12c” with “egg12c”. this is done by using the “r” edit command as follows.

GGSCI 7> fc 4
GGSCI 7> info pgg12c
GGSCI 7..     regg12c
GGSCI 7> info egg12c
GGSCI 7..

EXTRACT    EGG12C    Last Started 2016-08-12 17:06   Status RUNNING
Description          'Integrated Extract'
Checkpoint Lag       00:00:08 (updated 00:00:05 ago)
Process ID           24082
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2016-08-15 10:14:45
                     SCN 0.51017268 (51017268)

By executing “fc 4” (fc n), I’m telling GGSCI to pull the fourth command from the memory buffer for editing. Then I use the “r” command to provide the text that I want to replace in the command. Notice the position of the “r”, it is lined up directly under the string I want to replace. Then I provide the replacement string. Before execution GGSCI provides you with an output of the changed command. After verifying the command, the command can be executed providing the output for the correct process.

Note: For more editing options with FC, you can find these here.

If you want to know what command was executed n commands before the previous command, you can use the -n option to “FC”. This makes the “FC” command act like the history command but only displays the command at the -n position. This can be seen if you do a history command prior to a fc -n command.

GGSCI (fred.acme.com) 11> history

GGSCI Command History

    2: stats extract egg12c
    3: info mgr
    4: info pgg12c
    5: info all
    6: history
    7: info egg12c
    8: info mgr
    9: history
   10: info egg12c
   11: history

GGSCI (fred.acme.com) 12> fc -4
GGSCI (fred.acme.com) 12> info mgr
GGSCI (fred.acme.com) 12..

Manager is running (IP port fred.acme.com.15000, Process ID 12377).

You will notice, that I have eleven commands in the command history. By using “fc -4”, I’m able to retrieve the info mgr command and then execute it by simply by hitting return. Before hitting return, I could still edit the command as was shown previously.

Lastly, you can retrieve a previous command by searching for a specific string. The string has to be at the beginning of the command. In the below example, I’m searching for the last stats command that was ran.

GGSCI (fred.acme.com) 16> history

GGSCI Command History

    7: info egg12c
    8: info mgr
    9: history
   10: info egg12c
   11: history
   12: info mgr
   13: info all
   14: history
   15: stats extract egg12c
   16: history


GGSCI (fred.acme.com) 17> fc sta
GGSCI (fred.acme.com) 17> stats extract egg12c
GGSCI (fred.acme.com) 17..

Sending STATS request to EXTRACT EGG12C ...

No active extraction maps
DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
	Operations                		           2.00
	Mapped operations         		           2.00
	Unmapped operations         		           0.00
	Other operations         		           0.00
	Excluded operations         		           0.00

As you can see, there is only one stats command in the command history. The “FC” command pulls this command and provides an opportunity to edit it. After hitting return, the command is executed.

Now that you know how to recall and edit Oracle GoldenGate commands from the GGSCI command prompt, hopefully, this will make your life with Oracle GoldenGate a bit easier.

Enjoy!!

@dbasolved
http://about.me/dbasolved


Filed under: Golden Gate
Categories: DBA Blogs

Find duration of client times SQL Server

Tom Kyte - Mon, 2016-08-15 06:46
i have data with 3 columns: (column1,col2,col3) as (start date, incident status (Assigned/Inprogress/Resolved), incident_status_reason as (Client Action/ Client Follow) as below values: Start Date incident_status Incident_Status_Reason...
Categories: DBA Blogs

Select query in-consistent results

Tom Kyte - Mon, 2016-08-15 06:46
Hi Tom, We have an issue with one of our select queries. The query returns in-consistent results (at times) for the same input. The query is fired from a Web Application (named PIPES) using JDBC templates (Spring). A Transaction is created for ...
Categories: DBA Blogs

shrink space behavior of lob tablespace

Tom Kyte - Mon, 2016-08-15 06:46
Hi I'm trying to understand the shrink space behaviors when dealing with multiple tables with multiple lob columns sharing a single tablespace. We've 3 tables (e.g. T1, T2, T3) with each table having a lob column. And the lob columns are stored in...
Categories: DBA Blogs

multiple layers of aggregation

Tom Kyte - Mon, 2016-08-15 06:46
greetings experts, I haven't seen this matter among other questions, certainly won't mind having it pointed out if I've missed it. I have an awkward requirement from management that requires summing a set of values, and each value is often going t...
Categories: DBA Blogs

regarding subquery terminology

Tom Kyte - Mon, 2016-08-15 06:46
When we can use any query with ''From '' clause, can we call it subquery??? eg. select * from (select * from emp order by sal desc);
Categories: DBA Blogs

Hide sensitive Data

Tom Kyte - Mon, 2016-08-15 06:46
Hi ask tom team, I want to hide sensitive data when querying from SQL Plus or any other SQL tool (Toad, SQL Developer), but show data when accessing from application server. How can i do this ? preferably without physical access to database se...
Categories: DBA Blogs

oracle locks,blocks,deadlocks

Tom Kyte - Mon, 2016-08-15 06:46
what is mean by locks,blocks,deadlocks in oracle? Please explain with one good example because I am learner,use e.g(emp,dept ) tables
Categories: DBA Blogs

Oracle

Tom Kyte - Sun, 2016-08-14 12:26
There is one table.How sir. table1(input)...........target(output). 1 0000000001 12 0000000012 123 0000000123 1234 0000001234 12345 123456
Categories: DBA Blogs

ORA-02292: integrity constraint <constraint name> violated

Tom Kyte - Sun, 2016-08-14 12:26
Hello, I am attempting to delete a record / object (that may use several tables at the DB level) from an application. The delete statement is raising a ORA-02292: integrity constraint <constraint name> violated How can I find the Delete state...
Categories: DBA Blogs

Finished Mathematics for Computer Science class

Bobby Durrett's DBA Blog - Sat, 2016-08-13 17:07

Today I finally finished the Mathematics for Computer Science class that I have worked on since December. For the last year or two I have wanted to do some general Computer Science study in my free time that is not directly related to my work. I documented a lot of this journey in an earlier blog post.

The math class is on MIT’s OpenCourseWare (OCW) web site. It was an undergraduate semester class and I spent about 9 months on it mostly in my spare time outside of work. I wanted to test out OCW as a source for training just as I had experimented with edX before. So, I thought I would share my thoughts on the experience.

The class contained high quality material. It was an undergraduate class so it may not have been as deep as a graduate level class could be but world-class MIT professors taught the class. Some of my favorite parts of the video lectures were where professor Leighton made comments about how the material applied in the real world.

The biggest negative was that a lot of the problems did not have answers. Also, I was pretty much working through this class on my own. There were some helpful people on a Facebook group that some of my edX classmates created that helped keep me motivated. But there wasn’t a large community of people taking the same class.

Also, it makes me wonder where I should spend time developing myself. Should I be working more on my communication and leadership skills through Toastmasters? Should I be working on my writing? Should I be learning more Oracle features?

I spent months studying for Oracle’s 12c OCP certification exam and I kind of got burnt out on that type of study. The OCP exam has a lot of syntax. To me syntax, which you can look up in a manual, is boring. The underlying computer science is interesting. It is fun to try to understand the Oracle optimizer and Oracle internals, locking, backup and recovery, etc. There is a never-ending well of Oracle knowledge that I could pursue.

Also, there is a lot of cloud stuff going on. I could dive into Amazon and other cloud providers. I also have an interest in open source. MySQL and PostgreSQL intrigue me because I could actually have the source code.

But, there is only so much time in the day and I can’t do everything. I don’t regret taking the math for computer science class even if it was a diversion from my Toastmasters activities and not directly related to work. Now I have a feel for the kind of materials that you have on OCW: high quality, general computer science, mostly self-directed. Now I just have to think about what is next.

Bobby

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs