DBA Blogs

Nvarchar to Varchar2 conversion (UTF8 to AL32UTF8)

Tom Kyte - Thu, 2018-11-15 20:46
We are planning to convert all NVarchar fields to Varchar2 fields as we're going to change our character set and since Oracle recommends AL32UTF8 character set encoding. My question is it 100% sure that all characters from Nvarchar (UTF8) can be conv...
Categories: DBA Blogs

sql plan management - difference in defining parameters at system and session level

Tom Kyte - Thu, 2018-11-15 20:46
Hi Tom, I am very new to performance tuning. there's something that I am unclear about sql plan management. which one is faster - 1. setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE at session level (inside function body) and OPTIMIZE...
Categories: DBA Blogs

Moving Oracle DB from one server to another

Tom Kyte - Thu, 2018-11-15 20:46
Hi, I am having an Oracle 11g database in an AIX linux server. I am planning to move this to a different server with same OS. I will be using same version of Oracle database in target DB as well. I have multiple schema in source database and in t...
Categories: DBA Blogs

SQL Slowdown ? A short list of potential reasons

Hemant K Chitale - Thu, 2018-11-15 20:14
Jonathan Lewis has published a short list of potential reasons why you might see a slowdown in SQL execution.  With newer releases 12.2, 18c and 19c, the list may have to be expanded.



Categories: DBA Blogs

Log DML, DDL and DCL user activity

Tom Kyte - Thu, 2018-11-15 02:26
Hello, Ask TOM Team. I want to know if there's a straightforward (not using triggers or things like that lol) way to log DML, DDL and DCL user activity on specific objects (12c). I do not know if <b>Database Vault</b> can help me with that. Any Do...
Categories: DBA Blogs

DB link is not working between 2 databases

Tom Kyte - Thu, 2018-11-15 02:26
Hi, We have 2 databases DB A and DB B. we have created db link between 2 dbs as a2b; in DB A, we have below table_A and data, <code>create table table_a (emp_id number, emp_name varchar2(30)) / insert into table_a values (1,'Test1') / ...
Categories: DBA Blogs

ORA-64610: bad depth indicator with Utl_Call_Stack

Tom Kyte - Thu, 2018-11-15 02:26
Hi, I have a database in Oracle 12.2.0 There I have deployed a PL/SQL logic which is called from an update trigger. From this logic, following code segment is called to get the format call stack. <code>FUNCTION Format_Stack___ ...
Categories: DBA Blogs

Enable Index To Search For NULLs By Adding Constant to Index List. But Some Constants Better Than Others (Never Let Me Down)

Richard Foote - Wed, 2018-11-14 18:29
By default, Oracle doesn’t index an entry if all columns within the index are NULL. However, (as I’ve blogged previously), it’s possible to index all possible NULL values by simply adding a constant value to the index column list. Importantly, the CBO knows when a column has all it’s NULL values indexed and can potentially […]
Categories: DBA Blogs

Above & Beyond for deploying Oracle Database / Weblogic As Docker Container On Oracle ...

As Oracle Public Cloud computing continues to transform every business and industry, developers at global enterprises and emerging startups alike are increasingly leveraging container technologies to...

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

March 2019 – New Webinar Dates Announced for “Oracle Indexing Internals and Best Practices”

Richard Foote - Tue, 2018-11-13 22:19
I’m very excited to announce two new Webinar events for my acclaimed “Oracle Indexing Internals and Best Practices” training event, running in March 2019 !! For details of all the extensive content covered in the webinars, please visit my Indexing Seminar page. The webinars will run for 4 hours each day, spanning a full week period (Monday […]
Categories: DBA Blogs

Impact of Altering an Oracle sequence from ORDER to NO ORDER

Tom Kyte - Tue, 2018-11-13 13:46
I have a table TAX_INFO where the primary key TAX_INFO_ID is generated using a sequence SEQ_TAX_INFO_ID. Below is the sequence definition. It is defined as ORDERED at the moment <code>CREATE SEQUENCE SEQ_TAX_INFO_ID MINVALUE 1 MAXVALUE 999999999...
Categories: DBA Blogs

Manually refresh materialized view in trigger

Tom Kyte - Tue, 2018-11-13 13:46
Hello, I have a set of MV that are dependant each other and from master tables. All are elligible to be fast-refreshed but for a reason I don't know, in some tables after a delete or update the fast refresh is longer than the complete one. So ...
Categories: DBA Blogs

pragma autonomous in exception

Tom Kyte - Tue, 2018-11-13 13:46
can you create pragma autonomous in exception handling
Categories: DBA Blogs

Database security

Tom Kyte - Tue, 2018-11-13 13:46
Hello, i'd like a suggestion about the use case below We have a database with 2 schemas, for the schemas 1 and 2 every objects are full granted <code>grant all on "object_name" to public</code> Each users have default role <code>"Resourc...
Categories: DBA Blogs

Partitioning -- 9 : System Partitioning

Hemant K Chitale - Tue, 2018-11-13 08:59
System Partitioning, introduced in 11g, unlike all the traditional Partitioning methods, requires that all DML specify the Target Partition.  For a System Partitioned Table, the RDBMS does not use a "high value" rule to determine the Target Partition but leaves it to (actually requires) the application code (user) to specify the Partition.

In my opinion, this seems like the precursor to Oracle Database Sharding.

SQL> create table sys_part_table
2 (id_column number,
3 data_element_1 varchar2(50),
4 data_element_2 varchar2(50),
5 entry_date date)
6 partition by SYSTEM
7 (partition PART_A tablespace PART_TBS_A,
8 partition PART_B tablespace PART_TBS_B,
9 partition PART_C tablespace PART_TBS_C)
10 /

Table created.

SQL>


Notice that I did not specify a Partition Key (column).  The Partitions are not mapped to specific values / range of values in a Key column.

Any DML must specify the Target Partition.

SQL> insert into sys_part_table
2 values (1, 'First Row','A New Beginning',sysdate)
3 /
insert into sys_part_table
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method


SQL>
SQL> !oerr ora 14701
14701, 00000, "partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method"
// *Cause: User attempted not to use partition-extended syntax
// for a table partitioned by the System method
// *Action: Must use of partition-extended syntax in contexts mentioned above.

SQL>
SQL> insert into sys_part_table partition (PART_A)
2 values (1, 'First Row','A New Beginning',sysdate)
3 /

1 row created.

SQL> insert into sys_part_table partition (PART_B)
2 values (2,'Second Row','And So It Continues',sysdate)
3 /

1 row created.

SQL>


I have to specify the Target Partition for my INSERT statement. This, obviously, also applies to DELETE and UPDATE statements.   However, I can run a SELECT statement without filtering (pruning) to any Target Partition(s) -- i.e. a SELECT statement that does not use the PARTITION clause will span across all the Partitions.

SQL> select * from sys_part_table;

ID_COLUMN DATA_ELEMENT_1
---------- --------------------------------------------------
DATA_ELEMENT_2 ENTRY_DAT
-------------------------------------------------- ---------
1 First Row
A New Beginning 13-NOV-18

2 Second Row
And So It Continues 13-NOV-18


SQL>


With Tablespaces assigned to the Partitions (see the CREATE table statement above),  I  can have each Partition mapped to a different underlying Disk / Disk Group.
.
.
.
Categories: DBA Blogs

Strange behaviour with excecute immediate.

Tom Kyte - Mon, 2018-11-12 19:26
Hi, Had problems with SQLLive, (500 response), therefore the examples are her. I have a strange behaviour with execute immediate, where it behaves differently from within a PL/SQL procedure than it does when running it standalone. Here is th...
Categories: DBA Blogs

Difference between named sequence and system auto-generated

Tom Kyte - Mon, 2018-11-12 19:26
Hello, Guys. A new db (12c) will have lots of tables with sequence used as PK. What is the difference between named sequence and system auto-generated in Oracle 12c? What would be the best approach?
Categories: DBA Blogs

Row Chaining

Tom Kyte - Mon, 2018-11-12 19:26
Hi Tom What is row chaining/migration ? what are the consequences of Row Chaining/Migration ? How I can find whether it is there in my database or not ? and if it is there what is the solution to get rid from it? Thanks in advance Howie
Categories: DBA Blogs

Run procedures or functions in parallel

Tom Kyte - Mon, 2018-11-12 19:26
hello, I've web app which is using procedures and functions inside packages most of the time, in some cases procedures and functions execution taking long time to return data (as SYS_REFCURSOR). the problem is that when other users execute other p...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs