DBA Blogs

Oracle Forms V Oracle APEX Check List

Tom Kyte - 17 hours 27 min ago
Oracle Forms has some strength and is still the best BackOffice tool from ORACLE from my Point of view. Here are some issues that I miss with APEX. Maybe you already have these Options in 18.x.? Can you check this list: 1. 100% accessiblity for...
Categories: DBA Blogs

Display blob pdf

Tom Kyte - 17 hours 27 min ago
Dear, I have table lob_table( id number ,doc blob ,namefile varchar (200)) I would like to display blob doc who is pdf file and print it in sqldeveloper. i have create this procedure is it corrects ? CREATE OR REPLACE PROCEDURE PROC2 AS...
Categories: DBA Blogs

What privilege to view package body

Tom Kyte - Mon, 2018-09-17 08:46
Hi Tom: I have a problem when i grant the package privilege to the other user. A is a normal user which used in factory environment. user B is for app team which can not create anything. First I grant create any procedure ,execute any procedure...
Categories: DBA Blogs

Hardware resource planning

Tom Kyte - Mon, 2018-09-17 08:46
Hello, Thanks for taking up this question. I am interested in understanding how to optimize the hardware resources (cores, memory, disk space) required for Oracle without impacting performance. There are multiple virtual machines in a VMwa...
Categories: DBA Blogs

system user could login without password or incorrect password

Tom Kyte - Mon, 2018-09-17 08:46
hi all, recently i had an incident.. i just logged into the database as system using sqlplus when sqlplus prompted for username i put 'SYS AS SYSDBA' and when prompted for password,instead of entering my password i just hit the ENTER key and s...
Categories: DBA Blogs

Returning count of rows deleted using execute immediate

Tom Kyte - Sun, 2018-09-16 14:46
How to I get the number of rows deleted within PL/SQL using the EXECUTE IMMEDIATE command?
Categories: DBA Blogs

Partitioning -- 5 : List Partitioning

Hemant K Chitale - Sun, 2018-09-16 10:14
List Partitioning allows you to specify a value (or a set of values) for the Partition Key to map to each Partition.

This example shows List Partitioning.

SQL> create table request_queue
2 (request_id number primary key,
3 request_submision_time timestamp,
4 requestor number,
5 request_arg_1 varchar2(255),
6 request_arg_2 varchar2(255),
7 request_arg_3 varchar2(255),
8 request_status varchar2(10),
9 request_completion_time timestamp)
10 partition by list (request_status)
11 (partition p_submitted values ('SUBMITTED'),
12 partition p_running values ('RUNNING'),
13 partition p_errored values ('ERRORED'),
14 partition p_completed values ('COMPLETED'),
15 partition p_miscell values ('RECHECK','FLAGGED','UNKNOWN'),
16 partition p_default values (DEFAULT)
17 )
18 /

Table created.

SQL>


Note how the P_MISCELL Partition can host multiple values for the REQUEST_STATUS column.
The last Partition, has is specified as a DEFAULT Partition (note that DEFAULT is a keyword, not a value like the others) to hold rows for REQUEST_STATUS for values not mapped to any of the other Partitions.  With List Partitioning, you should always have a DEFAULT Partition (it can have any name, e.g. P_UNKNOWN) so that unmapped rows can be captured.

If you go back to my previous post on Row Movement, you should realise the danger of capturing changing values (e.g. from "SUBMITTED" to "RUNNING" to "COMPLETED") in different Partitions.  What is the impact of updating a Request from the "SUBMITTED" status to the "RUNNING" status and then to the "COMPLETED" status ?  It is not simply an update of the REQUEST_STATUS column alone but a physical reinsertion of the entire row (with the consequent update to all indexes) at each change of status.

SQL> insert into request_queue
2 values (request_id_seq.nextval,systimestamp,101,
3 'FAC1','NOTE',null,'SUBMITTED',null)
4 /

1 row created.

SQL>
SQL> commit;

Commit complete.

.... sometime later ....

SQL> update request_queue
2 set request_status = 'RUNNING'
3 where request_id=1001
4 /
update request_queue
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL>


So, although now we know that we must ENABLE ROW MOVEMENT, we must suffer the impact of the physical reinsertion of the entire row into a new Partition.

SQL> alter table request_queue enable row movement;

Table altered.

SQL> update request_queue
2 set request_status = 'RUNNING'
3 where request_id=1001
4 /

1 row updated.

SQL> commit;

Commit complete.

SQL>
.... sometime later ....

SQL> update request_queue
2 set request_status = 'COMPLETED',
3 request_completion_time=systimestamp
4 where request_id=1001
5 /

1 row updated.

SQL> commit;

Commit complete.

SQL>


(Note that all the previous "Partitioning 3a to 3d" posts about Indexing apply to List Partitioning as well)



Categories: DBA Blogs

Parse string then flatten into columns

Tom Kyte - Fri, 2018-09-14 07:46
Hi, LiveSQL link not accepted by the form: https://livesql.oracle.com/apex/livesql/s/g88hb5van1r4ctc65yp4lq9gb I have this situation (see link): <b>ID String</b> Id1 Thing1: Sub1, <br>Thing2: Sub7 ,Sub8 , Sub9 <br>Thing3: Sub12 Id1 Thing...
Categories: DBA Blogs

Oracle View object - performance Issue with Outer Join including a WITH clause

Tom Kyte - Fri, 2018-09-14 07:46
Hi Tom ; Thank you , I've been using your site for 2 years now, resolved many issues based on your answers. Case Scenario : Customer having multiple addresses , only one is active ; some cases ALL the addresses of a customer could be inactive....
Categories: DBA Blogs

MATERIALISED VIEW ISSUE

Tom Kyte - Fri, 2018-09-14 07:46
Error starting at line : 12 in command - CREATE MATERIALIZED VIEW EMP_MV BUILD IMMEDIATE REFRESH FORCE ON COMMIT AS SELECT EMPID,EMPNAME FROM EMP Error report - ORA-12054: cannot set the ON COMMIT refresh attribute for the materializ...
Categories: DBA Blogs

Recommended partition size

Tom Kyte - Fri, 2018-09-14 07:46
We want to partition some tables using interval partitioning on the creation date. Partitioning is for manageability - we want to drop older partitions eventually - and partition pruning for improving performance. What is the Recommended partition si...
Categories: DBA Blogs

Identify the missing object in an ora-08103 error

Tom Kyte - Thu, 2018-09-13 13:26
We have a large cursor that runs nightly and on a random night we get the following error from the job that calls the cursor: 180911 180019 (APPS.GAINS_COMMON,1194) Begin export_data 180911 194348 (APPS.GAINS_COMMON,4735) BEGIN export_sla 1809...
Categories: DBA Blogs

Oracle locking a table while deleting set of rows

Tom Kyte - Thu, 2018-09-13 13:26
I have 2 scripts running at the same time deleting different set of rows from same table. Would this cause any locking issues or contention? Please advise. Thank you very much.
Categories: DBA Blogs

What SQL is currently running in the database

Tom Kyte - Thu, 2018-09-13 13:26
I am using an application that submits SQL queries to the Oracle database. These queries can have hundreds of bind variables in them. I want to be able to see the SQL that is running with the bind variables substituted. I have used this query to g...
Categories: DBA Blogs

Exchange Partition - Error with VIRTUAL & CLOB columns

Tom Kyte - Thu, 2018-09-13 13:26
Hi Folks, Need your expert advice on this. Could you please guide me through the below issue - Issue : Exchange partition doesn't seem to work when both Virtual column and CLOB column exists together in a table :( . However it works fine on it ...
Categories: DBA Blogs

Giving grant role to invoker from stored procedure

Tom Kyte - Wed, 2018-09-12 19:06
Hello there, As schema owner, I give grant execute on package to another user, who is considered as executor of the package. The package contains security part (procedure with invoker rights (authid current_user)), which checks whether the caller ...
Categories: DBA Blogs

How to strip off characters upto a specific character in a BLOB type column

Tom Kyte - Wed, 2018-09-12 19:06
I have a column of type BLOB which is being used to store images. A typical value that is currently getting stored in this table is: data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAYEBQYFBAYGBQYHBwYIChAKCgkJChQODwwQFxQYGBcUFhYaHSUfGhsjHBY...
Categories: DBA Blogs

Parsing the CLOB data seperated by delimiters

Tom Kyte - Wed, 2018-09-12 19:06
<code> CREATE TABLE T ( DT_FIELD DATE, SKEY NUMBER, FIELD_VALUES CLOB ) INSERT INTO T VALUES('06-SEP-18',10,'68|} 88026 |}ABC|}101010|}ADSD|}'); INSERT INTO T VALUES('07-SEP-18',11,'70|} 88027 |}DEF|}'); INSERT INTO T VALUES('07-SEP-18',12,'...
Categories: DBA Blogs

As per Oracle recommendation we are using Automatic Segment Space Management . We see contentions when we route traffic for DML on multiple RAC nodes

Tom Kyte - Wed, 2018-09-12 19:06
As per Oracle recommendation we are using Automatic Segment Space Management . We see contentions when we route traffic for DML on multiple RAC nodes. The only way I could think we can solve this Manual segment space management for LARGE table wit...
Categories: DBA Blogs

Remap_Data Mutiple Tables- Cannot be applied in expdp?

Tom Kyte - Wed, 2018-09-12 00:46
Hi Tom, Trying to apply remap_data to multiple tables it always fails. The column name is same across both tables where i am applying the function, it exports and imports without any errors from the source schema to the target schema, but it...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs