Say Hello to Oracle Apex and for the new Blog member too!

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.

get_workspace

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.

WLST easeSyntax

Who works with WLST know it’s pretty boring to natigate to MBeans, because whenever necessary to put in parentheses () commands and quotation marks ‘ ‘. When we forget, need to retype the whole command again.
I found a command that helps a lot when it comes to navigate in MBean tree, it eliminates the need for parentheses and quotation marks.
After entering the WLST, type:

wls:/xpto_domain/serverConfig> easeSyntax()

wls:/xpto_domain/serverConfig> ls
dr– AdminConsole

dr– SelfTuning
dr– Servers
dr– ShutdownClasses
dr– SingletonServices

wls:/xpto_domain/serverConfig> cd Servers
wls:/xpto_domain/serverConfig/Servers> ls
dr– AdminServer
dr– WLS1_MSWS1
dr– WLS1_MSWS2

wls:/xpto_domain/serverConfig/Servers> cd WLS1_MSWS1
wls:/xpto_domain/serverConfig/Servers/WLS1_MSWS1> cd Log
wls:/xpto_domain/serverConfig/Servers/WLS1_MSWS1/Log> cd ..
wls:/xpto_domain/serverConfig/Servers/WLS1_MSWS1> cd Machine
wls:/xpto_domain/serverConfig/Servers/WLS1_MSWS1/Machine> ls
dr– app1wsmachine1

Not tested within python scripts, only browsing the tree Mbean.

Jackson.

Script: Copy Large Table Through DBLink

To understand the situation:

Task: Need to migrate large database 11.1.0.6 to 12c Multi-Tenant Database with minimum downtime.
To better use the features, reorginize objects and compress data, I decided to migrate the data logically (not physically).
The first option was to migrate schema by schema through datapump with database link. There is no long columns.

Problem1: The database was veeery slow with perfect match to Bug 7722575 -DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp.
workaround: None
Solution: Upgrade to 11.2. (No way).
Other things: Yes, I tried to change the cursor sharing, the estimate from blocks to statistics and all things documented. It doesn’t work.

Ok doke! Let’s use traditional exp/imp tools (with some migration area), right?
Problem2: ORA-12899 on import related to multiblocking x singleblocking charsets.
Solution: https://grepora.com/2015/11/20/charsets-single-byte-vs-multibyte-issue/
:)

Done? Not for all. For some tables, just happened the error:

EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully

An what Oracle says? “Solution: Use Datapump!”😛

Well, well… I realized I was going to become by myself…
Ok, so lets create table as select using database link. For most of all, ok…
But, for example, one of the missing tables has 700 million rows (350GB of compressed and no partitioned data).
Just to remmember that DBLink exclude parallel options (always serial).

The solution was to make a McGayver, this way:
1) Creating an aux table (source database):

alter session force parallel query parallel 12;
create table SCHEMA_OWNER.AUX_ROWID(ROW_ID,NUM) as select rowid, rownum from SCHEMA_OWNER.TABLE;
alter session disable parallel query;

* This table will be used to break the table in chunks.

2) Script run_chunck.sql to run each chunk of data:

DECLARE
counter number;
CURSOR cur_data is
select row_id from (
select row_id, num from SCHEMA_OWNER.AUX_ROWID@SOURCEDB order by num)
where num >= &1
and num <=&2;
BEGIN
counter :=0;
FOR x IN cur_data LOOP
BEGIN
counter := counter +1;
insert into SCHEMA_OWNER.TABLE select * from SCHEMA_OWNER.TABLE@SOURCEDB where rowid = x.row_id;
if counter = 1000 then ---commit every 1000 rows
commit;
counter := 0;
end if;
EXCEPTION
when OTHERS then
dbms_output.put_line('Error ROW_ID: '||x.row_id||sqlerrm);
END;
END LOOP;
COMMIT;
END;
/
exit;

3) Run in a BAT or SH like (my example was made for a bat, with “chunks” of 50 million rows – and commits by every 1k, defined on item 2):

@echo off
set /p db="Target Database.: "
set /p user="Username.......: "
set /p pass="Password..................: "
pause
START sqlplus %user%/%pass%@%db% @run_chunck.sql 1 2060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 2060054 52060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 52060054 102060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 102060054 152060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 152060054 202060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 202060054 252060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 252060054 302060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 302060054 352060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 352060054 402060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 402060054 452060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 452060054 502060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 502060054 552060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 552060054 602060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 602060054 652060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 652060054 702060053 -- count(*) from table

 

Watching the inserts running…

targetdb>@sess
User:MATHEUS
USERNAME EVENT SQL_ID
---------- ---------- -------------------------
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink gt3mq5ct7mt6r
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from dblink 6qc1hsnkkfhnw
MATHEUS_BOESING SQL*Net message from client
MATHEUS_BOESING SQL*Net message from client
MATHEUS_BOESING SQL*Net message to client c7a5tcc3a84k6

After a few (26 hours) the copy was successfully concluded.:)

Matheus.

ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted

When running external script by scheduler. The solution:

chown root $ORACLE_HOME/bin/extjob
chmod 4750 $ORACLE_HOME/bin/extjob

chown root $ORACLE_HOME/rdbms/admin/externaljob.ora
chmod 640 $ORACLE_HOME/rdbms/admin/externaljob.ora

chown root $ORACLE_HOME/bin/jssu
chmod 4750 $ORACLE_HOME/bin/jssu

 

Have a nice week!
Matheus.