DBA Blogs

How do I find out what username the database is passing through when connecting using Windows Authentication?

Tom Kyte - Wed, 2017-06-21 04:06
Hi, I'm having trouble using Windows authentication with my database accounts. I have a front-end application that creates a user account based on what their OS username is, OPS$Username, disregarding the domain name. Logging into the application ...
Categories: DBA Blogs

DBMS_JOB Poll Interval

Tom Kyte - Wed, 2017-06-21 04:06
We recently upgraded databases from 11.2.0.4 to 12.1.0.2. With that upgrade, we noticed that after submitted jobs via dbms_job, the procedures can take anywhere between 5-30 seconds in order to actually start to execute after committing. A sample scr...
Categories: DBA Blogs

How to exchange index partition, if there is such a thing

Tom Kyte - Wed, 2017-06-21 04:06
I have a global partition on an INDEX as below (the table was partitioned on different keys) Fiscal year, period_cd, and 2 more keys as shown <code>CREATE INDEX kr.ufy_idx_px ON kr.LD_LDGR_ENTR_T ( u_fy, u_prd_cd, c_cd, obj_cd) GLOBAL PAR...
Categories: DBA Blogs

12c MultiTenant Posts -- 4 : Relocate Individual PDB using RMAN

Hemant K Chitale - Tue, 2017-06-20 22:29
Given the current location of a PDB :

SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7
f8go_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7
f8hf_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dk
j7f8hg_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbc
p0wz_.dbf

FILE_NAME
--------------------------------------------------------------------------------


SQL>


I can use RMAN to relocate it. First I take an Image Copy Backup to the new location

RMAN> backup as copy pluggable database newpdb format '/u03/oradata/NEWPDB/%U';

Starting backup at 20-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2 tag=TAG20170620T231338 RECID=4 STAMP=947200428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di tag=TAG20170620T231338 RECID=5 STAMP=947200441
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1 tag=TAG20170620T231338 RECID=6 STAMP=947200451
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4 tag=TAG20170620T231338 RECID=7 STAMP=947200454
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 20-JUN-17

Starting Control File and SPFILE Autobackup at 20-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_20/o1_mf_s_947200455_dnms48pp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-JUN-17

RMAN>


Then I switch the database file pointers to the new location.

RMAN> alter pluggable database newpdb close;

Statement processed

RMAN> switch pluggable database newpdb to copy;

datafile 16 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di"
datafile 17 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2"
datafile 18 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1"
datafile 19 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4"

RMAN> recover pluggable database newpdb;

Starting recover at 20-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 20-JUN-17

RMAN> alter pluggable database newpdb open;

Statement processed

RMAN>


I can now verify the new location for the database files.

 
SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

SQL>


The datafiles at the old location still remain and can be manually deleted later as they are no longer part of the database.  Note that those datafiles are still registered by RMAN as COPY

RMAN> list copy of pluggable database newpdb;

using target database control file instead of recovery catalog
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
8 16 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
Container ID: 4, PDB Name: NEWPDB

9 17 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
Container ID: 4, PDB Name: NEWPDB

10 18 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
Container ID: 4, PDB Name: NEWPDB

11 19 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
Container ID: 4, PDB Name: NEWPDB


RMAN>


If I manually delete the old location files, I'd need to also delete them from the RMAN Registry.  Alternatively, I can directly delete them from RMAN.

RMAN> delete copy of pluggable database newpdb;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=278 device type=DISK
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
8 16 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
Container ID: 4, PDB Name: NEWPDB

9 17 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
Container ID: 4, PDB Name: NEWPDB

10 18 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
Container ID: 4, PDB Name: NEWPDB

11 19 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
Container ID: 4, PDB Name: NEWPDB


Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf RECID=8 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf RECID=9 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf RECID=10 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf RECID=11 STAMP=947200522
Deleted 4 objects


RMAN>


I still need to relocate the TEMP Tablespace Tempfile.

SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_temp_dkj7f8
hg_.dbf


SQL> alter tablespace temp add tempfile '/u03/oradata/NEWPDB/temp01.dbf' size 100M;

Tablespace altered.

SQL>
SQL> alter tablespace temp drop tempfile '/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_temp_dkj7f8hg_.dbf';

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u03/oradata/NEWPDB/temp01.dbf

SQL>


So, I used the same Image Copy method we'd use in 11g databases to relocate a 12c Pluggable Database when within the same server and storage.

To verify that I can access the PDB in the new location :

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
OBJ_LIST
HKC_STORE_FILE
T

SQL> insert into obj_list select * from obj_list;

72641 rows created.

SQL> commit;

Commit complete.

SQL>


.
.

Categories: DBA Blogs

How to update a materialized view directly

Tom Kyte - Tue, 2017-06-20 09:46
Hi Can we update data in Materialized view directly using update statement. If yes,will that updated to table as well. What if there are more than one table in view? Please help me on this.
Categories: DBA Blogs

Oracle Text ctxrule - MATCHES: special word work and not work?

Tom Kyte - Tue, 2017-06-20 09:46
Hi all, I use CTXRULE to classify some text, but AB&B work and AT&T not work. Please support me on this: Here is my search text <code> SQL> select text from test_lexer_special; TEXT -------------------- AB&B {AT&T} AT&T SQL></code> ...
Categories: DBA Blogs

Fast Refreshable Materialized Views - Direct Path Logs on the Master Table

Tom Kyte - Tue, 2017-06-20 09:46
Hello Masters :-), I read the documentation about fast refreshable materialized views. It states: "The changes for conventional DML changes are stored in the materialized view log associated with the master table. The changes for direct-path IN...
Categories: DBA Blogs

Populating sequence for tab delimited input data using SQL*Loader

Tom Kyte - Tue, 2017-06-20 09:46
Hi Tom I have an input file that contains data that is tab delimited containing info like name, type, date, result, value as separate rows. The fields are not enclosed in quotes or anything else. I need to populate sequence for the data while load...
Categories: DBA Blogs

Unload Javascript web page contents to a text file to load in database table

Tom Kyte - Tue, 2017-06-20 09:46
Hello Experts, I have a requirement wherein I need to unload/export web page contents whose data is populated by javascript to a text/csv file; which in turn will be be loaded in the database table. I am not sure if utl_http will get the job d...
Categories: DBA Blogs

Basic stored procedures with Oracle

Tom Kyte - Tue, 2017-06-20 09:46
1. Use table [Employee] Write a stored procedure named Get_Employee_Detail which will accept 2 parameters. @col_name varchar(100) @col_value varchar(500) col_name parameter can accept any column name from the table and col_val...
Categories: DBA Blogs

enq: FB - contention

Tom Kyte - Tue, 2017-06-20 09:46
Hi, We are getting enq: FB - contention for some client and its because of Insert statement. We have Very few Oracle document and SR's created to this can we have an idea on why we are getting this and way to find and fix the issue ( possible w...
Categories: DBA Blogs

Getting SQL ids for a a sqltext

Tom Kyte - Mon, 2017-06-19 15:26
Hello Tom & Team, Can you please provide to get the list of sql ids along with elasped times and other metrics for a given sql statment. Once obtained, can i force oracle to use a particular sql id which was performing better ? Regards, Manjun...
Categories: DBA Blogs

SQL - Get Date Ranges for contiguous records

Tom Kyte - Mon, 2017-06-19 15:26
Hi Tom, I have been on your site numerous times and learnt a lot from your opinions and solutions. I am looking for SQL that displays in chronological order in which country "Tom" lived. Below are my details: <code>--create table. create ...
Categories: DBA Blogs

SQL Loader- Double Qutoes...

Tom Kyte - Mon, 2017-06-19 15:26
Hi, I have data in the below format,how to load its without loosing double quite... all columns enclosed in "" and separated by ~ , also last column has values with double quote and need to load with quote "Jobid"~"Jobname"~"Status"~"Comment...
Categories: DBA Blogs

best way to derive deltas when there is nothing set up on source

Tom Kyte - Mon, 2017-06-19 15:26
We are looking at a gradual migration of a legacy system running on 11gR1. We do not have control over the source system (although their DBAs are willing to accommodate <i>small</i> requests we might have). The data model is being changed drastical...
Categories: DBA Blogs

More Fine-Grained 'ALTER USER' Privilege

Tom Kyte - Mon, 2017-06-19 15:26
I am currently looking for a more fine-grained approach to the user management within an Oracle 11g Release 2 (soon to be 12.1.0.2) Database: There is an idea to give some users the permission to manage some aspects of a user account such as: -...
Categories: DBA Blogs

Oracle 12c Index unusable after partition drop

Tom Kyte - Mon, 2017-06-19 15:26
In Oracle 11g if global index maintained during execution of the DROP partition command then command could take hours to complete. Oracle 12c said DROP partition command executes immediately and index will remain usable. I had database table in Or...
Categories: DBA Blogs

Steps for Moving ASM Disk from FRA to DATA

Pakistan's First Oracle Blog - Sun, 2017-06-18 21:11
Due to some unexpected data load, the space in DATA diskgroup became critically low on one of the production systems during middle of night on the weekend. There was no time to get a new disk and we needed the space to make room for new data load scheduled to be run after 3 hours.

Looked at the tablespaces space in DATA diskgroup and there wasn't much hope in terms of moving or shrinking or deleting anything. Also the upcoming data load was direct path load which always writes above the high water mark in segments, so shrinking wasn't of much help.

Looked at the FRA diskgroup and found out that there was plenty of space there, so I decided to rob Peter to pay Paul. The plan was to remove a disk from FRA diskgroup and add it to DATA. This all was done online and these were general steps:

Steps for Moving ASM Disk from FRA to DATA :

1) Remove Disk from FRA diskgroup

SQL> alter diskgroup FRA drop disk FRA_06;

Diskgroup altered.

2) Wait for Rebalance to finish

SQL> SELECT group_number, operation, state, power, est_minutes FROM v$asm_operation;

3) Add disk to the DATA diskgroup

alter diskgroup DATA add disk '/dev/myasm/superdb_fra_06' name DATA_06 rebalance power 8;

4) Wait for Rebalance to finish

SQL> SELECT group_number, operation, state, power, est_minutes FROM v$asm_operation;

This provided a much needed breather for the weekend and data load ran successfully. We will be making sure that we provision more disks to the DATA diskgroup and return the FRA disk to FRA with thanks.


Categories: DBA Blogs

Collection Function SET

Tom Kyte - Sun, 2017-06-18 02:46
Hi, My question is related with how the collection function SET works. I have a collection that contains duplicated values (such as the example provided) and I want to have as an output, the distinct list of values from that collection. The SET...
Categories: DBA Blogs

Updating Table Using with Clause or Analytical Function

Tom Kyte - Sun, 2017-06-18 02:46
Hi, Good Day ! From last few days I came across many situations where I need to update a table based on some analytical function output. Since, my table don't have any primary key I am having limitation on update using SQL and hence I left with...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs