Category Archives: Tutorial

Delete old S_NQ_ACCT/S_NQ_DB_ACCT partitions

In the previous post I’ve described how to partition the S_NQ_ACCT and S_NQ_DB_ACCT tables used by OBIEE for Usage Tracking. Now I’m gonna describe how to delete partitions that are X days old.

When we partitioned the table, we configured it to be done daily. Now if we are going to issue the following command:

conn DEV_BIPLATFORM;
SELECT * FROM user_tab_partitions;

we are gonna notice that Oracle Database created the HIGH_VALUE as “TO_DATE(‘ 2013-11-09 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)”. If you want to delete all partitions that are 32 days old, I’ve used the following sql script:

SET serveroutput ON
SET feedback off
variable v_days_to_keep NUMBER;
exec :v_days_to_keep := 32;
 
DECLARE
    v_statement VARCHAR2(4000);
BEGIN
    FOR x IN (SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM user_tab_partitions WHERE TABLE_NAME = 'S_NQ_DB_ACCT') LOOP
      IF x.PARTITION_NAME != 'PART1'
        THEN
          v_statement := '
BEGIN
    IF SYSDATE - ' || x.HIGH_VALUE || ' > '|| :v_days_to_keep || '
      THEN
        EXECUTE IMMEDIATE ''ALTER TABLE "' || x.TABLE_NAME || '" DROP PARTITION "' || x.PARTITION_NAME || '" UPDATE GLOBAL INDEXES'';
    END IF;
END;';
          EXECUTE IMMEDIATE v_statement;
      END IF;
    END LOOP;
    EXECUTE IMMEDIATE 'alter table S_NQ_DB_ACCT disable CONSTRAINT "FK_S_NQ_DB_ACCT"';
    FOR x IN (SELECT TABLE_NAME,PARTITION_NAME,HIGH_VALUE FROM user_tab_partitions WHERE TABLE_NAME = 'S_NQ_ACCT') LOOP
      IF x.PARTITION_NAME != 'PART1'
        THEN
          v_statement := '
BEGIN
    IF SYSDATE - ' || x.HIGH_VALUE || ' > '|| :v_days_to_keep || '
      THEN
        EXECUTE IMMEDIATE ''ALTER TABLE "' || x.TABLE_NAME || '" DROP PARTITION "' || x.PARTITION_NAME || '" UPDATE GLOBAL INDEXES'';
    END IF;
END;';
          EXECUTE IMMEDIATE v_statement;
      END IF;
    END LOOP;
    EXECUTE IMMEDIATE 'alter table S_NQ_DB_ACCT enable novalidate CONSTRAINT "FK_S_NQ_DB_ACCT"';
END;
/

I’ve tested this and it’s working perfectly on OBIEE 11.1.1.7.1 with DB 11.2.0.3. All you have to do now is set a job in the DB or sh script in the OS to run daily. I’ve went with the second option, because it’s simpler for me to receive email alerts if anything goes wrong with the script. I’ve also added some output to the script so I know when something is deleted. The sh script is below. Please change the password and tns_entry accordingly to you environment.

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin/:${PATH}
export TNS_ADMIN=$ORACLE_HOME/network/admin
 
before=`sqlplus -s DEV_BIPLATFORM/pass@tnsentry << EOFSQL
set heading off
set pages 0
select count(1) from user_tab_partitions;
exit;
EOFSQL`
 
sqlplus -s DEV_BIPLATFORM/pass@tnsentry << EOFSQL
Set serveroutput on
set feedback off
variable v_days_to_keep number;
exec :v_days_to_keep := 32;
 
declare
    v_statement varchar2(600);
begin
    for x in (select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME = 'S_NQ_DB_ACCT') loop
      if x.PARTITION_NAME != 'PART1'
        then
          v_statement := '
BEGIN
    IF SYSDATE - ' || x.HIGH_VALUE || ' > '|| :v_days_to_keep || '
      THEN
        EXECUTE IMMEDIATE ''ALTER TABLE "' || x.TABLE_NAME || '" DROP PARTITION "' || x.PARTITION_NAME || '" UPDATE GLOBAL INDEXES'';
    END IF;
END;';
          execute immediate v_statement;
      end if;
    end loop;
    EXECUTE IMMEDIATE 'alter table S_NQ_DB_ACCT disable CONSTRAINT "FK_S_NQ_DB_ACCT"';
    for x in (select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME = 'S_NQ_ACCT') loop
      if x.PARTITION_NAME != 'PART1'
        then
          v_statement := '
BEGIN
    IF SYSDATE - ' || x.HIGH_VALUE || ' > '|| :v_days_to_keep || '
      THEN
        EXECUTE IMMEDIATE ''ALTER TABLE "' || x.TABLE_NAME || '" DROP PARTITION "' || x.PARTITION_NAME || '" UPDATE GLOBAL INDEXES'';
    END IF;
END;';
          execute immediate v_statement;
      end if;
    end loop;
    EXECUTE IMMEDIATE 'alter table S_NQ_DB_ACCT enable novalidate CONSTRAINT "FK_S_NQ_DB_ACCT"';
end;
/
exit;
EOFSQL
 
after=`sqlplus -s DEV_BIPLATFORM/pass@tns_entry << EOFSQL
set heading off
set pages 0
select count(1) from user_tab_partitions;
exit;
EOFSQL`
 
deleted=`expr $before - $after`
[[ ! $deleted -eq 0 ]] && echo "$deleted paritions deleted."

Moving/renaming datafile in Oracle database

In Oracle Database 12c moving/renaming a datafile can be done online using the following command:

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb1/system01.dbf' TO '/u02/oradata/pdb1/system01.dbf';

In older version of Oracle Database this can be done by placing the tablespace offline if the datafile is not a system datafile. Here is the sequence of commands you need to issue, tested on Oracle Database 11gR2:

select * from dba_data_files;
-- get the tablespace name and file name that you want to move
alter tablespace tbs offline;
-- move the datafile in the OS
-- mv /u01/app/oracle/oradata/orcl/test.dbf /u02/oradata/orcl/test.dbf
alter tablespace tbs rename datafile '/u01/app/oracle/oradata/orcl/test.dbf' to '/u02/oradata/orcl/test.dbf';
alter tablespace tbs online;

And that’s it. There is no need to shutdown the whole database if you need to move/rename a non system datafile.

Note: keep in mind that if you move the datafile from one partition to another, while the database is is running, the space used by the old datafile will not be released until the database is restarted because the file will appear as opened file by lsof command.

Automatically start pluggable databases on container start

With Oracle Database 12c the new feature regarding pluggable databases was introduced. But when you start the database the usual way (sqlplus / as sysdba and then startup) this only starts the container database. You will have to manually start the pluggable databases using:

alter pluggable database [PDB_NAME] open;

If you want all or a specific pluggable database to be started when the container database starts, you will have to create a trigger on the container database like the one below:

create or replace trigger SYS.AFTER_STARTUP after startup on database 
begin 
  execute immediate 'alter pluggable database all open'; 
end; 
/

Configure RestFull Services on Apex Listener

I had to configure Apex Listener to work with RestFull Services. After a lot of hours of debugging I managed to make my Apex Listener on Weblogic to respond to the services.

A think that helped me a lot in all this debugging was the Apex Standalone server (if your environement is not in production or dev you can modify that as well) started in the debug mode. Basically you have to change the defaults.xml file in the Apex Listnere configdir and enable the on screen display:

<entry key="debug.printDebugToScreen">true</entry>

The steps to make it all work are the following:

1. Change directory to your apex installation and connect to the database and run apex_rest_config.sql and set the password for the APEX_LISTENER and APEX_REST_PUBLIC_USER users.
Note: If you do this step after step 2, be sure to unlock the two users because they will get locked when Apex Listener will try to connect with the wrong password.

2. Deploy Apex to Weblogic according to the official documentation here: http://docs.oracle.com/cd/E37099_01/doc/doc.20/e25066/install.htm#autoId17
Note: Be sure to enter the correct users and passwords.

3. In our case, there were a lot of Workspaces created before installing and enabling RestFull Services. I think that for all the Workspaces that are created before enabling RestFull Services or created on the same Apex Instance but from another deployment that does not have RestFull Services (like Apache) the url-mappings are not correct. If you try to test the service on a old Workspace you will get the following error:

Request Path passes syntax validation
Mapping request to database pool: PoolMap [_poolName=apex, _regex=null, _workspaceIdentifier=null, _failed=false, _lastUpdate=-1, _template=null, _type=REGEX]
Applied database connection info
Attempting to process with PL/SQL Gateway
Not processed as PL/SQL Gateway request
Attempting to process as a RESTful Service
Determining if request can be dispatched as a Tenanted RESTful Service
Request path has one path segment, continuing processing
No Tenant Principal established yet, continuing processing
APEX_LISTENER pool exists, continuing processing
No matching tenant found for: travel, cannot dispatch
No candidate found for: GET travel/hr/empinfo/ in context: http://localhost:8080/apex/

In the above example travel is the Workspace.

For the restfull services to work on old workspaces you will have to create the mappings manually according to the documetnation: http://docs.oracle.com/cd/E37099_01/doc/doc.20/e25066/config.htm#autoId3. In our example you should run:

java -jar apex.war map-url --type base-path --workspace-id travel /travel apex

And that should fix the issue.

How to auto sort in excel

Today my dad asked me a simple question: “How do you auto sort an excel table?”
At first I thought, “Don’t you just use the sort button on the selected table?”, but then I realized that it would be so cool if you could make a it AUTO sort. If you change some values in a table, so it will auto sort. With the help of your “friend” Google, i found out that you could do a Visual Basic script that auto sorts, but I couldn’t understand if it automatically sorts the table? Anyway, I found a simpler way to do it. But first, there are some thinks you have to consider:
1. Is your table vertical or horizontal?
Both are possible, but you have to use another excel function.
2. How complicated is your table?
If it’s a simple table, then you’re good. If you have a complicated table, you will have to change it so you can use this trick.

The  table I will use is a simple monthly expenses report. I inserted snapshots with the values and also the formulas used (you can see ALL the formulas used in an excel using the ” Ctrl+` ” key combination). The tables looks like this:

OK! Now that we have the table, we are good to go. I will tell you the steps and at the end the snapshots with the final table and formulas. The steps are as follows:

1. As you can see I left a the firs row empty. That row will be used to set the rank (1,2,3,4..) for the “Total values”. So in the cell B1 we use the formula =RANK(B10;$B$10:$G$10;0). The last argument dictates the sorting order: 0 for descending, 1 for ascending.
2. We fill the cells C1 thru G1 with the same formula. We used $ in the B1 formula to make it not change when filling the cells to the right. Now we have the ranks of the Ratios.
3.  Now we have to make the sort array. We can make it vertically or horizontally. I will use vertically, cause I thinks it’s more easier to read. You can make it horizontally, but you have to change the function we will later use (VLOOKUP instead of HLOOKUP). So we insert 1 thru 6, every value one in a cell, one under the other. I will use cells J2 – J7.
4. In K2 we insert the following formula =HLOOKUP(J2;$B$1:$G$10;2;FALSE). J2 is the value to search in the array B1:G10. 2 is the row in the array from where hlookup will return the corresponding value. False is used to find the exact match of the value used to search.  We fill the cells K3 to K7.
5. In L2 we insert =HLOOKUP(J2;$B$1:$G$10;10;FALSE) and fill the cells L3 to L7.

Now we have another table in which there is the sorted expenses categories and the corresponding percentages. Finally our excel should look something like this:

Hope this little tutorial helped you in any way.