DBA Blogs

Oracle Database 10g on Windows 2003 R2 does not start upon reboot

Tom Kyte - Fri, 2017-07-07 23:26
HEllo !! ## PROBLEM STATEMENT ## This server has just been handed over to me after it created problem. DB is not starting through WIndows Services. Windows service for Oracle starts running successfully but db stays dead. I have tried fol:- ...
Categories: DBA Blogs

Unable to open Database as redo log file seems missing

Tom Kyte - Fri, 2017-07-07 23:26
Hi Guys, I am facing a serious issue with one of our databases as I am unable to open it. When I try to open the database, I get the following error- SYS@omc> alter database open; alter database open * ERROR at line 1: ORA-00313: open faile...
Categories: DBA Blogs

inconsistent behavior of table() function

Tom Kyte - Fri, 2017-07-07 23:26
Hi, I try to use table() function over collection of rows. It fails in delete statements (delete MY_TABLE where xxx in (select yyy from table(collection)). When I use the same construction in select statement it works fine. Same issue exists for upd...
Categories: DBA Blogs

Creating PK constraint and index on very large partitioned table

Tom Kyte - Fri, 2017-07-07 23:26
Hey Tom, It has been a few years since I did DBA work but I have been tasked with moving a database from one place to another so that the source location server can be rebuilt. So I created DDL scripts for each object off of the source database a...
Categories: DBA Blogs

DELETE Vs Bulk DELETE

Tom Kyte - Fri, 2017-07-07 23:26
Hi Connor/Chirs, I tried executing below two statements to delte records from tb_test, and the statement with FORALL delete works faster? EXECUTE IMMEDIATE 'DELETE FROM tb_test WHERE ROWID IN (SELECT column_value FROM TABLE(CAST(:tb_rowid as VC_A...
Categories: DBA Blogs

Reference on Histograms

Tom Kyte - Fri, 2017-07-07 23:26
Hi Connor, There are several references posted by you on oracle features like partition, indexes on YouTube Keep It Simple SQL. Is there any video references on Histograms & Index Organised Tables by you or any TOM member? Can you please share i...
Categories: DBA Blogs

The importance of being CURRENT_USER

Flavio Casetta - Fri, 2017-07-07 06:00
Categories: DBA Blogs

Procedures, roles and grants

Tom Kyte - Fri, 2017-07-07 05:06
Hi Tom, We are using 2 schemas, user1, user2. User1 has user1_table, and created role role1. The following was issued: grant select, insert, update, delete on user1_table to role1; grant role1 to user2 User2 created private synonym user1_table...
Categories: DBA Blogs

Huge Blob Data over DB link

Tom Kyte - Fri, 2017-07-07 05:06
Hi, We have an implementation where datasize has grown over 24TB's and most of it(13 TB's including the indexes) is from couple of BLOB tables which have partitions(around 40 for biggest table) and subpartitions(44 in each partition). Now the task...
Categories: DBA Blogs

Virtual column issue : ORA-31495: error in synchronous change table on Schema.Tablename

Tom Kyte - Fri, 2017-07-07 05:06
Hi Team, I am facing tricky issue that never engage before in my database. WE have some tables which caring few virtual columns. These virtual columns are generated using user define function which is just doing strings to upper. It was working fi...
Categories: DBA Blogs

Bind mismatch(21):

Tom Kyte - Fri, 2017-07-07 05:06
Hi, I have a Database with cursor mismatch for a sql_id , but I cant find information about this Bind mismatch "mismatch(21)" neither for new_oacexl. This is a RAC three node, the result of tannel script nonshared2.sql for this sql_id is SQL...
Categories: DBA Blogs

build a hierarchy caculation with ONE sql

Tom Kyte - Fri, 2017-07-07 05:06
Hi Team, We want to build a hierachy process bar with one SQL but failed. We decide to turn to you guys for help. Plz allow me to put it in this way with a simple test. >>> Here's my test data: <code>create table DEMP ( ename VARCHAR2(1...
Categories: DBA Blogs

View for finding Bind variable values

Tom Kyte - Fri, 2017-07-07 05:06
Hi, We are using the 11.2.0.4 database enterprise edition , and due to the application performance need we had to set CURSOR_SHARING=FORCE, as application was not able to use bind variables. When i try to generate the PLAN for sql_id using OR w...
Categories: DBA Blogs

Oracle Cloud Machine ; Your Own Cloud Under Your Own Control

Pakistan's First Oracle Blog - Fri, 2017-07-07 04:12
Yes, every company wants to be on cloud but not everyone wants that cloud to be out there in wild no matter how secure it is. Some want their cloud to be trapped within their own premise, under their own control.




Enters Oracle Cloud Machine.

Some of the key reasons why this would make sense are sovereignty, residency, compliance, and other business requirements. Moreover, the cloud benefits would be there like turn key solutions, same IaaS and PaaS environments for development, test and production.

Cost might be a factor here for some organizations so a hybrid solution might be a go for majority of corporations.Having a private cloud machine and also having a public cloud systems would be the solution for many. One advantage here would be that the integration  of this private cloud with public one would be streamed lined.
Categories: DBA Blogs

performance tuning - library cache pin and library cache lock

Tom Kyte - Thu, 2017-07-06 10:46
Hi , i want to know about wait events of library cache pin and library cache lock in my environment i have faced this issue but i am not able to get session whos is waiting for library cache pin and session which is acessing that object . ...
Categories: DBA Blogs

BULK COLLECT on DB types Vs PL/SQL types

Tom Kyte - Thu, 2017-07-06 10:46
Hi, I have writtem below PL/SQL block to populate ROWID of table into intermidiate table tb_load_stats. Below block works fine when TYPE typ_rowid/t_typ_rowid is directly used in PL/SQL block. But when these types are created as a oracle obj...
Categories: DBA Blogs

Oracle SQL statement is taking long time

Tom Kyte - Thu, 2017-07-06 10:46
<code>Hi , My SQL statement is taking long time approximately 2.30 hr to complete. I have query like below Select from tables UNION Select from tables Here is the Gather Stat Plan. SQL> SELECT * 2 FROM TABLE(DBMS_XPLAN.DISPLAY...
Categories: DBA Blogs

Performance Issue due to RMAN Jobs

Tom Kyte - Thu, 2017-07-06 10:46
Hello, We are facing performance issues on our database. Our application is designed in such a way that it is rigorously used only for <b>5-days</b> in a month and while it is used there are around 300 sessions running in parallel at any point...
Categories: DBA Blogs

Find oracle database owner

Tom Kyte - Thu, 2017-07-06 10:46
Hi Ora experts, I have some quick queries regarding oracle database. Is there any way to find an oracle database owner information (in Windows platform, where I don't have 'oratab' file)? Also, we also need to find the 'last access time' of an or...
Categories: DBA Blogs

V$SQL_PLAN_STATISTICS

Tom Kyte - Thu, 2017-07-06 10:46
Dear Tom, Does V$SQL_PLAN_STATISTICS shows only the statics about the current (connected) session? SQL> select count(*) from v$sql_plan ; --I can get the plan details here COUNT(*) ---------- 18540 SQL> select count(*) from V$SQ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs