DBA Blogs

Is it possible to use occi client of oracle11 to fetch/write extended data types that were added in oracle 12c?

Tom Kyte - Wed, 2016-08-24 10:26
Hi, I use oracle11 client library (libocci.so.11.1) to work with extended data types, like RAW(32000), VARCHAR(32000), etc. This is a new feature of Oracle 12c. When I try to fetch RAW data, I get exception: ORA-01461: can bind a LONG value on...
Categories: DBA Blogs

Partition pruning/elimination -reg..

Tom Kyte - Wed, 2016-08-24 10:26
Dear Tom, Sorry for the delay. 1 CREATE TABLE emp (no number, name VARCHAR2(10) , 2 PRIMARY KEY (no, name)) 3 partition by hash(no) 4 ( 5 PARTITION PART1 TABLESPACE "TS1" , 6 PARTITION PART2 TABLESPACE "TS2" 7* ) ...
Categories: DBA Blogs

Links for 2016-08-23 [del.icio.us]

Categories: DBA Blogs

Update using subquery with group by

Tom Kyte - Tue, 2016-08-23 16:06
Hi Tom I have a table with 18 million rows. I need to do an once-off update to fix a data issue. CREATE TABLE TESTLARGE(CODE number, STATE varchar2(5), SDATE date, flag char(1)); This table does not have any primary key enforced. I have t...
Categories: DBA Blogs

problem with a few chjaracters in JAVA / JDBC that cant be converted from source 9i solaris DB to a 12c linux system

Tom Kyte - Tue, 2016-08-23 16:06
Hello Tom, 1.what is the right superset for WE8DEC?? 2. what OS charset (say I would it set in my java program) should by set if I want to read varchar fields and write them in a new varchar field? Apart from this they received "ORA-29345:...
Categories: DBA Blogs

DBMS_STATS.GATHER_TABLE_STATS Gives wrong row count (NUM_ROWS column in user_tables)

Tom Kyte - Tue, 2016-08-23 16:06
Tom, I am a big fan of yours and you are awesome. Here is something I observed today. I always thought analyzing table will populate number of rows in user_tables.num_rows column. I have a table with 204,913 records. When I do a select count(1) fr...
Categories: DBA Blogs

dynamic pivoting

Tom Kyte - Tue, 2016-08-23 16:06
Hello, I have a table something like this: book name| chapter| sub-chapter | no. of pages| book1 chap1 sub_book1_a1 230 book1 chap1 sub_book1_a2 110 book1 chap1 sub_book1_a3 220 book1 chap2 su...
Categories: DBA Blogs

ORA-01410: invalid ROWID

Tom Kyte - Tue, 2016-08-23 16:06
Declare Cursor C1 is select * from emp; Cursor C2(P_deptno dept.deptno%type) is select deptno,dname,loc from dept where deptno=P_deptno for update of dname,loc; dept_Rec dept%rowtype; i emp%rowtype; Begin Open C1; loop Fetch C1 into i; Exit...
Categories: DBA Blogs

alias to a non named column

Tom Kyte - Tue, 2016-08-23 16:06
Hi Chris and Connor, How can I give an alias a non named column resulting from a subquery? the case select * from ( select 6*7 from dual ); I know it's easy to alias it in the subquery like that select result from ( select 6*7 as ...
Categories: DBA Blogs

Oracle Materialized View Fast refresh on remote database

Tom Kyte - Tue, 2016-08-23 16:06
GM Tom, In my current db implementation, we do not have any data/tables in our db and gets all data from two other data sources. To do so we have created Materialized Views using dblinks to get data from these two other db sources. We use to COMPL...
Categories: DBA Blogs

Force Query to use a hash plan

Tom Kyte - Tue, 2016-08-23 16:06
Hi Tom, I have SQL HC for a SQL ID which shows the query have two Hash Plans one is Optimal and another which elapse a lot. And my query is taking long time to run which means that it is using the worst plan. I want to force my query to use the O...
Categories: DBA Blogs

a sql query which pulls all the record with the same first name and email and if one row has email and other has no email but i have same phone number in cluster

Tom Kyte - Mon, 2016-08-22 21:46
a sql query which pulls all the record if one cluster has same first name ,last name and one (i.e 22 )has email and other has no email but i have same Phone number There are about million of records,i have to fetch all the records according to the...
Categories: DBA Blogs

Slow select after bulk insert-remove

Tom Kyte - Mon, 2016-08-22 21:46
I have the problem, that I could describe in several steps: 1. Insert 160K into table t_reg_account. Here I use simple insert statement without any hints. I_ACC_PK index on participant_code, account_type, account_value, account_sequence. 2. Execu...
Categories: DBA Blogs

Kindly guide for Oracle DBA self learning methods,

Tom Kyte - Mon, 2016-08-22 21:46
Hi, At present am working as PLSQL developer, I want to Learn DBA but I am not interested in Institute learning,believe I could learn more through self learning. Kindly guide me with the books to learn from scratch to advanced DBA. Thanks! R...
Categories: DBA Blogs

RMAN duplication fails.

Tom Kyte - Mon, 2016-08-22 21:46
Hi I am trying to duplicate database . When I do do that , at the end of the process , I am getting RMAN-06025 . why does it try to apply the non exisiting log imn the auxillary db? Thanks RMAN> run { 2> 3> allocate auxili...
Categories: DBA Blogs

PLSQL Code type native compilation

Tom Kyte - Mon, 2016-08-22 21:46
Hi Tom, what are the pros and cons if I convert my plsql code type to Native? I have package bodies, procedures & functions where most of my code runs. I do have lot of data extractions taken from DB & number of PLSQL programs that run for hours....
Categories: DBA Blogs

Copying data from one environment to other

Tom Kyte - Mon, 2016-08-22 21:46
Hi, what is the best way to copy data from one environment (PRD) to other environment (NON-PRD) in oracle installed on UNIX server? Thanks, DIPU
Categories: DBA Blogs

Uncommited changes in data files?

Tom Kyte - Mon, 2016-08-22 21:46
Hi, While i was studying "Instance Recovery" topic in the "Oracle 11g Concepts" document, i read something something like this; "When a transaction is committed, log writer (LGWR) writes both the remaining redo entries in memory and the transac...
Categories: DBA Blogs

Sequence.nextval not working in sqlldr

Tom Kyte - Sun, 2016-08-21 09:06
sequence.nextval not working when i am using in sqlldr , sqlldr loading null value in DB Snapshot of my control file load data append into table Selectiotab WHEN (1:2) <> 'H|' and (1:2) <> 'T|' fields terminated by "|" trailing nullcols ( ...
Categories: DBA Blogs

As a DBA how to verify if private database link is functional

Tom Kyte - Sun, 2016-08-21 09:06
Hi, Being a DBA, how can we verify if private database link created in application schema is working fine? If application schema's password is not known to DBA. We can verify public database link using select * from dual@public_db_link; How pri...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs