DBA Blogs

In-Memory area static pools

Tom Kyte - 4 hours 59 min ago
Hello team, I have noticed that In-Memory area has static pools with fixed amount of memory allocated to them. Here is an example: <code> SQL> select * from v$inmemory_area; POOL ALLOC_BYTES USED_BYTES POPULATE_STA ...
Categories: DBA Blogs

calling stored procedure,stored function inside the trigger

Tom Kyte - 4 hours 59 min ago
hi Tom, 1)How to call stored function inside the trigger? 2)Is it possible to call stored function,stored procedure inside the trigger at a time, please can show with an example ?
Categories: DBA Blogs

Recursive function that uses FETCH cursor BULK COLLECT LIMIT

Tom Kyte - 4 hours 59 min ago
I am extracting data from complex XML documents into the database. The way that I have approached this is to write functions which handle lists of elements. So, the initial procedure opens the entire XMl document and I extract data from it using a ...
Categories: DBA Blogs

Multi-tenancy database vs. limited disk read throughput

Tom Kyte - 4 hours 59 min ago
Hi AskTom team, I have relatively small SaaS app with "poor man" multi-tenancy database architecture, there is an tenantId field in each table and application server properly generates SQL queries with tenantId in the where clause. There are th...
Categories: DBA Blogs

resetlogs

Tom Kyte - 4 hours 59 min ago
Hi Tom, As we all know we need to open up db with resetlogs when ever incomplete recovery done. 1)why we need open up database with resetlogs after rename the database using control file method or using nid tool? 2)and what more situations n...
Categories: DBA Blogs

How to make sure the tablespace is empty

Tom Kyte - 4 hours 59 min ago
Hello, I am implementing TDE (tablespace level) and once I have moved tables/indexes etc, from a tablespace to an encrypted tablespace, i want to make sure that tablespace has no objects. I am using the following query to make sure that tablesp...
Categories: DBA Blogs

Multiple Partial Search in single field

Tom Kyte - Mon, 2017-02-27 10:06
create table t (id number, name varchar2(30)) insert into t (111, 'MGR_123 NAME 123'); insert into t (123, 'Silvaji_521 main 234'); insert into t (786, 'Rajini_786 sounth 111'); insert into t (678, 'vIKRAm_333 nila 532441'); insert int...
Categories: DBA Blogs

Number internal storage and display

Tom Kyte - Mon, 2017-02-27 10:06
Hi Guys, I tried to execute the below script, but got some unambiguous result while fetching the record. I am trying this query in Windows 7 Operating system. SELECT * FROM V$Version; ----------------------------------------------------...
Categories: DBA Blogs

query issue

Tom Kyte - Mon, 2017-02-27 10:06
hi team , i have a scenario.I have a statement a,b,c, here by using regular expression regexp_replace i need to replace the commas in the statement with ||','|| and the last comma in the statement which is present should be replaced with nul...
Categories: DBA Blogs

copy of table from existing table

Tom Kyte - Mon, 2017-02-27 10:06
Hi, How will you copy the table from existing table to new table by ommiting a particular column name. and if columns are few we can specify the column name but its very big table and so many columns are there.
Categories: DBA Blogs

Report of Schedule Job in Oracel SQL Developer

Tom Kyte - Mon, 2017-02-27 10:06
Dear all, How could I develop a report to run by Schedule Job in Oracel SQL Developer?
Categories: DBA Blogs

how can I partition a table based on Persian months format?

Tom Kyte - Mon, 2017-02-27 10:06
Hello Dear Tom As you know in oracle 12c Iran territory is supported. <code> col PARAMETER format a30; col VALUE format a30; select * from v$nls_valid_values where lower(VALUE) in ('iran','persian'); PARAMETER VALUE ...
Categories: DBA Blogs

Understand the behaviour of nextval of a sequence in SQL and PL/SQL

Tom Kyte - Mon, 2017-02-27 10:06
Hello Oracle Masters, I have a question about the <b>nextval</b> pseudocolumn of a sequence. I thought that the call of nextval produces each time a new integer, while currval returns the current value of the sequence. In the script that you see...
Categories: DBA Blogs

Downgrade a fresh 11.2.0.4 database [not upgraded] to 11.2.0.1

Tom Kyte - Mon, 2017-02-27 10:06
Hi Tom, I need to downgrade a newly installed database 11.2.0.4 to 11.2.0.1, Is it possible?, please let me know the steps involved. There is a metalink note but it is only for databases which were previously upgraded. Thanks a lot in advance. ...
Categories: DBA Blogs

Data Guard

Tom Kyte - Sat, 2017-02-25 21:26
Hello, Tom Can you explain guaranteed protection mode of the Data Guard? What happens with primary database instance in this mode if standby database is unavailiable? Documentation says in this case all changes to primary is prohibited. If it i...
Categories: DBA Blogs

Load JSON data file into Oracle 12c

Tom Kyte - Sat, 2017-02-25 21:26
Hi Ask Tom Team, I have a requirement to load JSON file into a table in Oracle database. Have seen some examples using External Table and filling it from a JSON File & then extracting fields through JSON functions to load data into relational ...
Categories: DBA Blogs

Update query

Tom Kyte - Sat, 2017-02-25 21:26
CREATE TABLE DB_FV_WG ( STORE_ID NUMBER(3,0) Not Null, FV_02 NUMBER, Y_02 NUMBER, YEAR VARCHAR(6), Month_NO NUMBER(2,0), L_Code NUMBER(2,0) ) Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('111',null,null,2016,...
Categories: DBA Blogs

Logminer

Tom Kyte - Sat, 2017-02-25 21:26
The v$logmnr_contents view shows the transactions from some tables as "unsupported". It seems to not like some of the column data types... I need to know how is it that in normal RMAN recovery, these transactions are successfully applied to the datab...
Categories: DBA Blogs

Procedure help - privileges

Tom Kyte - Sat, 2017-02-25 03:06
I am creating a procedure within a schema that has dba access and delete any table privilege. I am deleting from other schemas using this procedure. It gives a compilation error saying the table doesn't exist. Do I need to have direct delete privile...
Categories: DBA Blogs

Best approach / best practices for application integration at the database level

Tom Kyte - Sat, 2017-02-25 03:06
Hi there, There are two applications, which I'll call Application A and Application P. Both applications are third party products with limited ability to modify the application. However, it's possible to make changes to the database, such as addin...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs