DBA Blogs

RMAN and Archivelog

Tom Kyte - Fri, 2016-07-22 14:46
Hi, Maybe this question was already answered somewhere but there so many question that I can not find the solution on this site. It is rather a simple one (I hope not a stupid one). Strictly speaking, when I make a "backup database" with Rman, do...
Categories: DBA Blogs

Restricting datafile addition to a filesystem (ASM)

Tom Kyte - Fri, 2016-07-22 14:46
Hi Tom The normal method of adding datafiles to a tablespace is to login to the database as "sqlplus / as sysdba" and then executing the "alter tablespace add datafile.." command. Is there a way to prevent / prompt the DBAs from accidentally adding ...
Categories: DBA Blogs

User defined Year-Month data type

Tom Kyte - Fri, 2016-07-22 14:46
Hi Answers Team. I keep seeing my maintenance / design / development teams abusing dates everywhere. One example is the use of YYYYMM as a way to extract a month from some piece of data what starts out as a date. 1. database design will cre...
Categories: DBA Blogs

Links for 2016-07-21 [del.icio.us]

Categories: DBA Blogs

Selectively purge the SQLAREA... terrible idea?

Tom Kyte - Thu, 2016-07-21 20:26
Hi "Tom"- We have an outside vendor's product hitting our database and never using bind variables. Every statement is unique. We can't change it, we're stuck with it. This outside program is a very high volume. These constantly unique statem...
Categories: DBA Blogs

parallel statement queueing - how to synchronize sessions?

Tom Kyte - Thu, 2016-07-21 20:26
Hi Tom-Team, I want to process statements in parallel by setting "parallel_degree_policy=auto". They are a mixture of DML and DDL statements. In this context, the order in which statements execute, is undefined. I know that I can amend that (parti...
Categories: DBA Blogs

Identifying ASM "orphans"

Tom Kyte - Thu, 2016-07-21 20:26
We've been migrating several DBs from their old Oracle 10G platforms to our new Oracle SuperClusters (SC). The older DBs were flatfile based, but the new SC-based DBs are using ASM. Several of the migrations initially failed, and some of the older DB...
Categories: DBA Blogs

PMON

Tom Kyte - Thu, 2016-07-21 20:26
what is PMON?
Categories: DBA Blogs

DBMS_SCHEDULER jobs - How Job_Priority attribute works?

Tom Kyte - Thu, 2016-07-21 20:26
Hi, Request your help to understand working of Job_Priority attribute that can be set for jobs created via DBMS_SCHEDULER. From my tests it seems that the attribute behavior is somehow linked to the number of jobs scheduled. However, looks like...
Categories: DBA Blogs

database corrupted - ORA-09925: Unable to create audit trail file

Tom Kyte - Thu, 2016-07-21 20:26
Actually when my database is running at that time I change my parameter file.after changing I shutdown my database.And again I tried to connect to my database as sysdba. But I cannnot connect to that database.It shows some errors. Then what is the s...
Categories: DBA Blogs

Design question

Tom Kyte - Thu, 2016-07-21 20:26
Hello AskTom Team, Hope you could help me with this. Business case: 1) We have system of records which we provide to customers. We refresh this data on demand (could be push or pull) on customer's on-premise instance 2) Cusotmer can overrid...
Categories: DBA Blogs

Implicit vs explicit cursors

Tom Kyte - Thu, 2016-07-21 20:26
Hi sir how are you....here i got a small doubt.i hope u can reply me.The doubt was....select * from tb...when i was select the data it gave me total records(100),so in this case what we will call it.I mean it is an implicit cursor or an explicit curs...
Categories: DBA Blogs

TRIM function in 12c

Tom Kyte - Thu, 2016-07-21 20:26
Hello, Tom! I've a lack of undestanding of 'trim' fuction's IN parameters. It's not explicitly said in the docs, that only a single character can be used as trim_character. However, an attempt to use a syntax like this: SELECT TRIM(BOTH 'A...
Categories: DBA Blogs

Using special characters in ORACLE DB passwords

Learn DB Concepts with me... - Thu, 2016-07-21 11:37

Using special characters in ORACLE DB passwords  
Using special characters in ORACLE DB passwords. Most times we restrict our selves from using complicated special characters as we think that we can't use them in passwords, but in oracle DB world we are allowed to use most special characters as passwords. See this simple example to see how to use special char's in passwords and login.




To log into DB using cmdline we need to use single quote ''. else oracle some times doesn't recognize the password. See below example :

I created a user with password using special characters.

SQL> alter user C##atest identified by "atest113..";

User C##ATEST altered.

[oracle@Linux03 admin]$ sqlplus C##atest/atest113..

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:39:56 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Solution to this is to use single quote:

[oracle@Linux03 admin]$ sqlplus C##atest/"atest113$$"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:41:44 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C

press ctrl+c to escape from this line


[oracle@Linux03 admin]$ sqlplus C##atest/'atest113$$&&'

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 16:43:20 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Jul 20 2016 16:42:54 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "C##ATEST"

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@Linux03 admin]$ sqlplus C##atest/atest113$$&&

Categories: DBA Blogs

IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE

Learn oracle 12c database management - Thu, 2016-07-21 11:33
 IMPDP  TABLE_EXISTS_ACTION PARAMETER EXPLAINED

 Data Pump IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE
In conventional import utility (IMP) we have ignore =y option which will ignore the error when the object is already exist with the same name.

When it comes to the data pump there is one enhanced option of ignore=y which is called TABLE_EXISTS_ACTION. The values for this parameter give 4 different options to handle the existing table and data.

$ impdp help=y

    TABLE_EXISTS_ACTION
    Action to take if imported object already exists.
    Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

    SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in conventional import utility.

    APPEND: This option appends the data from the data dump. The extra rows in the dump will be appended to the table and the existing data remains unchanged.

    TRUNCATE: This option truncate the exiting rows in the table and insert the rows from the dump

    REPLACE: This option drop the current table and create the table as it is in the dump file. Both SKIP and REPLACE options are not valid if you set the  CONTENT=DATA_ONLY for the impdp.

   
Method to Import only rows does not exist in the target table
See some examples here.

In this example lets use abc table in my schema (ATOORPU)

SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12

I took the data pump dump export EXPDP of employee table.

oracle@orcl: $ expdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc

Export: Release 11.2.0.1.0 - Production on Fri Oct 24 09:25:02 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ATOORPU"."SYS_EXPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ATOORPU"."ABC"                             5.921 KB       6 rows
Master table "ATOORPU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATOORPU.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/abc.dmp
Job "ATOORPU"."SYS_EXPORT_TABLE_01" successfully completed at 09:25:36


oracle@qpdbdev201:[/u01/app/oracle/dpump] $ sqlplus atoorpu

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 24 09:25:57 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


   ------ TABLE_EXISTS_ACTION=SKIP  ------

In this example I want to use table_exists_action=skip, where I want to skip the table data in my import, if a similar table exists.

oracle@orcl: $ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:32:32 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "ATOORPU"."ABC" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:32:42


    ------     TABLE_EXISTS_ACTION=APPEND    ------

I have deleted and inserted 4 new rows into employee table. So as of now the rows the dump and table are different and I am going to import the dump with APPEND option.

SQL> delete from employee;

4 rows deleted.

SQL> insert into abc (select * from abc_bak);

4 rows created.

SQL> commit;
SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
ARVd                                   70 20-DEC-12
ARVIND2                                69 20-DEC-12
ATOORPUe                               64 19-DEC-12
BIf                                    63 19-DEC-12

$  impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:37:34 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "ATOORPU"."ABC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:37:42


Now 4 more rows appended to the table. Lets verify that

SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
ARVd                                   70 20-DEC-12
ARVIND2                                69 20-DEC-12
ATOORPUe                               64 19-DEC-12
BIf                                    63 19-DEC-12
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12

8 rows selected.

        ------ TABLE_EXISTS_ACTION=TRUNCATE     ------ 

    Now let’s try with table_exists_action=truncate option. In truncate option it will truncate the content of the existing table and insert the rows from the dump. Currently my abc table has 8 rows which we inserted last insert.

$   impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:39:39 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "ATOORPU"."ABC" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:39:46


SQL >  select * from abc


USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12


        ------ TABLE_EXISTS_ACTION=REPLACE    ------


This option drop the current table in the database and the import recreate the new table as in the dumpfile.

$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:41:59 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at 09:42:07


Now if you check the last_ddl_time for the table it would be the same as the import time.


SQL> select OBJECT_NAME, to_char(LAST_DDL_TIME,'dd-mm-yyyy hh:mi:ss') created from dba_objects where OBJECT_NAME='ABC' and owner='ATOORPU';


OBJECT_NAME                    CREATED          
--------------------------             -------------------
ABC                                        24-10-2014 09:42:06

Categories: DBA Blogs

Keep big integers in Oracle RDBMS

Tom Kyte - Thu, 2016-07-21 02:06
Oracle has several datatypes that can keep numbers. LONGINTEGER can keep the biggest whole number - number in the range of (-2**63) to (2**63)-1 I need to keep bigger numbers so I can't use it. Keeping the numbers in decimals may affect precision. ...
Categories: DBA Blogs

Unable to open file even though given correct file path wile reading certificate from wallet

Tom Kyte - Thu, 2016-07-21 02:06
Hi Tom , We have requirement to communicate payment gateway through https from plsql. so we used SSL through wallets. But in 11.2.0.1.0 we are unable to read file from wallet even though correct file path provided. I gone through below link al...
Categories: DBA Blogs

Life cycle of a query

Tom Kyte - Thu, 2016-07-21 02:06
Can you please explain the full life cycle of a query like "select * from employees" ..Parsing and memory utilization..?
Categories: DBA Blogs

trigger execution order

Tom Kyte - Thu, 2016-07-21 02:06
Sir, I have 5 same functionality triggers on a single table, let's suppose t1,t2,t3,t4,t5. I want that trigger t3 should execute at first. Is this possible? If yes then how? Please give some explanation also. Thanks & Regards
Categories: DBA Blogs

Database restoration

Tom Kyte - Thu, 2016-07-21 02:06
Hi Tom, This is Gurupi from India. I am a newbie to Oracle but have fair working knowledge in Linux OS. One of my clients need to restore their Oracle 10g databases on their old IBM server running CentOS 5. Their server drives crashed and they ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs