Many, many thanks to those who came to my three sessions at Sangam 18, the largest meeting of Oracle technologists in South Asia.
As I promised, you can download the presentations and scripts for all my sessions here. As always, your feedback will be highly appreciated.
The Arup Nanda Blog
Confessions of an Oracle Database Junkie - Arup Nanda The opinions expressed here are mine and mine alone. They may not necessarily reflect that of my employers and customers - both past or present. The comments left by the reviewers are theirs alone and may not reflect my opinion whether implied or not. None of the advice is warranted to be free of errors and ommision. Please use at your own risk and after thorough testing in your environment.
Sunday, December 09, 2018
Wednesday, October 24, 2018
OTW18 Sessions: Data Engineering and Latch Contentions
Thank you all those who came to my sessions at the OakTable World. Here are the presentations materials
Think Data; not Database
Data Engineering for Data Science
Latch Contentions Presentation Scripts
Think Data; not Database
Data Engineering for Data Science
Latch Contentions Presentation Scripts
Monday, October 22, 2018
OOW18 Session: Jumpstarting Docker
Thank you all for honoring me with a standing room only session, even at towards the end of the day. Much appreciated. Here is the presentation materials I used in the session. Please feel free to contact me if you need additional information.
Presentation
Presentation
Wednesday, April 25, 2018
Collaborate 18 Session: Networking for the Cloud DBA
Thank you all for coming to my session today. In my years of experience managing DBAs, I find that the basic concepts of networking are always the weakest link in the knowledge to get things together, at least for the DBAs. In this session you learned how netmask, CIDR, routing table works, how that fits together in a cloud world, where a knowledge of networking is not just nice to have; but an absolute must.
Download the preso here. As always I highly appreciate your comments, here as comments or directly to me via twitter @ArupNanda or by email arup@proligence.com.
Download the preso here. As always I highly appreciate your comments, here as comments or directly to me via twitter @ArupNanda or by email arup@proligence.com.
Tuesday, April 24, 2018
Collaborate 18 Session: Microservices Using Python and Oracle
Many thanks to all those who came to my session at Collaborate 18 today. I sincerely hope you enjoyed the session and learned something new in the process.
Here are the files you can download:
1. The presentation
2. The list_avail template
3. The hotel.py python program (download the file and remove the .txt extension)
4. The hoteld.py python program
In case you wanted to visit the Python learning series I wrote, here is where you can find it. bit.ly/python4plsql
As I mentioned, I presented about Oracle and Python at Oracle Code event a couple of months ago in New York City. Here is where you can get the scripts and the presentation.
Here are the files you can download:
1. The presentation
2. The list_avail template
3. The hotel.py python program (download the file and remove the .txt extension)
4. The hoteld.py python program
In case you wanted to visit the Python learning series I wrote, here is where you can find it. bit.ly/python4plsql
As I mentioned, I presented about Oracle and Python at Oracle Code event a couple of months ago in New York City. Here is where you can get the scripts and the presentation.
Monday, April 23, 2018
Collaborate18 Session: Real Life DBA Best Practices
Thank you all those came for my session at IOUG Collaborate 18 today. I hope you learned something new and inspired by something to make your task a bit easier, a bit more efficient and a whole lot enjoyable.
You can download the presentation here.
Remember, a best practice is something that can be explained as:
1) why it is good, or what specific good comes by following it
2) what happens if you don't follow it
3) what circumstances it doesn't apply, if any
If you hear a "best practice" without any of these, reject it politely. And most of all, create your own best practices and promote them. We all deserve to learn something new to make our collective life a bit easier.
You can download the presentation here.
Remember, a best practice is something that can be explained as:
1) why it is good, or what specific good comes by following it
2) what happens if you don't follow it
3) what circumstances it doesn't apply, if any
If you hear a "best practice" without any of these, reject it politely. And most of all, create your own best practices and promote them. We all deserve to learn something new to make our collective life a bit easier.
Tuesday, January 23, 2018
Western Europe 2018 Tour
Many thanks to all those who graced my sessions during my Western Europe 2018 Tour in Paris, Lausanne, Milan and Rome. You can download the materials presented here http://bit.ly/2DA0ae9.
Friday, January 19, 2018
Boston Cloud SIG Session: Docker Demystified
Thank you to all for coming to my session for North East Oracle User Group (Boston), more specifically the first ever Cloud SIG meetup a Burlington, MA on Jan 18th. It was heartening to see a 100+ crowd for a session that spanned all the way up to 9:30 PM on a week day. I sincerely hope you enjoyed it.
Here is the presentation for you to download: http://bit.ly/2FU7PRY
Here is the presentation for you to download: http://bit.ly/2FU7PRY
Monday, July 31, 2017
Uruguay User Group Session: Secure Your Database in 1 Day
Thursday, July 13, 2017
Role of # in SQL*Plus
The # character is for commenting in SQL*Plus, right?
The character # has been mostly used for comments in many languages, such as shell scripts and python. Interestingly # is legal syntax in SQL scripting as well; but is it considered a comment? The answer is no; it's not. The purpose of # in SQL scripts is very different. and you should be very careful using it.
Entering # tells SQL*Plus to temporarily pauses what has been entered before and execute everything after that #sign, as if in a different session. Here is a usecase. Suppose you are writing this query:
SQL> select *
2 from v$sesstat
3 where
[Typographical Conventions: User inputs are shown bold. System outputs are not.]
At this time you are stuck. You don't remember the column names of V$SESSTAT. But you need to know that to use it as a predicate. What can you do? You could just press ENTER at this point, which causes the SQL*Plus prompt to be redisplayed. You can describe the view and that will be your answer. However, this means you will have lost all the input you have entered until then. In case of a complicated long query it is hardly desirable.
This is where the # symbol comes to help. Just type # and enter the command you want to be displayed. In this case you want to describe the view. So enter at the 4th line prompt of SQL*Plus:
4 #desc v$sesstat
When you enter this command and press ENTER, SQL*Plus will halt the evaluation of all the command it is doing now and execute the desc v$sesstat command. Here will be the output:
Name Null? Type
----------------------------------------- -------- ----------
SID NUMBER
STATISTIC# NUMBER
VALUE NUMBER
CON_ID NUMBER
After this display, the SQL*Plus prompt will show the 4th line as the prompt "4". Now that you know the column, enter the rest of the SQL statement you were entering
4 con_id = 1
5 /
And that's it. Note how SQL*Plus didn't discard any of the commands already entered. Instead it merely paused the evaluation and started evaluating the command "desc v$sesstat" after the # symbol. This symbol "#" is not a comment character but a temporary new command without discarding the previous commands in play in the session.
SQL> create table t (col1 number);
Table created.
SQL> insert into t values (1);
1 row created.
SQL> select count(1)
2 from t
3 where 1 > 0
4 /
COUNT(1)
----------
1
We got the results as expected. Now let's introduce a small line #roll in between the commands. Perhaps someone assumed it was a comment and left it there.
SQL> select count(1)
2 #roll
Rollback complete.
2 from t
3 where 1 > 0
4 /
COUNT(1)
----------
0
Note how the counts came back as 0. Why? It's because the rollback occurred independently of the select command. By the time the select statement was completed, the rollback statement had rolled back the insert and hence there were no rows in the table.
In some cases this behavior might prove deadly. Here is another example I have seen, from a script that shuts down the database, takes a storage snapshot and restarts it.
shutdown abort
startup
Later it was decided that shutdown abort is not desirable. It should be shutdown transactional. So, the DBA changed the script to the following. Assuming the # character to be comment, she thought she was commenting the line instead of removing it:
#shutdown abort
shutdown transactional
startup
Now let's run the script:
SQL> @b
ORACLE instance shut down.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0
ORACLE instance started.
Total System Global Area 5117050880 bytes
Fixed Size 8757424 bytes
Variable Size 1056968528 bytes
Database Buffers 4043309056 bytes
Redo Buffers 8015872 bytes
Database mounted.
Database opened.
What happened? The shutdown failed with "ORACLE not available". Why?
The answer is simple. The shutdown abort executed. The database was already down. Therefore the shutdown transactional failed.
SQL> set sqlprefix *
Now, let's repeat the same example we used earlier but with * instead of #:
SQL> select count(1)
2 * desc t
Name Null? Type
----------------------------------------- -------- --------
COL1 NUMBER
2 from t;
COUNT(1)
----------
0
What if you use # character in this case?
SQL> select count(1)
2 # desc t
3 from t;
# desc t
*
ERROR at line 2:
ORA-00911: invalid character
You will not go anywhere. You will get an error. It's probably better than an assumption of # character as a comment.
The character # has been mostly used for comments in many languages, such as shell scripts and python. Interestingly # is legal syntax in SQL scripting as well; but is it considered a comment? The answer is no; it's not. The purpose of # in SQL scripts is very different. and you should be very careful using it.
Entering # tells SQL*Plus to temporarily pauses what has been entered before and execute everything after that #sign, as if in a different session. Here is a usecase. Suppose you are writing this query:
SQL> select *
2 from v$sesstat
3 where
[Typographical Conventions: User inputs are shown bold. System outputs are not.]
At this time you are stuck. You don't remember the column names of V$SESSTAT. But you need to know that to use it as a predicate. What can you do? You could just press ENTER at this point, which causes the SQL*Plus prompt to be redisplayed. You can describe the view and that will be your answer. However, this means you will have lost all the input you have entered until then. In case of a complicated long query it is hardly desirable.
This is where the # symbol comes to help. Just type # and enter the command you want to be displayed. In this case you want to describe the view. So enter at the 4th line prompt of SQL*Plus:
4 #desc v$sesstat
When you enter this command and press ENTER, SQL*Plus will halt the evaluation of all the command it is doing now and execute the desc v$sesstat command. Here will be the output:
Name Null? Type
----------------------------------------- -------- ----------
SID NUMBER
STATISTIC# NUMBER
VALUE NUMBER
CON_ID NUMBER
After this display, the SQL*Plus prompt will show the 4th line as the prompt "4". Now that you know the column, enter the rest of the SQL statement you were entering
4 con_id = 1
5 /
And that's it. Note how SQL*Plus didn't discard any of the commands already entered. Instead it merely paused the evaluation and started evaluating the command "desc v$sesstat" after the # symbol. This symbol "#" is not a comment character but a temporary new command without discarding the previous commands in play in the session.
Unintended Ramifications
This behavior of # character may result in some scenarios not at all intended. Take for instance the following scenario. We create a table, insert a row, do not commit and then check the number of records.SQL> create table t (col1 number);
Table created.
SQL> insert into t values (1);
1 row created.
SQL> select count(1)
2 from t
3 where 1 > 0
4 /
COUNT(1)
----------
1
We got the results as expected. Now let's introduce a small line #roll in between the commands. Perhaps someone assumed it was a comment and left it there.
SQL> select count(1)
2 #roll
Rollback complete.
2 from t
3 where 1 > 0
4 /
COUNT(1)
----------
0
Note how the counts came back as 0. Why? It's because the rollback occurred independently of the select command. By the time the select statement was completed, the rollback statement had rolled back the insert and hence there were no rows in the table.
In some cases this behavior might prove deadly. Here is another example I have seen, from a script that shuts down the database, takes a storage snapshot and restarts it.
shutdown abort
startup
Later it was decided that shutdown abort is not desirable. It should be shutdown transactional. So, the DBA changed the script to the following. Assuming the # character to be comment, she thought she was commenting the line instead of removing it:
#shutdown abort
shutdown transactional
startup
Now let's run the script:
SQL> @b
ORACLE instance shut down.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0
ORACLE instance started.
Total System Global Area 5117050880 bytes
Fixed Size 8757424 bytes
Variable Size 1056968528 bytes
Database Buffers 4043309056 bytes
Redo Buffers 8015872 bytes
Database mounted.
Database opened.
What happened? The shutdown failed with "ORACLE not available". Why?
The answer is simple. The shutdown abort executed. The database was already down. Therefore the shutdown transactional failed.
Don't Like #?
Now that you see how some people may confuse # as a comment and make mistakes, you may not like to have this special property assigned to #. Instead, you may want another character, say "*". You can easily change it. The special setting in SQL*Plus called SQLPREFIX allows you to do that:SQL> set sqlprefix *
Now, let's repeat the same example we used earlier but with * instead of #:
SQL> select count(1)
2 * desc t
Name Null? Type
----------------------------------------- -------- --------
COL1 NUMBER
2 from t;
COUNT(1)
----------
0
What if you use # character in this case?
SQL> select count(1)
2 # desc t
3 from t;
# desc t
*
ERROR at line 2:
ORA-00911: invalid character
You will not go anywhere. You will get an error. It's probably better than an assumption of # character as a comment.
Takeaways
- The # character is not a comment in SQL scripts.
- When SQL*Plus encounters a # character at the first position, it temporarily pauses the evaluation of the statements being entered and immediately executes the command after # and resumes evaluation of the previously entered commands afterwards.
- You can assign another character to exhibit this behavior by setting the SQLPREFIX to that character.
Monday, July 10, 2017
Enough with AFIEDT.BUF
You are in SQL*Plus. You entered a command and urgh, there was a typo. No worries, you bring up the command in an editor by typing:
SQL> ed
This opens up an editor, such as notepad.exe in Windows or vi in Unix, etc. And it puts the last SQL you entered in a file, oddly named, afiedt.buf. You don't like it and you want a name easier to type. Is it possible? Of course.
Later as UFI was added new features, it was called Advanced UFI (well, "advanced" is relative). To keep pace, the generated file was called Advanced UFI Editor Buffer, or aufiedt.buf. However an operating system that Oracle supported couldn't handle a 7 character file name before the period before suffix. Therefore the name was shortened to afiedt.buf to keep it distinct from ufiedt.buf (which presumably was not as "advanced"). As the advancements in technology came, Oracle didn't bother to change it and the name stuck. Long live afiedt.buf!
SQL> set editfile arup.sql
After this when you type the editor command:
SQL> ed
Wrote file arup.sql
Look how the file created was named "arup.sql"; not afiedt.buf. Of course you can use any name as you find appropriate.
Let's handle the second problem. You are in a directory where you don't have write permission and you want to edit the command. Well, you can use a full path to the file as well.
SQL> set editfile c:\temp\arup.sql
SQL> ed
Wrote file c:\temp\arup.sql
The file is created in the C:\temp directory instead of the default option of the current directory.
Very simple; another setting _EDITOR allows us to do that. It's a variable; so you have to use DEFINE command. Use the following:
SQL> define _editor = vi
That's it; now when you enter "ed" command, it will bring up vi editor. Change to whatever you want to set.
Afiedt.Buf is dead. Long live Afedit.Buf. Or, was it Afiedt.Buf? Oh, who cares?
SQL> ed
This opens up an editor, such as notepad.exe in Windows or vi in Unix, etc. And it puts the last SQL you entered in a file, oddly named, afiedt.buf. You don't like it and you want a name easier to type. Is it possible? Of course.
History of Afiedt.Buf
First a little bit of background information on the odd name. SQL*Plus as a tool evolved from another tool Oracle provided a long, long time ago, called--rather uncreatively--User Friendly Interface, or UFI. When the editor wanted to bring up a file for editing, the file had to be given a name unique enough so as not to conflict with anything else. Therefore the file was named ufiedt.buf, which roughly indicated UFI Editor Buffer.Later as UFI was added new features, it was called Advanced UFI (well, "advanced" is relative). To keep pace, the generated file was called Advanced UFI Editor Buffer, or aufiedt.buf. However an operating system that Oracle supported couldn't handle a 7 character file name before the period before suffix. Therefore the name was shortened to afiedt.buf to keep it distinct from ufiedt.buf (which presumably was not as "advanced"). As the advancements in technology came, Oracle didn't bother to change it and the name stuck. Long live afiedt.buf!
Why Change It?
If ain't broken, don't fix it. Shouldn't that be the case? The name doesn't break anything; so why change it? There are three reasons to at least consider.- The name may difficult to type.
- he extension of ".buf" is not automatically recognized by SQL*Plus as a SQL script.
- The file is created in the present directory. Suppose you are in a directory you don't have permission on, e.g. "/etc" in Unix or \Windows in Windows, you can't just bring up the editor as you won't have the permissions to create the file there. You will get this error:
SP2-0110: Cannot create save file "afiedt.buf"
- What do you do? Exit from SQL*Plus, change to a proper directory and re-execute this command? Then you would lose all the settings you may have done in that session. So you would want to create the file in a location of your choosing.
So, while you mean no disrespect to Oracle's legacy, you would want to relegate afiedt.buf to where it belongs--to the footnotes of history.
How can I Change It?
It's ridiculously simple to change the file. A setting in SQL*Plus controls it.SQL> set editfile arup.sql
After this when you type the editor command:
SQL> ed
Wrote file arup.sql
Look how the file created was named "arup.sql"; not afiedt.buf. Of course you can use any name as you find appropriate.
Changing Directory
Let's handle the second problem. You are in a directory where you don't have write permission and you want to edit the command. Well, you can use a full path to the file as well.
SQL> set editfile c:\temp\arup.sql
SQL> ed
Wrote file c:\temp\arup.sql
The file is created in the C:\temp directory instead of the default option of the current directory.
Changing the Editor
While on the subject, do you know that you can change the default editor as well? For instance, if you are on Windows, you don't have to have Notepad as the editor for SQL scripts. It can be notepad++, or, as I prefer, "vim", which is enhanced vi. In Unix, the default is not vi; it's "ed" and you may want to change it. How to do that?Very simple; another setting _EDITOR allows us to do that. It's a variable; so you have to use DEFINE command. Use the following:
SQL> define _editor = vi
That's it; now when you enter "ed" command, it will bring up vi editor. Change to whatever you want to set.
Tips for Practical Uses
If you want to change the default editor and the default file for editing, put the commands in the glogin.sql file, which ensures their automatic execution when someone invokes SQL*Plus.Afiedt.Buf is dead. Long live Afedit.Buf. Or, was it Afiedt.Buf? Oh, who cares?
Friday, July 07, 2017
Setting APPINFO in SQL*Plus
Ever used the MODULE column of V$SESSION view? If you haven't, you are
missing out on a very important piece of instruemntation code built right into
the Oracle database session management. This allows you to assign a completely
arbitary name, as you would see will properly describe for your application.
Later when you want to identify that session, you can check the MODULE column in
V$SESSION. Even though the userid is the same for all these sessions; the module
column will help you identify the session.
Let's see how it works by a little example. Here is how you check the module information.
In both cases the MODULE column shows the same value. You can set he module to a more descriptive name by issuing this command:
Now if you check the module:
As you can see the session where executed the packaged procedure has the descriptive module name. If you want to enable tracing, check the sessions stats, debug what's going on, etc., the ability to identify the session uniquely and accurately could be lifesaver.
But we had to execute the package dbms_application_info. The principles of least privileges says that we don't want to grant more privileges than absolutely necessary. While no one will argue against this being convenient, it will probably be hard to justify as "absolutely" necessary. So, what could you do to identify the session via the module? The generic module name "sqlplus.exe" is pretty much useless.
There is a much simpler solution. The SQL*Plus parameter appinfo comes to rescue. By default the setting is off. You can confirm that by issuing the following:
To set to a value you want, use the follwoing:
Now if you check the sessions from another sessions:
See how the module is set to MyApp, which allows you to locate the session from many from the same user. And you could do that without calling the package.
What are the practical implications? The best usecase, I think is using this in the automatic login scripts such as glogin.sql. Put the following line in gloin.sql in $ORACLE_HOME/sqlplus/admin directory.
Now any session using that Oracle Home will have the Module column set to AcmeModule.
However you can also create local files called login.sql in individual directories and set appinfo apprpriately. For instance, suppose you have three major directories where you generally run the SQL scripts from. Create login.sql files on each directory. Directory "C:\App1" has a login.sql file with the following content:
Create login.sql files in the other directories with the appropriate contents. Now when someone connects to the database from SQL*Plus from those directories, the module will be set appropriately.
You will be able to know what directory the user has been in calling the scripts.
A caveat on the above, though. In Oracle 12.2, login.sql file is not executed, if a glogin.sql file is present. It is silently ignored. To make sure the local login.sql file is read, you have to explcitly set the variable ORACLE_PATH or SQLPATH to that directory.
Hope you find use of this little known setting in SQL*Plus.
Let's see how it works by a little example. Here is how you check the module information.
select module from v$session where username = 'SYS' MODULE ----------------------------- sqlplus.exe sqlplus.exe
In both cases the MODULE column shows the same value. You can set he module to a more descriptive name by issuing this command:
SQL> exec dbms_application_info.set_module('MyModule','MyAction') PL/SQL procedure successfully completed.
Now if you check the module:
MODULE --------------- MyModule sqlplus.exe
As you can see the session where executed the packaged procedure has the descriptive module name. If you want to enable tracing, check the sessions stats, debug what's going on, etc., the ability to identify the session uniquely and accurately could be lifesaver.
But we had to execute the package dbms_application_info. The principles of least privileges says that we don't want to grant more privileges than absolutely necessary. While no one will argue against this being convenient, it will probably be hard to justify as "absolutely" necessary. So, what could you do to identify the session via the module? The generic module name "sqlplus.exe" is pretty much useless.
There is a much simpler solution. The SQL*Plus parameter appinfo comes to rescue. By default the setting is off. You can confirm that by issuing the following:
SQL> show appinfo appinfo is OFF and set to "SQL*Plus"
To set to a value you want, use the follwoing:
SQL> set appinfo MyApp
Now if you check the sessions from another sessions:
SQL> select module 2 from v$session 3 where username = 'SYS'; MODULE ------------------------------- MyApp sqlplus.exe
See how the module is set to MyApp, which allows you to locate the session from many from the same user. And you could do that without calling the package.
What are the practical implications? The best usecase, I think is using this in the automatic login scripts such as glogin.sql. Put the following line in gloin.sql in $ORACLE_HOME/sqlplus/admin directory.
set appinfo "AcmeModule"
Now any session using that Oracle Home will have the Module column set to AcmeModule.
However you can also create local files called login.sql in individual directories and set appinfo apprpriately. For instance, suppose you have three major directories where you generally run the SQL scripts from. Create login.sql files on each directory. Directory "C:\App1" has a login.sql file with the following content:
set appinfo "App1"
Create login.sql files in the other directories with the appropriate contents. Now when someone connects to the database from SQL*Plus from those directories, the module will be set appropriately.
You will be able to know what directory the user has been in calling the scripts.
A caveat on the above, though. In Oracle 12.2, login.sql file is not executed, if a glogin.sql file is present. It is silently ignored. To make sure the local login.sql file is read, you have to explcitly set the variable ORACLE_PATH or SQLPATH to that directory.
Hope you find use of this little known setting in SQL*Plus.
Tuesday, June 13, 2017
E4 Session Developing Agnostic Data Services
Thank you for all attending my virtual session Developing Location and Technology Agnostic Data Services at Accenture Enkitec's E4 2017 Conference.
Here is the presentation deck for your reference. http://www.proligence.com/pres/e4_2017/e4_17_data.pdf
As always, I will appreciate your feedback--the good, the bad and the ugly.
Here is the presentation deck for your reference. http://www.proligence.com/pres/e4_2017/e4_17_data.pdf
As always, I will appreciate your feedback--the good, the bad and the ugly.
NYOUG Session How Oracle Buffer Cache Works
Thank you all for coming to my session How Oracle Buffer Cache Works at New York Oracle User Group Summer General Meeting in New York City.
You can download the session and scripts used for the demo here.
Presentation : http://www.proligence.com/pres/nyoug17/nyoug17_buffercache.pdf
Scripts : http://www.proligence.com/pres/nyoug17/nyoug17_buffercache_scripts.zip
As I mentioned during my talk, you may find these blogposts helpful to understand this more:
How Oracle Locking Works http://arup.blogspot.com/2011/01/how-oracle-locking-works.html
Cache Buffer Chains Demystified http://arup.blogspot.com/2014/11/cache-buffer-chains-demystified.html
100 Things You Didn't Know About Oracle http://arup.blogspot.com/2010/12/100-things-you-probably-didnt-know.html
More on Buffer Clones http://arup.blogspot.com/2011/04/can-i-fit-80mb-database-completely-in.html
You can download the session and scripts used for the demo here.
Presentation : http://www.proligence.com/pres/nyoug17/nyoug17_buffercache.pdf
Scripts : http://www.proligence.com/pres/nyoug17/nyoug17_buffercache_scripts.zip
As I mentioned during my talk, you may find these blogposts helpful to understand this more:
How Oracle Locking Works http://arup.blogspot.com/2011/01/how-oracle-locking-works.html
Cache Buffer Chains Demystified http://arup.blogspot.com/2014/11/cache-buffer-chains-demystified.html
100 Things You Didn't Know About Oracle http://arup.blogspot.com/2010/12/100-things-you-probably-didnt-know.html
More on Buffer Clones http://arup.blogspot.com/2011/04/can-i-fit-80mb-database-completely-in.html
Wednesday, May 17, 2017
Pesentations at Great Lakes Oracle Conference 2017
For those who came to my sessions at GLOC 2017 in Cleveland, thank you for giving me your time. I hope you found them useful. As always, I love to hear your feedback. You can add a comment here, email me at arup@proligence.com, tweet at @ArupNanda or at www.Facebook.com/ArupKNanda.
All about Deadlocks The Presentation The Tracefile Used as an Example
How Buffer Cache Works The Presentation The Scripts
Big Data for Oracle DBAs The Presentation
Again, appreciate your coming to my session and thanks in advance for your feedback.
All about Deadlocks The Presentation The Tracefile Used as an Example
How Buffer Cache Works The Presentation The Scripts
Big Data for Oracle DBAs The Presentation
Again, appreciate your coming to my session and thanks in advance for your feedback.
Friday, April 28, 2017
Passing Single Quotes in DBMS Assert Package
Today, while describing the usefulness of DBMS_ASSERT package to prevent SQL and PL/SQL Injection attacks someone asked me how to pass a string with single quotes successfully to this package.
First, if you don't know what DBMS_Assert is or why you should know about it, check out the presentation on this blogpost. In summary, the ENQUOTE_LITERAL() function strips off all the single quotes from around the string and replace with just a pair of single quotes, which makes it a clean, uninjected string. Here is an example of a string called Joe Pizza.
SQL> select dbms_assert.enquote_literal('Joe Pizza') from dual;
DBMS_ASSERT.ENQUOTE_LITERAL('JOEPIZZA')
---------------------------------------
'Joe Pizza'
As you can see, the string is presented back with a pair of single quotes. Now let's see what happens if we put another pair of single quotes. To be syntactically correct, we will need to escape the single quote with another single quote.
SQL> select dbms_assert.enquote_literal('''Joe Pizza''') from dual;
DBMS_ASSERT.ENQUOTE_LITERAL('''JOEPIZZA''')
-------------------------------------------
'Joe Pizza'
What happened? Well, the Assert package stripped off all extraneous single quotes and replaced them with just one pair of single quotes.
But what happens when we need to put a single single quote as a legitimate character, e.g. Joe's Pizza? This is where the escape sequence in SQL comes in. You can write this as follows:
SQL> select dbms_assert.enquote_literal(q'[Joe''s Pizza]') from dual;
DBMS_ASSERT.ENQUOTE_LITERAL(Q'[JOE''SPIZZA]')
---------------------------------------------
'Joe''s Pizza'
That's it; "Joe's Pizza" is now perfectly passed.
Wait a minute. It's not Joe's Pizza; it's Joe''s Pizza. There are two single quotes; not one. That's not what we intended, did we? So it's wrong, right?
No; it's perfectly fine. You see, when you pass "Joe's Pizza" inside single quotes, you must escape the single apostrophe inside. The escape character is the single quote. Since the Assert package puts single quotes around the string, the single quote inside the string must be escaped to be syntactically correct. So, it makes sense to have two single quotes; not just one.
Joe's Pizza--if there is something in real world in that name--should probably pay me for the publicity.
First, if you don't know what DBMS_Assert is or why you should know about it, check out the presentation on this blogpost. In summary, the ENQUOTE_LITERAL() function strips off all the single quotes from around the string and replace with just a pair of single quotes, which makes it a clean, uninjected string. Here is an example of a string called Joe Pizza.
SQL> select dbms_assert.enquote_literal('Joe Pizza') from dual;
DBMS_ASSERT.ENQUOTE_LITERAL('JOEPIZZA')
---------------------------------------
'Joe Pizza'
As you can see, the string is presented back with a pair of single quotes. Now let's see what happens if we put another pair of single quotes. To be syntactically correct, we will need to escape the single quote with another single quote.
SQL> select dbms_assert.enquote_literal('''Joe Pizza''') from dual;
DBMS_ASSERT.ENQUOTE_LITERAL('''JOEPIZZA''')
-------------------------------------------
'Joe Pizza'
What happened? Well, the Assert package stripped off all extraneous single quotes and replaced them with just one pair of single quotes.
But what happens when we need to put a single single quote as a legitimate character, e.g. Joe's Pizza? This is where the escape sequence in SQL comes in. You can write this as follows:
SQL> select dbms_assert.enquote_literal(q'[Joe''s Pizza]') from dual;
DBMS_ASSERT.ENQUOTE_LITERAL(Q'[JOE''SPIZZA]')
---------------------------------------------
'Joe''s Pizza'
That's it; "Joe's Pizza" is now perfectly passed.
Wait a minute. It's not Joe's Pizza; it's Joe''s Pizza. There are two single quotes; not one. That's not what we intended, did we? So it's wrong, right?
No; it's perfectly fine. You see, when you pass "Joe's Pizza" inside single quotes, you must escape the single apostrophe inside. The escape character is the single quote. Since the Assert package puts single quotes around the string, the single quote inside the string must be escaped to be syntactically correct. So, it makes sense to have two single quotes; not just one.
Joe's Pizza--if there is something in real world in that name--should probably pay me for the publicity.
Thursday, April 27, 2017
Preventing SQL and PL/SQL Attacks at New York Meetup
Thank you all for attending my session at New York Meetup and New York Oracle User Group Spring Meeting in New York City on April 27th. I am truly honored by your presence, and especially for the questions.
You can download the presentation here And the scripts I used for the demos here. As always, I will appreciate your feedback either via comments here, or on social media or emails.
Twitter @ArupNanda
Facebook.com/ArupKNanda
You can download the presentation here And the scripts I used for the demos here. As always, I will appreciate your feedback either via comments here, or on social media or emails.
Twitter @ArupNanda
Facebook.com/ArupKNanda
Friday, January 13, 2017
Python for PL/SQL Developers Series
How hard it is to learn a new language? It depends on the learner, of course; but everyone will agree that it is easier to get your feet wet if you learn the corresponding structures of a language you are already proficient in. That's the principle behind my new article series on Oracle Technology Network: Python for PL/SQL Developers. If you are at least an intermediate level in PL/SQL, you will jumpstart your learning Python by learning the corresponding syntax elements, which is almost always a guaranteed way to learn the meat of the language fairly quickly. I learned most of the languages that way, and this is an experiment to do the same here as well.
Designed as a five part series, it starts off with the basics such as variables and assignments. then it gradually progresses to conditions, loops, functions, modules, etc. Finally, it explains how to use publicly available packages for data manipulation, including the ones to connect to the Oracle database. The idea is to have you up and running in Python by committing about an hour every day for a week.
Too busy to read? I have developed a video for each part. Watch it and go at it. Each part also has a quiz to test your knowledge gained from that part.
Here it is: http://bit.ly/python4plsql
As always, I will be successful only if you (and only you) like it. I will appreciate your honest feedback on this series. How you liked it (or, not); what are the strengths; what areas could have been improved and so on. Post as comments here, tweet @ArupNanda, post at www.FaceBook.com/ArupKNanda or just email me at arup@proligence.com.
Designed as a five part series, it starts off with the basics such as variables and assignments. then it gradually progresses to conditions, loops, functions, modules, etc. Finally, it explains how to use publicly available packages for data manipulation, including the ones to connect to the Oracle database. The idea is to have you up and running in Python by committing about an hour every day for a week.
Too busy to read? I have developed a video for each part. Watch it and go at it. Each part also has a quiz to test your knowledge gained from that part.
Here it is: http://bit.ly/python4plsql
As always, I will be successful only if you (and only you) like it. I will appreciate your honest feedback on this series. How you liked it (or, not); what are the strengths; what areas could have been improved and so on. Post as comments here, tweet @ArupNanda, post at www.FaceBook.com/ArupKNanda or just email me at arup@proligence.com.
Wednesday, November 23, 2016
A Million Thanks to _You_
Tomorrow is Thanksgiving holiday in the United States. Millions of Americans will travel this weekend to be with family, away from the family, visit somewhere fun, or for some unfortunate few, be stuck at work or stuck in the traffic. Amid loads of turkey, stuffing and the oft-present alcohol, it's easy to forge the concept behind this holiday: it's about giving thanks. It's a day to remember, acknowledge and celebrate all those who made us who we are today, and offer our thanks.
I take this moment to show my immense gratitude to all those who have inspired and encouraged me to speak, write and teach. I have always been a mentor and loved to teach and write. I first presented in public exactly 14 years ago in Richmond, VA. My first published article came out later that year in New York Oracle User Group publication TechJournal. And my first book was published a year later. Since then I have had the opportunity to speak at 422 sessions in 24 counties, deliver 35 training seminars, write 517 published articles and 6 books. And, I could do that just for the support and inspiration provided by the readers and attendees who I had the pleasure of delivering the sessions and writings.
Here is a map of the places I presented. It's not complete; I put what I could remember. https://drive.google.com/open?id=1EenMw3RKyKc2AxUMuNCj81lTywo&usp=sharing
I am truly honored to have you read my work or listen to me. For this I am thankful to you, dear reader, attendee or the visitor to my blog.
Happy Thanksgiving!
I take this moment to show my immense gratitude to all those who have inspired and encouraged me to speak, write and teach. I have always been a mentor and loved to teach and write. I first presented in public exactly 14 years ago in Richmond, VA. My first published article came out later that year in New York Oracle User Group publication TechJournal. And my first book was published a year later. Since then I have had the opportunity to speak at 422 sessions in 24 counties, deliver 35 training seminars, write 517 published articles and 6 books. And, I could do that just for the support and inspiration provided by the readers and attendees who I had the pleasure of delivering the sessions and writings.
Here is a map of the places I presented. It's not complete; I put what I could remember. https://drive.google.com/open?id=1EenMw3RKyKc2AxUMuNCj81lTywo&usp=sharing
I am truly honored to have you read my work or listen to me. For this I am thankful to you, dear reader, attendee or the visitor to my blog.
Happy Thanksgiving!
Friday, November 11, 2016
Sangam16 Session: Resolving Latch Contention
Many thanks to those who came to my session at Sangam, the annual conference of All India Oracle Users Group. You can download the presentation materials here:
Presentation here
Scripts here
As always, I will greatly appreciate to hear your thoughts on the session.. Please reach out to me via a comment on this post, or on the following:
Email: arup@proligence.com
Twitter: @ArupNanda
Facebook: ArupKNanda
Google+: +ArupNanda
Presentation here
Scripts here
As always, I will greatly appreciate to hear your thoughts on the session.. Please reach out to me via a comment on this post, or on the following:
Email: arup@proligence.com
Twitter: @ArupNanda
Facebook: ArupKNanda
Google+: +ArupNanda
Subscribe to:
Posts (Atom)