DBA Blogs

Someone Delete data

Tom Kyte - Fri, 2017-04-21 03:06
In my office, someone deleted the Procedure. I want to know who did it? What time did it? Please tell the query to find the person information's like IP Address or Name.
Categories: DBA Blogs

Table Backup

Tom Kyte - Fri, 2017-04-21 03:06
Hi Team, I have had gone through one question in interview,I could not find answer to that. If you can help me with that. Let say,there is one table employee with millions of record and we want to increase the column size address_col(10) to add...
Categories: DBA Blogs

External table preprocessor

Tom Kyte - Fri, 2017-04-21 03:06
Hello, I have to read a file via external table but this file it's located into another directory inside the ASM. As first step I have to move the file So, I decided to use the preprocessor feature running a script Here's my table (premise: ...
Categories: DBA Blogs

How to get rid of double quotes from get_ddl

Tom Kyte - Fri, 2017-04-21 03:06
Hi Team, Is there any way to get rid of the double quotes from the output of dbms_metadata.get_ddl. Following is the sample output of dbms_metadata.get_ddl('TABLE','TEST') : e.g., CREATE TABLE "TEST" ( "F1" NUMBER(4,0) NOT NULL ENAB...
Categories: DBA Blogs

ROWID not incrementing. Need an alternative for deduping

Tom Kyte - Fri, 2017-04-21 03:06
Hi Tom, I have a SQL script which (runs daily) inserts data into a table and then de-duping is done based on the rowid. But recently I have observed that rowid is not incrementing with inserts and instead Oracle is re-using space to generate rowid...
Categories: DBA Blogs

How to create a stored procedure only when a particular table exists

Tom Kyte - Fri, 2017-04-21 03:06
Hi Tom, I'm trying to create a stored procedure only when a particular table already exists, using the following code: SET SERVEROUTPUT ON DECLARE v_row_count INTEGER := 0; e_wrong_db EXCEPTION; BEGIN S...
Categories: DBA Blogs

Is there a self-reference to my PL/SQL program's name?

Tom Kyte - Fri, 2017-04-21 03:06
Hi Tom, Is there a PL/SQL variable equivalent of the $0 variable in Unix. Within a procedure/function if I want to know the name of the program that I am running, can I get it from a system variable. I plan to write into a log table and I want to pa...
Categories: DBA Blogs

Impdp

Tom Kyte - Thu, 2017-04-20 08:46
Hi team, I have imported the database schema from one db to another db with same schemas details and it is completed successfully but when i comparing the objects it is showing difference So, can you pls let me know in impdp some specific object...
Categories: DBA Blogs

SYS indexes

Tom Kyte - Thu, 2017-04-20 08:46
I have some indexes that starts with SYS I am not able to see them in user_indexes but SQL developer explorer shows them. Any idea?
Categories: DBA Blogs

plsql developer - delete memory record

Tom Kyte - Thu, 2017-04-20 08:46
can we delete a record from cursor memory with out effecting main table in plsql
Categories: DBA Blogs

Automatic Memory Management

Tom Kyte - Thu, 2017-04-20 08:46
Hi Tom, I am trying to understand For Automatic Memory Management in 11g.We have MEMORY_TARGET parameter. 1)Can you please explain how MEMORY_TARGET considers SGA_MAZ_SIZE if auto shared memory management is enabled. If i keep sga_targ...
Categories: DBA Blogs

Is it possible for a single session to fully utilize IO?

Tom Kyte - Thu, 2017-04-20 08:46
Dear Tom, We have an Oracle 12c database used mainly as a data warehouse for scientific data. The database has a few very large tables (>1TB) and they are usually accessed via a full table scan. The database is mostly idle having no more than one ...
Categories: DBA Blogs

Predicate evaluation order

Tom Kyte - Wed, 2017-04-19 14:26
I've known for a while that the order of predicate evaluation in a query is non deterministic, as posted by Tom here (https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:696647495510). I even wrote my own blog about it a while back (...
Categories: DBA Blogs

"accessible by" clause in 12.2

Tom Kyte - Wed, 2017-04-19 14:26
Team, Reading through the "accessible by" clause enhancements in 12.2 <u>http://docs.oracle.com/database/122/LNPLS/release-changes.htm#GUID-85A17D6B-4E7A-49D9-B5AC-B0D69390B449</u> <quote> Starting with Oracle Database 12c release 2 (12.2),...
Categories: DBA Blogs

Change font color in SQL*Plus

Tom Kyte - Wed, 2017-04-19 14:26
Hi Tom, I'm pretty sure that recently I stumbled upon an article on some Oracle blog, demonstrating how to change the color of fonts in SQL*plus. (No HTML etc., just the (server)output in SQL*Plus) Unfortunatly I didn't bookmark it, can't find ...
Categories: DBA Blogs

Huge Pages use for improvement of performance

Tom Kyte - Wed, 2017-04-19 14:26
hello, i would like to know the use of Huge pages in 12c for performance improvements. we got suggestion for implementing the Huge pages for performance improvements. could you advice on this?? thanks
Categories: DBA Blogs

SYS_REF_CURSOR AND REF CURSOR

Tom Kyte - Wed, 2017-04-19 14:26
Hi, When i learning about Cursor Variable (Ref Cursor) on One Website I found this Code. <code> CREATE OR REPLACE FUNCTION f RETURN SYS_REFCURSOR AS c SYS_REFCURSOR; BEGIN OPEN c FOR select * from dual; RETURN c; END; / set se...
Categories: DBA Blogs

import metadata with segment attributes deffered

Tom Kyte - Wed, 2017-04-19 14:26
Hi Tom, While doing metadata import of a schema using impdp its taking too much of space for creating empty partitions. Initialization parameter deferred_segment_creation is set to true still the partitions are taking space. Is there any way to im...
Categories: DBA Blogs

How does PIVOT work?

Tom Kyte - Wed, 2017-04-19 14:26
Hi, I tried to learn PIVOT concept to Pivot the Rows to Columns, but i could not understand how it works internally and how it identify which rows should get turn into Columns. So would you Explain How it Makes Rows as Columns.
Categories: DBA Blogs

Process to add temp tablesoaces in a Data Guard environment

Tom Kyte - Wed, 2017-04-19 14:26
We have a physical Data Guard environment. When checking the standby, a new temporary tablesoaces was not added to the standby (but is on the primary). I understand why it's not being automatically created on the standby. However, what's the best ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs