DBA Blogs

Fetching the type of join, columns used in join and filter for a given select query

Tom Kyte - Sun, 2017-03-26 22:26
Dear Sir, I have a requirement to parse a given select query to get the columns used in joins, type of joins and filters used. Can you please suggest how to do this. If this issue was already discussed in this blog, my apologies for raising it o...
Categories: DBA Blogs

Question about undo management

Tom Kyte - Sun, 2017-03-26 22:26
Hi, I have two questions about automatic undo management. 1.Assume my undo tablespace has maximum size to 32GB with undo_retention=900 and currently it's used 10GB. While I querying a table more than 15 min and others users perform DML stateme...
Categories: DBA Blogs

Getting Compilation errors for PACKAGE BODY NAV_PKG_ST Error: PLS-00306: wrong number or types of arguments in call to '||' Line: 1728 Text: EXECUTE IMMEDIATE 'INSERT INTO '||TAB_COUNT.TABLE_NAME ||' VALUES '||V_CRS_IS_DART_COLL(I)

Tom Kyte - Sun, 2017-03-26 22:26
Thanks in Advance TOM I am Srinadh And i have an issue in my code. i am using dynamic sql to insert data into some of tables(Cursor C_TABLES ) from Views(Cursor DAIL_VIEWS ). i am getting the above error and also i'm not sure can we use Dyna...
Categories: DBA Blogs

regarding oracle database export

Tom Kyte - Sun, 2017-03-26 22:26
i am trying to export database.i am using arabic in my databse . my database Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 client charaterset oracle 6i on windows10 machine AMERICAN_AMERICA.WE8ISO8859P1 database charaterset ...
Categories: DBA Blogs

dbms_redefinition VS dbms_stats.gather_schema_stats

Tom Kyte - Sun, 2017-03-26 22:26
Hi, Can you please advice which scenario we need to use these. dbms_redefinition VS dbms_stats.gather_schema_stats 1) As per my Knowledge We need to use dbms_stats.gather_table_stats after large number of DML occur on the table .So that ta...
Categories: DBA Blogs

Bulk Collect Usage

Tom Kyte - Sun, 2017-03-26 22:26
Hi, Could you please let me know that how to judge that what should be our limit while using a bulk collect for fetching cursors.I have read in various blogs that using bulk collect is not always a good option as it can degrade performance in few ...
Categories: DBA Blogs

12.2 Online Conversion of a Non-Partitioned Table to a Partitioned Table (A Small Plot Of Land)

Richard Foote - Sun, 2017-03-26 18:46
In my previous post, I discussed how you can now move heap tables online with Oracle Database 12.2 and how this can be very beneficial in helping to address issues with the Clustering Factor of key indexes. A problem with this technique is that is requires the entire table to be effectively reorganised when most of […]
Categories: DBA Blogs

Execute a procedure up to 5 milion times

Tom Kyte - Sun, 2017-03-26 04:06
Hello, I represent in my schema the following tables : Container : contains a column ContainerName (unique) and represents a container Container_Item : contains a column ContainerItemName (unique) and represents a container item Container_Can...
Categories: DBA Blogs

Adding constraints to an integer attribute that defaults to 0

Tom Kyte - Sun, 2017-03-26 04:06
Hi Tom, I have two tables, Table A that contains my primary key x and Table B with attribute y that references Table B but I had not added constraints until now. Thus, A -> x B ->y and y references x. I need to add a constraint on Table...
Categories: DBA Blogs

With clause in Oracle

Tom Kyte - Sat, 2017-03-25 09:46
Hi all, "With clause" in Oracle will store the results of select query in cache and will show the results from cache to display records next time for the same statement? What is the advantage and alternative for "With clause" other than using f...
Categories: DBA Blogs

Table Reorg

Tom Kyte - Sat, 2017-03-25 09:46
Most of the DBA books on Oracle discuss that if a table has lot of empty blocks below the HWM, the best way to reorg is to do the following steps : 1. CREATE TABLE N AS select * from O 2. Drop O 3. Create Owith appropriate INITIAL_EXTENT 4. CRE...
Categories: DBA Blogs

Is Oracle Database in Cloud PaaS, IaaS, SaaS, or DBaaS?

Pakistan's First Oracle Blog - Sat, 2017-03-25 01:15
Question: Is Oracle Database in Cloud PaaS, IaaS, SaaS, or DBaaS?
Answer:
  • If you install and manage Oracle database in cloud by yourself, then you are using it on IaaS.
  • If you are just using it in cloud without installing or managing it, then it's PaaS.
  • If you are configuring the database instance and have access to it through SQL*Net, then its DBaaS. 
  • SaaS not really relevant when it comes to Oracle database in cloud as database mostly reside at the backend of applications whereas Saas is primarily all about applications.


Categories: DBA Blogs

DBMS_SCHEDULER Programs are not being dropped automatically once the job completed

Tom Kyte - Fri, 2017-03-24 15:46
Hi, Whether the Programs created using DBMS Scheduler would be dropped automatically when the Job completed successfully ? Thanks in advance !!
Categories: DBA Blogs

Not possible to revoke a grant on an invalid view from a role

Tom Kyte - Fri, 2017-03-24 15:46
During one of our framework scripts, we perform revoke operations on some views from different product areas in our application which had already been granted. There we noticed that, if a view is invalid due to some reason, the revoke operation error...
Categories: DBA Blogs

Effective way to compare/merge large tables

Tom Kyte - Fri, 2017-03-24 15:46
Dear Oracle Gurus ;) I have two big tables (client accounts, 50+ million rows, same fields but different rows) in DWH environment: - first table coming daily as raw data from OLTP server to staging area of DWH server, contains all client accounts...
Categories: DBA Blogs

DBMS_JOB and SYS_CONTEXT ('USERENV', 'SERVICE_NAME')

Tom Kyte - Fri, 2017-03-24 15:46
Hi. When scheduling a job using DBMS_JOB, I make use of the value in SYS_CONTEXT ('USERENV', 'SERVICE_NAME'). As an example, I have this procedure: <code>CREATE OR REPLACE PROCEDURE proc_test IS BEGIN INSERT INTO test_table (test_value) ...
Categories: DBA Blogs

Temporary tablespace size on standby database

Tom Kyte - Fri, 2017-03-24 15:46
Hello, More than a question is a curiosity, if there is a dataguard enviroment physical - standby is it needed that both databases servers have the same size of their temporary tablespace? I know temporary tablespace is just for sort and joining o...
Categories: DBA Blogs

session cursor caching

Tom Kyte - Fri, 2017-03-24 15:46
Hello Experts, As per my understanding, cursors cached in pl/sql cache of an anonymous Pl/SQL block are closed after execution of the block so that next execution requires library cache to be visited to confirm the presence and validity of the cur...
Categories: DBA Blogs

Data Conversion Formats

Tom Kyte - Fri, 2017-03-24 15:46
Hi, I've been tasked with loading several csv data files into a single oracle database, which would be no issue but for the fact that I have no less than 7 different date/time format to deal with. Listed below are the 5 that I don't know how to h...
Categories: DBA Blogs

Issue with the dbms_sql concept

Tom Kyte - Fri, 2017-03-24 15:46
Hi Team, well i have a requirement i have a table here is the script of the table. <code> CREATE TABLE SCOTT.T ( SOURCE VARCHAR2(50 BYTE), ATLAS VARCHAR2(20 BYTE), HER VARCHAR2(20 BYTE), BUZZ VARCHAR2(20 BYTE), ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs