DBA Blogs

Database objects: package spec and bodies

Tom Kyte - Tue, 2018-01-16 18:26
Hi, Are package body and package specifications different objects,bcz in DBA_OBJECTS view i see different object_id's for same package body and specification?
Categories: DBA Blogs

Indexing at very Large Table

Tom Kyte - Tue, 2018-01-16 18:26
Hello Tom, I am indexing a very large table with more than 500 million rows. This table is partitioned. I needed to create the index on the table. I am creating the local index with parallel and nologging option. Once I was doing it, it was runnin...
Categories: DBA Blogs

Exiting SQL Plus when can't connect

Tom Kyte - Tue, 2018-01-16 18:26
I have to launch SQL Plus using command line options with user/password. If connection don't work, SQL Plus don't terminate the execution, but ask for another login: >> SQL*Plus: Release 11.2.0.1.0 Production on Mar Gen 16 13:26:00 2018 >> >> ...
Categories: DBA Blogs

Why are triggers a bad idea

Tom Kyte - Tue, 2018-01-16 18:26
Tom, thanks for your devotion to duty in answering the world's Oracle questions. I am struggling to explain to colleagues precisely why triggers are a bad idea in Oracle. I have read your article entitled "The trouble with Triggers" and it say...
Categories: DBA Blogs

Risk associated with Oracle Flashback technology to get records modified

Tom Kyte - Tue, 2018-01-16 18:26
Hello, I have one question for you regarding the data modified. I am working on a project where one team is making modifications in database (insert, update and delete). The data is being modified using one editor software and the software does no...
Categories: DBA Blogs

Can we create nested package in PL/SQL like we do in Java?

Tom Kyte - Tue, 2018-01-16 18:26
Can we create nested package in PL/SQL like we do in Java? I mean can we create a package inside package in Oracle PL/SQL 11g?
Categories: DBA Blogs

Partner Webcasts on Oracle IoT Cloud Services

Eight zetabytes of data are coming from tens of millions of devices. Where are you going to put the data, and what are you going to do with it? Internet of things (IoT) is all about integrating and...

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

which approach should i apply to fetch 6 millions records from select statement from various table

Tom Kyte - Mon, 2018-01-15 23:46
I have one requirement where i needs to fetch all the Install base records along with other details.currently in our system there are 6 millions are exist.So please help us to choose better approach for this.
Categories: DBA Blogs

duplicate rows

Tom Kyte - Mon, 2018-01-15 23:46
How to find duplicate rows in a table ?
Categories: DBA Blogs

Observing a "create table as select statement"

Tom Kyte - Mon, 2018-01-15 23:46
Hi Tom, In a customer project we are using quite a lot of "create table as select ...." (cannot change them), that are taking quite a while (some hours). I would like to observe the growth of these objects, while they are created, but I'm bumpin...
Categories: DBA Blogs

big file tablespace

Tom Kyte - Mon, 2018-01-15 23:46
I have a big file tablespace?which has 100G size?I wonder ,big file tablepspace vs normal small file tablespace?which is better? someone said, it is very difficult to recover if bad block occurs in a big file, and what's more, performance is worse th...
Categories: DBA Blogs

convert from char to varchar2 - retrieve space

Tom Kyte - Mon, 2018-01-15 23:46
Hi Tom; We are working on Oracle 11g standard edition. By mistake, several CHAR fields have been created in several large tables, which generated an increase in the space occupied by them. We convert the fields to VARCHAR2, but we can not recove...
Categories: DBA Blogs

JOIN with select first item

Tom Kyte - Mon, 2018-01-15 23:46
Hello, my first query "select Barcode as sample_barcodes" returns the following entries: <code> ----------------- SAMPLES_BARCODES 50027 50028 50029 ----------------- </code> my second query "select CampaignItemId, Barcode from MyView...
Categories: DBA Blogs

How to get MIN and MAX of Consecutive Numbers

Tom Kyte - Mon, 2018-01-15 23:46
Thanks for your awesome help on "Trying to split serial number ranges" question today. I have another opportunity to ask a question and it's somewhat related to my earlier question. I hope this would be an easy one. I have a table with the foll...
Categories: DBA Blogs

Coding Parallel Processing on 12c

Tom Kyte - Mon, 2018-01-15 23:46
Hello Tom, I gave the below link a try and applied the method on 12c. https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542 But it takes same time as serial processing. Could you please light me up what I'...
Categories: DBA Blogs

Using FBA with Materialized Views

Tom Kyte - Mon, 2018-01-15 05:26
Please refer to the LiveSQL link. NB some of the statement do not work because the user has insufficient privs. to create and manage Flashback areas in the LiveSQL environment. The code creates a table, inserts data , creates a flashback archive t...
Categories: DBA Blogs

DBMS_AQ.LISTEN to listen to a Single/Multi-Consumer Queue

Tom Kyte - Sun, 2018-01-14 11:06
Dear Experts, Need your guidance/suggestions to resolve this issue: Part of oracle advance queueing implementation, we've to dequeue the message as soon as it has been enqueued into the queue. This should happen immediately without any manual inter...
Categories: DBA Blogs

Doing DB upgrade RAC , via DBUA, from 11gr2 to 12cr2 . Using TDE (tablespace level) on source database

Tom Kyte - Sun, 2018-01-14 11:06
I am running a DB 11.2.0.4 (RAC db) that has TDE implemented - Tablespace level. Source db (11.2.0.4) has TDE implemented. sqlnet.ora file on each node has the entry ENCRYPTION_WALLET_LOCATION. Also each node has the wallet and auto login file (t...
Categories: DBA Blogs

Audit Trail : Disable my bash script audit

Tom Kyte - Sun, 2018-01-14 11:06
Hello Tom. I set audit trail to "XML,EXTENDED" , because my $AUD table was growing to much. I have a lot of 4kb files generated. I have several scripts in my crontab, and that is what is being audited. The content of the files are like this:...
Categories: DBA Blogs

YTD logic using analytic functions

Tom Kyte - Sun, 2018-01-14 11:06
Hi Tom, I am trying to get YTD in a view. I have below view, <code>create or replace view billsummary as select szRegionCode, szState, szPartitionCode, szProduct, TO_CHAR(dtSnapshot,'YYYY.MM') szMonthYear, szJioCenter, ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs