DBA Blogs

RefCursor and Multiple responses in single call

Tom Kyte - Fri, 2016-07-15 00:06
Hello Tom, Thank you in advance for taking my question and for this wonderful platform. For search functionality for our Content Management system, I have created a package which returns a ref cursor. The package accepts search critetrias, dy...
Categories: DBA Blogs

Database Security

Tom Kyte - Fri, 2016-07-15 00:06
In Our organization , two employees who copy whole windows server system to image file. And our server has business and people's related information stored in oracle database. Both does not know the password but they know sys user. and they are a ...
Categories: DBA Blogs

Query does not use index

Tom Kyte - Fri, 2016-07-15 00:06
I would like to understand why my sql instruction doesn't use an index; I have a table that has 6 million rows. And I'm trying to extract some rows and Oracle optmizer are not using the index existent in column. TABLE ESTRUCTURE: <code> COLUM...
Categories: DBA Blogs

How to loop a select statement while inserting the values in a table

Tom Kyte - Fri, 2016-07-15 00:06
create or replace PROCEDURE PROC_PROJ_ID_AUTO_GENERATE(op_error_code OUT VARCHAR2, op_succ_msg OUT VARCHAR2) BEGIN FOR i IN (select a.program_id , a.PRODUCTIVITY_IDENTIFIER ,TARGET_START_DATE ,TARGET_COMP_DATE , PRGM_...
Categories: DBA Blogs

Truncate on cluster table

Tom Kyte - Thu, 2016-07-14 05:46
Hi Team, Could you please help me on how to truncate a table which part of a cluster. I want to truncate a table and getting below error. please help me ASAP. Below is the error: SQL> truncate table TEST1; truncate table TEST1 ...
Categories: DBA Blogs

Rebuild optimize failing for Text index in RDBMS 12c (12.1.0.2.0) CDB-PDB config

Tom Kyte - Thu, 2016-07-14 05:46
Hi Guys, DB: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production OS: OEL 6.5 I am testing use case for Oracle Text index on 12c CDB-PDB config (single PDB), and found that REBUILD optimize is failing with internal exce...
Categories: DBA Blogs

Spatial queries

Tom Kyte - Thu, 2016-07-14 05:46
Hi Tom I am calculating distance between two 2 points (using their gps co-ordinates) from the below query (Query 1). One column (name geo_location of sdo_geometry datatype where spatial index is created) in my table stores the geometric location o...
Categories: DBA Blogs

Session Memory Release

Tom Kyte - Thu, 2016-07-14 05:46
Hi Connor, Wanted to understand the session memory release process. 1] A database 2] Connections are established to the database, through connection pool 3] Connection pool is configured in Application Server [ Weblogic ] 4] The connection p...
Categories: DBA Blogs

Is it possible to overwrite OS User when connecting to oracle database from a .NET Application

Tom Kyte - Thu, 2016-07-14 05:46
I have a .NET Desktop Application which runs on our user's machine individually, as part of one of our functionality we need to pull data from Upstream that has data stored in an Oracle Database. Currently we get data from this oracle db by logging ...
Categories: DBA Blogs

Cardinality of table collections in SQL

Tom Kyte - Thu, 2016-07-14 05:46
I'm doing two queries, one will use an index I am trying to use. The first query it does not use the index and does full table scan. number_tbl will be a parameter passed into a stored procedure. We are using a type to pass in a table of numbers. ...
Categories: DBA Blogs

Migration of about 20000 Tablespaces from Solaris Sparc 10 with Oracle 9.2.0.4 to OL 7.2 64bit Oracle 12c

Tom Kyte - Thu, 2016-07-14 05:46
Hello Tom we plan a migration of our production system. we think we have two possibilities 1) one is from the 9i system exporting all TS's with exp and go directly to the 12c and do with imp all tablespaces in the new database but this optio...
Categories: DBA Blogs

sql loader delimiters

Tom Kyte - Thu, 2016-07-14 05:46
columns separated by ',' in data file but in control file by mistake if i mentioned field terminated '/' in control file.then what happen while loading data.
Categories: DBA Blogs

Log Buffer #482: A Carnival of the Vanities for DBAs

Pythian Group - Wed, 2016-07-13 13:29

This Week’s log buffer edition covers some of the useful blog posts from Oracle, SQL Server and MySQL.

Oracle:

ASM disks – lsdg compared with the v$asm_diskgroup view

Can a query on the standby update the primary ?

What should I know about SQL?

Setting Environment Variables in Application Server/Process Scheduler Tuxedo Domains

Oracle HEXTORAW Function with Examples

SQL Server:

Query Store is a new feature in SQL Server 2016 which, once enabled, automatically captures a history of queries, execution plans, and runtime statistics, retaining them for your troubleshooting performance problems caused by query plan changes.

Finding and Eliminating Duplicate or Overlapping Indexes

Changing Linked Server Properties

Windows Containers and Docker

Stretch Database in SQL Server 2016 RTM

MySQL:

Why Adaptive Fault Detection is Powerful and Unique

Develop By Example – Document Store Connections using Node.js

libssl.so.6: cannot open shared object file with MariaDB Galera

How to make sure that ‘password’ is not a valid MySQL password

MySQL 5.7, utf8mb4 and the load data infile

Categories: DBA Blogs

How can we skip existing values while inserting with sequence to id column(unique)

Tom Kyte - Wed, 2016-07-13 11:46
Hi Tom, Can you please let me know best possible way for below scenario to skip existing value while loading through sequence Table Structure: Tem_seq_check(id number(3) unique,name varchar2(5)); Tem_name(name varchar2(5)). I will insert...
Categories: DBA Blogs

Message queue

Tom Kyte - Wed, 2016-07-13 11:46
Hi, I have a situation where I was asked to use message queue technique, can you please suggest me whether it is possible/feasible or not. There are two data bases D1 & D2, upon changing the status of a particular field in D1 some data need to ...
Categories: DBA Blogs

get ORA-01031: insufficient privileges when execute procedure

Tom Kyte - Wed, 2016-07-13 11:46
Hello there: I met the "ORA-01031: insufficient privileges" error, when rebuild index with online option in a procedure. I know the role cannot be used in procedure, so grant some privileges to system(its DBA' user:system privilege: -- 2...
Categories: DBA Blogs

Analytics question

Tom Kyte - Wed, 2016-07-13 11:46
<code> I have a table from a 3rd party application that is used to track an order through the various manufacturing operations. A subset of the information looks like this: ORDER OPN STATION CLOSE_DATE ----- --- ------- ----------...
Categories: DBA Blogs

not able to re-create materialized view on prebuilt table

Tom Kyte - Wed, 2016-07-13 11:46
Steps I am trying to execute : <code> CREATE TABLE sample.MV(application_mode varchar2(25)); CREATE MATERIALIZED VIEW sample.MV ON PREBUILT TABLE REFRESH FORCE ON DEMAND AS SELECT application_mode FROM sample.tbl_name WHERE cnt > 0 ...
Categories: DBA Blogs

oracle lsitener config

Tom Kyte - Wed, 2016-07-13 11:46
I am trying to understand how the oracle listener gets its config info. I see this when I run lsnrctl status: Listener Parameter File /home/oracle/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /home/oracle/u0...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs