DBA Blogs

Admin vs Design

Tom Kyte - Tue, 2017-11-14 03:06
First of all why people or do not understand or u people force . DBA primary role and still they r rude ( back up , recovery ,upgrade, monitoring ) a system. It is the most crucial part as Data Loss , recovery can not not be priced. It is a 24*7...
Categories: DBA Blogs

Object info w.r.t redo

Tom Kyte - Tue, 2017-11-14 03:06
Dear Sir, How can I found which object generate more redo in database. Thanks Pradeep
Categories: DBA Blogs

DBMS facilities

Tom Kyte - Tue, 2017-11-14 03:06
I am not able to understand this question. What are they asking in the question? Thank You!: "Starting at the client invocation describe the mechanics of the DBMS through a SQL SELECT all the way through the response to the client. Name all faci...
Categories: DBA Blogs

difference between sql tuning advisor and sql access advisor

Tom Kyte - Tue, 2017-11-14 03:06
After reading both "Sql Tuning advisor" and "sql access advisor" , getting little confused on -- what is exact difference behind these advisors -- which situation one can use one advisor over another Please provide your guidance. thx A...
Categories: DBA Blogs

Table with date column datatype (Storing datetime) causing issue in fetching result and when use with to_date and to_char causing performance issue.

Tom Kyte - Tue, 2017-11-14 03:06
Hello Tom, Good Morning. This is my first time posting question, I always got helped for my most of problems from your post. Need your help in understanding what will be good way with respect of performance. I have one table with date dat...
Categories: DBA Blogs

Partner Webcast – Setting up API strategy with Oracle API Platform Cloud Service

APIs are at the center of the digital economy as they provide the software plumbing between ever more distributed modes of digital interaction.  Several business models are associated with...

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

Expdp exclude table/exclude schema

Learn oracle 12c database management - Mon, 2017-11-13 10:56

expdp export exclude syntax:Here is a simple example on how to use exclude in your export cmds.

----------------------------------------------
Exclude Tables
----------------------------------------------

Using parfile:

First create a parfile with details.
$ vi impdp_full.par

--add the details to parfile
directory=DPUMP logfile=SCOTT_EXP.log dumpfile=SCOTT_%U.dmp parallel=4  EXCLUDE=TABLE:"IN ('TABLE1','TABLE2','TABLE3')

Then execute using parfile:
expdp SCOTT/XXXXXX parfile=impdp_full.par

on cmdline:
expdp SCOTT/XXXXX directory=DPUMP dumpfile=SCOTT_%U.dmp logfile=SCOTT.log schemas=SCOTT  parallel=6 EXCLUDE=TABLE:\"IN (\'TABLE1\', \'TABLE2\')\"
  ----------------------------------------------
Exclude Schemas
----------------------------------------------

First create a parfile with details.

$ vi expdp_full.par
--add the details to parfile
directory=DPUMP FULL=Y dumpfile=FULLDB_%U.dmp logfile=FULL.log   parallel=6 EXCLUDE=SCHEMA:"IN ('SCHEMA1','SCHEMA2','SCHEMA3')

Then execute using parfile:
expdp SYSTEM/XXXXXX parfile=expdp_full.par

on cmdline:
expdp SYSTEM/XXXXX directory=DPUMP dumpfile=FULLDB_%U.dmp logfile=FULL.log FULL=Y  parallel=6 EXCLUDE=TABLE:\"IN (\'SCHEMA1\', \'SCHEMA2\')\"
Categories: DBA Blogs

is alter table drop constraint drop index syntactically valid?

Tom Kyte - Mon, 2017-11-13 08:46
Hi Tom, I'm dropping unique constraint from a table and need to drop the unique index too. Yes there's lot written about this topic and what is not clear to me seems to be just documentation issue: I found advices to use syntax <code>alter tabl...
Categories: DBA Blogs

oracle external table issue

Tom Kyte - Mon, 2017-11-13 08:46
Hi team, I have a txt file which has data delimited by "TAB SPACE" and i am trying to load the data using the oet table.Here is the script below which i used to load the data . Unfortunately the data is not loading . Can any one say how to load th...
Categories: DBA Blogs

Persuade customer to use SQLT

Tom Kyte - Mon, 2017-11-13 08:46
Hi Tom, While doing sql query tuning I came across SQLT Tool and I found it very useful. But there's one problem. Our clients (esp. client's DBAs) are not ready to allow me to use it on their production environment. I told them that it won't have ...
Categories: DBA Blogs

Last login

Tom Kyte - Mon, 2017-11-13 08:46
We are running versions 11.2.0.4 12.1 12.2. I am looking for a generic solution to capture last login date. I think the best solution would be an initial load and then maintain the information via an after logon trigger, which will contain a merg...
Categories: DBA Blogs

INACTIVE session is blocking active session

Tom Kyte - Mon, 2017-11-13 08:46
DBA is throwing information as follows 06112017:11:00:09 WELOPP@n1pv97/46581 (Session=('300,19867')Status=INACTIVE sqlid=>) blocking WELOPP@n1pv97/45876 (Session=('1803,10683') Status=ACTIVE sqlid=fp5x2quh0zpqk) f...
Categories: DBA Blogs

For Joins in Query Performance optimization

Tom Kyte - Sun, 2017-11-12 14:26
I have a query with 4 For loops puting data into temp table an then that temp table TEMPTBL_NUMBER_SEARCH is called to execute the operations in a select clause. So the problem with the 4 for loop is making it slow to 15-20 mins. Its all indside a pr...
Categories: DBA Blogs

export issue

Tom Kyte - Sun, 2017-11-12 14:26
Hi team, We are taking daily export of schema with expdp But for a few days we are continuously getting error saying - snapshot too Old. Table is a partitioned table weekly base. And the script which we are using for expdp is - expdp us...
Categories: DBA Blogs

Does the context switch account for the recursive calls

Tom Kyte - Sun, 2017-11-12 14:26
Hi Tom, Here is what i did trying to understand the enhancements of 12c. Here i was trying to understand the enhancements of WITH clause. I have created the table and compiled the below function. <code> CREATE TABLE lnd_numbers AS SELECT ...
Categories: DBA Blogs

In sql how can I update a value , and then reuse the updated value and re-update it

Tom Kyte - Sun, 2017-11-12 14:26
Hi Gurus I need to write in SQL something which previously was done in PL/SQL if possible. I have a Invoice Line Description e.g. 'ABC Mon Tue' for which I need to translate certain words. I also have a lookup(fnd_lookups) which stores the...
Categories: DBA Blogs

Versioning Data Model

Tom Kyte - Sat, 2017-11-11 01:46
Hi AskTom team, I'd like your ideas about the data model design and/or Oracle features that I could take advantage of to achieve the design goals described below. <u>Background:</u> I'm in the early stages of designing a data model for a bra...
Categories: DBA Blogs

Union all query missing lines

Tom Kyte - Sat, 2017-11-11 01:46
Hello Tom and Tom, Linked live sql shows a condensed and "moved-to-dual" query we are using with a far resemblance on our database. It's a couple of nested "union all" statements, where we would expect the outermost union (UNION2) to deliver the u...
Categories: DBA Blogs

Grant select on a View with grant option does not work

Tom Kyte - Sat, 2017-11-11 01:46
Hi, I have Schema_1 that owns table_1, table_2, table_3. Schema_1 creates View_1 using table_1, Schema_1 Creates View_2 using table_2, Schema_1 Creates View_3 using table_3. Schema_2 Creates View_4 using View_1, View_2 and View_3. Then ...
Categories: DBA Blogs

Identify patterns and create groups

Tom Kyte - Fri, 2017-11-10 07:35
I have data that looks like this: <code>create table t (a varchar2(30), b date); insert into t values (NULL,TO_DATE('2003/05/03 16:02:44', 'yyyy/mm/dd hh24:mi:ss')); insert into t values (NULL,TO_DATE('2003/05/03 17:02:44', 'yyyy/mm/dd hh24:mi...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs