Feed aggregator

Foreign key reference table

Tom Kyte - Thu, 2016-11-24 18:26
Hi, A table contains a primary key so it referes to another table,I know table name and its primary key column name but i dont know to which table it referes. So is it possible to know the reference table name and foreign key column name?
Categories: DBA Blogs

MV log segments growing to big and to fast

Tom Kyte - Thu, 2016-11-24 18:26
Hi, I have 2 11g DB connected over DB link. - DB1 has master table (MASTER_TBL) and - DB2 materialized view (MV) connected to master table (MASTER_TBL) with fast refresh. MASTER_TBL is: - updated constantly from different sources. - si...
Categories: DBA Blogs

Understanding 'Outline Data' in Oracle Explain Plan

Tom Kyte - Thu, 2016-11-24 18:26
Hi Tom, I am using DBMS_XPLAN.DISPLAY_CURSOR(format ==> 'Advanced') to generate the explain plan. This is generating for me 2 sections of information 'Query Block Name / Object Alias (identified by operation id):' and 'Outline Data'. Could...
Categories: DBA Blogs

INITRANS and LOB index

Tom Kyte - Thu, 2016-11-24 18:26
Hi, Is there a way to specify INITRANS for a LOB index (in my specific case on a VARRAY attribute of an object column - to be even more specific on SDO_ELEM_INFO_ARRAY and SDO_ORDINATES attributes of the column of the MDSYS.SDO_GEOMETRY). Oracle v...
Categories: DBA Blogs

Retrieving new records

Tom Kyte - Thu, 2016-11-24 18:26
Hi, I have a question on data warehousing and am looking for guidance how this can be best achieved using features of the database. Assuming we have a very large table in one instance and are looking to retrieve all the new records from it each eve...
Categories: DBA Blogs

Difference between Role & Privilage

Tom Kyte - Thu, 2016-11-24 18:26
Hi, Could you Explain the exact Difference between the ROLE and Privilage with an example,also explain how they affect the user granted with roles and privilages? Thank you.
Categories: DBA Blogs

Observing DML restarts caused by invalidations

Yann Neuhaus - Thu, 2016-11-24 14:53

Usually, cursor invalidations do not interrupt the current execution of the cursor. You can even drop a table while a query on it is running and the query may end successfully. However some invalidations will throw internal errors that are catch and the cursor execution restarts transparently. I’ve reproduced one here to show the symptoms: it’s a different EXEC_SQL_ID so the restarts are not accounted within the same SQL Monitoring.

I had that question because I encountered a SQL Monitoring report where the ‘executions’ count of the INSERT operation was more than one. I wondered if it can be a case of DML restart or if DML restart should be accounted in different EXEC_SQL_ID. I got the quick answer from a very reliable source:

@FranckPachot Yep!

— Stefan Koehler (@OracleSK) November 21, 2016

However for my better understanding, I’m now reproducing a case of DML restart to show the symptoms on V$SQL and SQL Monitor.

I need a table with some rows:

SQL> create table DEMO as select rownum n from xmltable('1 to 3');
Table created.

Initially my problem was with an insert into a GTT but now I’m reproducing the case with a partitioned table:

SQL> create table DEMOGTT (n number) partition by hash(n);
Table created.

For internal reasons, when a cursor is invalidated after it has acquired a lock on a partition, a DML restart occurs. If you have a doubt, “oerr ora 14403″ explains that.

The easiest way to invalidate while the cursor is running is to call a function that does it for each row. And as you will see that it can be restarted to infinity I set a timeout timestamp after 30 seconds.


SQL> column limit new_value limit
SQL> select to_char(sysdate+30/24/60/60,'yyyymmddhh24miss') limit from dual;
 
LIMIT
--------------
20161124212154
 
SQL> create function DEMOSLOW(n number) return number as
2 pragma autonomous_transaction;
3 begin
4 if sysdate > to_date('&limit','yyyymmddhh24miss')
5 then
6 dbms_stats.gather_table_stats(user,'DEMOGTT',no_invalidate=>false);
7 end if;
8 return n;
9 end;
10 /
old 4: if sysdate < to_date('&limit','yyyymmddhh24miss')
new 4: if sysdate < to_date('20161124212154','yyyymmddhh24miss')
 
Function created.

Time to run the insert, calling the function for each row:

SQL> alter session set statistics_level=all;
Session altered.
 
SQL> set timing on time on
21:21:24 SQL> insert /*+ monitor */ into DEMOGTT select n from DEMO where n=DEMOSLOW(n);
3 rows created.
 
Elapsed: 00:00:29.30
21:21:54 SQL>

This has taked 30 seconds. Without the timeout, it never stops.

Execution plan for last run shows only one ‘Start':

21:21:54 SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 3fk1hyq9vnuzx, child number 0
-------------------------------------
insert /*+ monitor */ into DEMOGTT select n from DEMO where
n=DEMOSLOW(n)
 
Plan hash value: 4000794843
 
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:00.01 | 138 |
| 1 | LOAD TABLE CONVENTIONAL | DEMOGTT | 1 | | 0 |00:00:00.01 | 138 |
|* 2 | TABLE ACCESS FULL | DEMO | 1 | 1 | 3 |00:00:00.01 | 9 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter("N"="DEMOSLOW"("N"))

This is a proof that DML restarts are not accounted within the same execution. the ‘last’ statistics are only from the last restart.

V$SQL accounts only one execution but thousands of invalidations:

21:21:54 SQL> select executions,invalidations from v$sql where sql_id='3fk1hyq9vnuzx';
 
EXECUTIONS INVALIDATIONS
---------- -------------
1 1571

ASH shows the different SQL_EXEC_ID:

21:21:54 SQL> select sample_time,sql_id,sql_exec_id from v$active_session_history where sql_id='3fk1hyq9vnuzx' and sample_time>sysdate-10/24/60/60 order by sample_time desc;
 
SAMPLE_TIME SQL_ID SQL_EXEC_ID
--------------------------------------------------------------------------- ------------- -----------
24-NOV-16 09.21.53.773 PM 3fk1hyq9vnuzx 16778774
24-NOV-16 09.21.51.773 PM 3fk1hyq9vnuzx
24-NOV-16 09.21.48.773 PM 3fk1hyq9vnuzx 16778501
24-NOV-16 09.21.46.773 PM 3fk1hyq9vnuzx 16778396
24-NOV-16 09.21.45.773 PM 3fk1hyq9vnuzx 16778341

and SQL Monitoring see different executions:

21:21:54 SQL> select sql_id,sql_exec_id,status,last_refresh_time,plan_operation,starts from v$sql_plan_monitor where sql_id='3fk1hyq9vnuzx' and plan_line_id=0 order by last_refresh_time desc fetch first 10 rows only;
 
SQL_ID SQL_EXEC_ID STATUS LAST_REFRESH_TI PLAN_OPERATION STARTS
------------- ----------- ------------------- --------------- ------------------------------ ----------
3fk1hyq9vnuzx 16778762 DONE 24-nov 21:21:53 INSERT STATEMENT 1
3fk1hyq9vnuzx 16778765 DONE 24-nov 21:21:53 INSERT STATEMENT 1
3fk1hyq9vnuzx 16778767 DONE 24-nov 21:21:53 INSERT STATEMENT 1
3fk1hyq9vnuzx 16778766 DONE 24-nov 21:21:53 INSERT STATEMENT 1
3fk1hyq9vnuzx 16778771 DONE 24-nov 21:21:53 INSERT STATEMENT 1
3fk1hyq9vnuzx 16778769 DONE 24-nov 21:21:53 INSERT STATEMENT 1
3fk1hyq9vnuzx 16778764 DONE 24-nov 21:21:53 INSERT STATEMENT 1
3fk1hyq9vnuzx 16778770 DONE 24-nov 21:21:53 INSERT STATEMENT 1
3fk1hyq9vnuzx 16778763 DONE 24-nov 21:21:53 INSERT STATEMENT 1
3fk1hyq9vnuzx 16778768 DONE 24-nov 21:21:53 INSERT STATEMENT 1

So those are the symptoms of DML restart. And my initial problem is still there:

  • This test case does not reproduce the issue on a GTT
  • My issue on the GTT had only one SQL_EXEC_ID with multiple ‘starts’ of the plan operations.

#sqlmon #puzzled
I expected to see '1' as 'executions for the first line of a plan,
Here I have executions with 1,12,19,64… ?!? pic.twitter.com/yHPZXDprgX

— Franck Pachot (@FranckPachot) November 21, 2016

Any comments greatly appreciated :)

 

Cet article Observing DML restarts caused by invalidations est apparu en premier sur Blog dbi services.

SQL Server on Linux in Azure

Yann Neuhaus - Thu, 2016-11-24 14:23

I’m pleased to announce that Microsoft gives now the opportunity to choose Linux as a new operating system for SQL Server.

To try this new version, you can find in the Cloud Azure a new template in the marketplace.

In the search bar, tape “SQL Server vnext” and you’ll find the “SQL Server vNext on Red Hat Enterprise Linux 7.2”.

SQL Server vNext 01 Follow all “classic” steps to create your new VM SQL Server on Linux:

SQL Server vNext 02

First step, choose your “deployment mode”

SQL Server vNext 04

After this first step, choose a name, a disk type, a couple login/password and the location

SQL Server vNext 05

The next step is to choose your storage:

SQL Server vNext 06

At the last step, configure you options:

SQL Server vNext 07

Wait a little bit for the provisioning and at the end, you’ll have your VM SQL Server on Linux ready to use:

SQL Server vNext 11

 

Et voila! My first VM SQL Server on Linux in the Cloud! It’s easy and fast to create…

Next blog is how to use it ;-)

 

Cet article SQL Server on Linux in Azure est apparu en premier sur Blog dbi services.

Rittman Mead at UKOUG Tech 16

Rittman Mead Consulting - Thu, 2016-11-24 11:52
Rittman Mead at UKOUG Tech 16

This year as always Rittman Mead is coming to UKOUG Tech 16 with a strong presence and a great line up of sessions covering OBIEE, ODI, Kafka, advanced visualisation and more. And yes, there will be Cloud!

Rittman Mead at UKOUG Tech 16

Here is the details of the Rittman Mead sessions :

There is no better way to finish a conference than with two success stories from our recent engagements! If the OBIEE 12c upgrade depicted in Francesco's session is something you are also looking to achieve, we would be pleased to tell you more about it and to see how we can help you.

And of course, we are also happy to answer any questions if you see us in sessions or around the conference. You can find some of us during the Oracle Big Data meetup (Monday evening) or the ODTUG Data and Analytics Switzerland meetup (Tuesday evening).

So see you in two weeks in Birmingham !

Categories: BI & Warehousing

Can I do it with PostgreSQL? – 1 – Restore points

Yann Neuhaus - Thu, 2016-11-24 11:03

When discussing with customers about PostgreSQL we often hear that they can do things in one database that they can not do in PostgreSQL. Most of the times this is not true and you actually can do it in PostgreSQL. Maybe not in exactly the same way but this is not surprising as PostgreSQL does implement features not in exactly the same way other vendors do.

To start this series we’ll talk about restore points. Of course you can create restore points in PostgreSQL and then restore up to such a point in case you need to (e.g. after a failed schema or application upgrade or just for testing purposes ). Lets go…

We’ll use the latest version of PostgreSQL which is 9.6.1 currently:

postgres@pgbox:/home/postgres/ [PG961] sqh
psql (9.6.1 dbi services build)
Type "help" for help.

(postgres@[local]:5439) [postgres] > select version();
                                                          version                                                           
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)

Time: 47.119 ms
(postgres@[local]:5439) [postgres] > 

When we want to do point in time recovery we need to setup archiving. Without going into the details (as this is out of scope here) the parameters which need to be adjusted are these (if not already done):

(postgres@[local]:5439) [postgres] > alter system set wal_level = 'replica';
ALTER SYSTEM
Time: 28.056 ms
(postgres@[local]:5439) [postgres] > alter system set archive_command='test ! -f /u90/pgdata/PG961/%f && cp %p /u90/pgdata/PG961/%f'; 
ALTER SYSTEM
Time: 20.925 ms
(postgres@[local]:5439) [postgres] > alter system set archive_mode ='on';
ALTER SYSTEM
Time: 5.307 ms
(postgres@[local]:5439) [postgres] > select name,context from pg_settings where name in ('archive_mode','archive_command','wal_level');
      name       |  context   
-----------------+------------
 archive_command | sighup
 archive_mode    | postmaster
 wal_level       | postmaster
(3 rows)

Time: 1.460 ms

Be sure to restart your instance before you continue. Changing archive_mode and wal_level can not be done online. Once you restarted make sure that your archive_command really succeeds:

(postgres@[local]:5439) [postgres] > create database test1;
CREATE DATABASE
Time: 1705.539 ms
(postgres@[local]:5439) [postgres] > drop database test1;
DROP DATABASE
Time: 107.283 ms
(postgres@[local]:5439) [restore] > select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/22001798
(1 row)

Time: 214.216 ms
(postgres@[local]:5439) [postgres] > \! ls -l /u90/pgdata/PG961/
total 16384
-rw-------. 1 postgres postgres 16777216 Nov 24 17:34 000000020000000000000022

When you can not see an archived wal in the last step you did something wrong. The next bit you need when you want to do point in time recovery with PostgreSQL is a base backup:

postgres@pgbox:/u02/pgdata/PG961/ [PG961] mkdir /u90/pgdata/PG961/basebackups
postgres@pgbox:/u02/pgdata/PG961/ [PG961] pg_basebackup -x -D /u90/pgdata/PG961/basebackups/
postgres@pgbox:/u02/pgdata/PG961/ [PG961] ls /u90/pgdata/PG961/basebackups/
backup_label  pg_commit_ts   pg_log        pg_replslot   pg_stat_tmp  PG_VERSION
base          pg_dynshmem    pg_logical    pg_serial     pg_subtrans  pg_xlog
global        pg_hba.conf    pg_multixact  pg_snapshots  pg_tblspc    postgresql.auto.conf
pg_clog       pg_ident.conf  pg_notify     pg_stat       pg_twophase  postgresql.conf

Fine. Lets generate some test data with this simple script:

(postgres@[local]:5439) [postgres] > \! cat a.sql
\c postgres
drop database if exists restore;
create database restore;
\c restore
create table t1 ( a int );
insert into t1 (a)
       values (generate_series(1,1000000));
select count(*) from t1;
\d t1

When you run this you’ll get a table (t1) containing 1 million rows:

(postgres@[local]:5439) [postgres] > \i a.sql
You are now connected to database "postgres" as user "postgres".
DROP DATABASE
Time: 114.000 ms
CREATE DATABASE
Time: 1033.245 ms
You are now connected to database "restore" as user "postgres".
CREATE TABLE
Time: 5.917 ms
INSERT 0 1000000
Time: 2226.599 ms
  count  
---------
 1000000
(1 row)

Time: 65.864 ms
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

Ok, fine. Now we are ready for testing restore points. Lets say you want to do some modifications to your table and to be on the safe side you want to create a restore point before. No problem:

(postgres@[local]:5439) [postgres] > select pg_create_restore_point('RP1');
 pg_create_restore_point 
-------------------------
 0/28D50EF8
(1 row)

Time: 0.825 ms

Quite easy and fast. Now lets play with our table:

(postgres@[local]:5439) [restore] > select count(*) from t1;
  count  
---------
 1000010
(1 row)

Time: 66.214 ms
(postgres@[local]:5439) [restore] > \d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

(postgres@[local]:5439) [restore] > alter table t1 add column b varchar(10);
ALTER TABLE
Time: 1.810 ms
(postgres@[local]:5439) [restore] > update t1 set b='b';
UPDATE 1000010
Time: 11004.972 ms
(postgres@[local]:5439) [restore] > drop table t1;
DROP TABLE
Time: 238.329 ms

Ups, table gone. How can we now go back to the restore point created above? Quite easy:

Shutdown your instance and copy back the base backup:

postgres@pgbox:/u02/pgdata/PG961/ [PG961] rm -rf pg_xlog
postgres@pgbox:/u02/pgdata/PG961/ [PG961] cp -pr /u90/pgdata/PG961/basebackups/* $PGDATA
cp: cannot overwrite non-directory ‘/u02/pgdata/PG961/pg_xlog’ with directory ‘/u90/pgdata/PG961/basebackups/pg_xlog’
postgres@pgbox:/u02/pgdata/PG961/ [PG961] ln -s /u03/pgdata/PG961/ pg_xlog

Then create a recovery.conf file (for telling PostgreSQL to go into recovery mode when it comes up) and specify the restore point you created above:

postgres@pgbox:/home/postgres/ [PG961] echo "restore_command = 'cp /u90/pgdata/PG961/%f %p'
> recovery_target_name = 'RP1'" > $PGDATA/recovery.conf
postgres@pgbox:/home/postgres/ [PG961] cat $PGDATA/recovery.conf
restore_command = 'cp /u90/pgdata/PG961/%f %p'
recovery_target_name = 'RP1'

Start the instance and check the log file:

LOG:  database system was interrupted; last known up at 2016-11-24 17:36:28 CET
LOG:  creating missing WAL directory "pg_xlog/archive_status"
LOG:  starting point-in-time recovery to "RP1"

If everything went fine your table should be back without the additional column:

(postgres@[local]:5439) [restore] > \d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

(postgres@[local]:5439) [restore] > select count(*) from t1;
  count  
---------
 1000000
(1 row)

Time: 82.797 ms

So, yes, you can definitely use restore points with PostgreSQL :)

If you want me to blog about any feature you are not sure is there in PostgreSQL let me know.

 

Cet article Can I do it with PostgreSQL? – 1 – Restore points est apparu en premier sur Blog dbi services.

Oracle VARIANCE Function with Examples

Complete IT Professional - Thu, 2016-11-24 05:00
In this article, I’ll explain what the Oracle VARIANCE function is, and show you some examples. Purpose of the Oracle VARIANCE Function The purpose of the VARIANCE function is to, well, return the variance of a set of numbers. A variance is a mathematical concept, which represents how different the values are in a set […]
Categories: Development

ORAAH 2.7 released!

The latest release of Oracle R Advanced Analytics for Hadoop (ORAAH), release 2.7.0, is one of the components of the Oracle Big Data Connectors software suite, an option to the Oracle Big Data...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Documentum story – Restrict the number of jpeg renditions

Yann Neuhaus - Thu, 2016-11-24 03:00

As already explained in this blog, we had to remove the PDF renditions for a customer to only keep the jpeg renditions on the ADTS side because the PDF was generated by another third-party tool. If you take a look at the dmr_content items attached to a specific document (parent_id), you will understand that there is by default only one PDF content while there might be a lot more jpeg renditions. Why? The answer to that is pretty simple, the content of the PDF rendition contains all the pages of the document while the jpeg renditions are only for a single page… Therefore if an ADTS is processing a document of 25 pages, then it will create 25 jpeg renditions. Actually that’s even more than that… Indeed by default the ADTS generates two types/formats of jpeg renditions for each page which double the total number of renditions. Here are the two formats available by default:

  • jpeg_lres: (Low Resolutions) that’s actually a real size preview in low resolution of the page
  • jpeg_story: (StoryBoards) that’s a reduced size preview of the page, quite hard to read what’s written…

 

jpeg_lres is the format used by the Thumbnail Server and also by D2 for the preview widget. On the other hand, jpeg_story isn’t used at all in D2 4.5. According to EMC, it *might* has been used for D2 4.1 and previous versions but they aren’t sure about it… ;)

 

Now that this has been said, let’s go back to the title of this blog. Because of this behavior of the ADTS, it might happen that one day you will see hundreds or even thousands of dmr_content items deleted. You might think that there is something wrong, that the cleanup jobs deleted too many objects or something like that… So if this happens to you, please take a look at the format of these dmr_content! Several months ago, the cleanup jobs were inactive for a few weeks because of a bug and when we reactivated them, this happened to us and we finally found out that 95% of these items where only jpeg renditions and that this was actually the expected behavior!

 

After that, we started thinking about how we should handle these jpeg renditions for really big documents? Because having the preview of the documents available in D2 is great but then is it really needed? Generating a preview of the first page or of the 10 first pages might makes sense but would it makes sense to generate a preview for each page of a document bigger than 10 pages? 100 pages? 1 000 pages? These previews are used in D2 and you need to move from one page to the other one starting with the page 1. If you absolutely want to see the preview of the page *364* in D2, then you will need 20 minutes to reach that page in the first place… I think that downloading the document is a little bit faster ;).

 

So is it possible to only generate previews for a few pages and setup a maximum number of jpeg renditions per document? The short answer to that is: yes! And that’s the purpose of this blog, wonderful, isn’t it?!

 

First check out the configuration file that will need to be updated:

[dmadmin@content_server_01 workspace]$ iapi DOCBASE -Udmadmin -Pxxx


        EMC Documentum iapi - Interactive API interface
        (c) Copyright EMC Corp., 1992 - 2015
        All rights reserved.
        Client Library Release 7.2.0050.0084


Connecting to Server using docbase DOCBASE
[DM_SESSION_I_SESSION_START]info:  "Session 013f245a802173d6 started for user dmadmin."


Connected to Documentum Server running Release 7.2.0050.0214  Linux64.Oracle
Session id is s0
API> retrieve,c,dm_document where folder('/System/Media Server/Command Line Files') and object_name = 'storyboard_pdfstoryboard.xml'
...
093f245a801c9075
API> checkout,c,l
...
093f245a801c9075
API> getfile,c,l,/tmp/workspace/storyboard_pdfstoryboard.xml
...
/tmp/workspace/storyboard_pdfstoryboard.xml
API> flushcache,c
...
OK

 

Then retrieve a test document that will be used to see how it is working:

API> retrieve,c,dm_document where r_object_id='093f245a801a8f56'
...
093f245a801a8f56
API> getfile,c,l,/tmp/workspace/TestRenditions.docx
...
/tmp/workspace/TestRenditions.docx

 

Ok so now the two files are stored locally. I retrieved the content of the file TestRenditions.docx in order to be able to regenerate the renditions, you will see how it works later. So let’s check how many renditions this document currently has:

API> ?,c,select r_object_id, full_format, parent_id, content_size, full_content_size, set_time, set_file from dmr_content where any parent_id='093f245a801a8f56'
r_object_id       full_format         parent_id         content_size  full_content_size       set_time                   set_file                                                                              
----------------  ------------------  ----------------  ------------  ----------------------  -------------------------  ---------------------------------------------------------------------------------------
063f245a801d95cc  jpeg_lres           093f245a801a8f56         60467                   60467  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile353776780929575961.tar
063f245a801d95cd  jpeg_lres           093f245a801a8f56        138862                  138862  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile353776780929575961.tar
063f245a801d95ce  jpeg_lres           093f245a801a8f56         29596                   29596  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile353776780929575961.tar
.....
063f245a8024b99e  jpeg_story          093f245a801a8f56          3392                    3392  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile7193259325098763580.tar
063f245a8024b99f  jpeg_story          093f245a801a8f56          4718                    4718  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile7193259325098763580.tar
063f245a8024b9a0  jpeg_story          093f245a801a8f56          1567                    1567  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile7193259325098763580.tar
.....
063f245a8024b622  msw12               093f245a801a8f56         90535                   90535  7/4/2016 13:22:30          /app/weblogic/tmp/DOCBASE/msD2-01/DefaultFileRenamePolicy.rename8572010254156028259.docx
(52 rows affected)

API> exit
Bye

 

As explained previously in this blog, the document “TestRenditions.docx” (093f245a801a8f56) has 25 pages and therefore there are 25 (jpeg_lres) + 25 (jpeg_story) + 1 (pdf) + 1 (real document) = 52 dmr_content items attached to it and therefore 51 renditions. Now let’s see the content of the configuration file and what to do to change the number of renditions we need:

[dmadmin@content_server_01 workspace]$ pwd
/tmp/workspace
[dmadmin@content_server_01 workspace]$ cat storyboard_pdfstoryboard.xml
<PDFSTORYBOARD_MP_PROPERTIES>
    <FORMAT name="JPEG">
        <PROP name="Format" type="string">JPEG</PROP>
        <PROP name="Width" type="unsigned long" token="doc_token_width">200</PROP>
        <PROP name="Height" type="unsigned long" token="doc_token_height">200</PROP>
        <PROP name="Dpi" type="unsigned long" token="doc_token_dpi">72</PROP>
        <PROP name="KeepRatio" type="boolean">true</PROP>
        <PROP name="Password" type="string">your_password_be_here</PROP>
        <PROP name="Max Pages" type="unsigned long" token="doc_token_maxPages">-1</PROP>
        <PROP name="Frames Requested" type="unsigned long" token="doc_token_frames_requested">-1</PROP>
    </FORMAT>
</PDFSTORYBOARD_MP_PROPERTIES>
[dmadmin@content_server_01 workspace]$ 
[dmadmin@content_server_01 workspace]$ sed -i 's/doc_token_maxPages">-1</doc_token_maxPages">1</' storyboard_pdfstoryboard.xml
[dmadmin@content_server_01 workspace]$ 
[dmadmin@content_server_01 workspace]$ cat storyboard_pdfstoryboard.xml
<PDFSTORYBOARD_MP_PROPERTIES>
    <FORMAT name="JPEG">
        <PROP name="Format" type="string">JPEG</PROP>
        <PROP name="Width" type="unsigned long" token="doc_token_width">200</PROP>
        <PROP name="Height" type="unsigned long" token="doc_token_height">200</PROP>
        <PROP name="Dpi" type="unsigned long" token="doc_token_dpi">72</PROP>
        <PROP name="KeepRatio" type="boolean">true</PROP>
        <PROP name="Password" type="string">your_password_be_here</PROP>
        <PROP name="Max Pages" type="unsigned long" token="doc_token_maxPages">1</PROP>
        <PROP name="Frames Requested" type="unsigned long" token="doc_token_frames_requested">-1</PROP>
    </FORMAT>
</PDFSTORYBOARD_MP_PROPERTIES>

 

As you can see above, I just changed the value assigned to the “doc_token_maxPages” from -1 (unlimited) to 1 (1 page) and that should be it! To apply this change, we need to check in the storyboard_pdfstoryboard.xml file:

[dmadmin@content_server_01 workspace]$ iapi DOCBASE -Udmadmin -Pxxx


        EMC Documentum iapi - Interactive API interface
        (c) Copyright EMC Corp., 1992 - 2015
        All rights reserved.
        Client Library Release 7.2.0050.0084


Connecting to Server using docbase DOCBASE
[DM_SESSION_I_SESSION_START]info:  "Session 013f245a80217406 started for user dmadmin."


Connected to Documentum Server running Release 7.2.0050.0214  Linux64.Oracle
Session id is s0
API> retrieve,c,dm_document where folder('/System/Media Server/Command Line Files') and object_name = 'storyboard_pdfstoryboard.xml'
...
093f245a801c9075
API> setfile,c,l,/tmp/workspace/storyboard_pdfstoryboard.xml
...
OK
API> checkin,c,l
...
093f245a8027254e
API> flushcache,c
...
OK

 

Once this is done, we can remove all current renditions of this document (we saw above pdf, jpeg_lres and jpeg_story renditions) to only let the docx/msw12 file:

API> retrieve,c,dm_document where r_object_id='093f245a801a8f56'
...
093f245a801a8f56
API> removerendition,c,l,pdf
...
OK
API> save,c,l
...
OK
API> flushcache,c
...
OK
API> removerendition,c,l,jpeg_lres
...
OK
API> save,c,l
...
OK
API> flushcache,c
...
OK
API> removerendition,c,l,jpeg_story
...
OK
API> save,c,l
...
OK
API> flushcache,c
...
OK
API> ?,c,select r_object_id, full_format, parent_id, content_size, full_content_size, set_time, set_file from dmr_content where any parent_id='093f245a801a8f56'
r_object_id       full_format         parent_id         content_size  full_content_size       set_time                   set_file                                                                              
----------------  ------------------  ----------------  ------------  ----------------------  -------------------------  ---------------------------------------------------------------------------------------
063f245a8024b622  msw12               093f245a801a8f56         90535                   90535  7/4/2016 13:22:30          /app/weblogic/tmp/DOMAIN/msD2-01/DefaultFileRenamePolicy.rename8572010254156028259.docx
(1 row affected)

 

The last step is to request the recreation of these renditions (using a setfile), wait 20 seconds or so and then check how much renditions have been recreated:

API> retrieve,c,dm_document where r_object_id='093f245a801a8f56'
...
093f245a801a8f56
API> setfile,c,l,/tmp/workspace/TestRenditions.docx
...
OK
API> save,c,l
...
OK
API> ?,c,select r_object_id, full_format, parent_id, content_size, full_content_size, set_time, set_file from dmr_content where any parent_id='093f245a801a8f56';
r_object_id       full_format         parent_id         content_size  full_content_size       set_time                   set_file                                                                              
----------------  ------------------  ----------------  ------------  ----------------------  -------------------------  ---------------------------------------------------------------------------------------
063f245a8024b622  msw12               093f245a801a8f56         90535                   90535  11/13/2016 12:49:48        /tmp/workspace/TestRenditions.docx
063f245a8024b957  jpeg_lres           093f245a801a8f56         60467                   60467  11/13/2016 12:49:56        C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile1116397023537525059.tar
063f245a8024b958  jpeg_story          093f245a801a8f56          3392                    3392  11/13/2016 12:49:57        C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile6054850753334521340.tar
(3 rows affected)

 

As you can see, there is now only one jpeg rendition per format and that’s for the first page only so that’s a success! If you want to keep only X jpeg renditions per document, now you know how to do it :)

 

Cet article Documentum story – Restrict the number of jpeg renditions est apparu en premier sur Blog dbi services.

UKOUG Conference 2016 coming up fast

Andrew Clarke - Thu, 2016-11-24 02:19
The weather has turned cold, the lights are twinkling in windows and Starbucks is selling pumpkin lattes. Yes, it's starting to look a lot like Christmas. But first there's the wonder-filled advent calendar that is the UKOUG Annual Conference in Birmingham, UK. So many doors to choose from!

The Conference is the premier event for Oracle users in the UK (and beyond). This year has another cracker of an agenda: check it out.

The session I'm anticipating most is Monday's double header with Bryn Llewellyn and Toon Koopelaar's A Real-World Comparison of the NoPLSQL & Thick Database Paradigms. Will they come down on the side of implementing business logic in stored procedures or won't they? It'll be tense :) But it will definitely be insightful and elegantly argued.

Oracle's bailiwick has expanded vastly over the years, and it's become increasingly hard to cover everything. Even so, it's fair to say in recent years older technologies such as Forms have been neglected in favour in favour of shinier baubles. Not this year. There's a good representation of Forms sessions this year, including a talk from Michael Ferrante, the Forms Product Manager. These sessions are all scheduled for the Wednesday, in a day targeted at database developers. If you're an Old Skool developer, especially if you're a Forms developer, and your boss will allow you only one day at the conference, then Wednesday is the day to pick.

Hope to see you there

Data Alignment

Tom Kyte - Thu, 2016-11-24 00:06
Hello, I'm having problems with my data not being aligned and neatly displayed when I perform a SELECT statement. The data is skewed and not aligned with the columns. I've tried statements with the COLUMN and HEADING commands but with no success....
Categories: DBA Blogs

Decryption Issue - Need Help

Tom Kyte - Thu, 2016-11-24 00:06
I have a requirement to decrypt a password coming in source extract file in our project. Currently the same thing is happening through a SQL server stored procedure. But the SQL server will be decommissioned in near future and the whole thing needs t...
Categories: DBA Blogs

Replacing text with extended ascii characters

Tom Kyte - Thu, 2016-11-24 00:06
Hi Tom, I have a column which contains text like [DEG],[MICRO],[PHASE]. I want to replace it with their symbols in oracle. My current version of oracle in 11g. Below link contains the complete list of replacements. http://i.imgur.com/bl7xvZJ.png ...
Categories: DBA Blogs

to_char number format

Tom Kyte - Thu, 2016-11-24 00:06
hi, I am printing a number using below to_char format : declare var number(6,2) begin var := 65.2 dbms_output.put_line(to_char(var,'9999.99')); end; the above code prints ' 65.20' (3 spaces and the number). one extra space is printed...
Categories: DBA Blogs

oracle 12c capacity planning

Tom Kyte - Thu, 2016-11-24 00:06
Hello Tom & Team, Can you please shed some light on capacity planning h/w + Oracle database configuration for Oracle 12c - Multitenant Option being used. We are planning to consolidate/upgrade 10 databases using Multitenant architecture. My thou...
Categories: DBA Blogs

VirtualBox OEL 7.3 Minimal Install for 12c

Michael Dinh - Wed, 2016-11-23 21:47

Finally, I was able to configure OEL 7.3 Minimal Install.

OEL 7.3 was a challenge since NAT and Host Only Networking from VirtualBox was not working.

I broke down and ended up using Bridged Adapter.

Next, blogs out there were using Server with GUI installation which is a really bloated option.

Even tried CentOS 7.0 Gnome and did not like having to install UEK or rebuild RPM.

Did it work?

VirtualBox OEL 7.3 Minimal Install for 12c

Silent Install CRS_SWONLY 12c using OEL 7.3 Minimal Install

Will you try it and let me know?

Happy Thanksgivings!

 


Pages

Subscribe to Oracle FAQ aggregator