DBA Blogs

SQL Loader - Load CSV file, double quoted record, dual double quoted field

Tom Kyte - Thu, 2017-12-14 04:06
Hello, Please tell me how to write the control file to load following data (without quotes) using sqlldr in three separate data fields. We get such a csv file from out client and they can't change the way it generates. This file opens fine in M...
Categories: DBA Blogs

ORA-29471: DBMS_SQL access denied

Tom Kyte - Thu, 2017-12-14 04:06
I have a problem in procedure: procedure .. begin v_c := dbms_sql.open_cursor; v_c2 := dbms_sql.open_cursor; dbms_sql.parse(v_c, v_sql, dbms_sql.native); dbms_sql.parse(v_c2, v_query, dbms_sql.native); v_stmt := dbms_sql.execute(...
Categories: DBA Blogs

Error in Opening KeyStore in Oracle 12c R2

Tom Kyte - Thu, 2017-12-14 04:06
I am trying to create TDE example . I was able to create a keystore ; but when I open the key store , I am getting ORA-28367. What am I missing here ? Thanks very much in advance. <code> SQL> select * from v$version ; BANNER ...
Categories: DBA Blogs

Oracle 12c - SQL query with inline PL/SQL function

Tom Kyte - Thu, 2017-12-14 04:06
I'm playing around with Oracle 12c and trying out the new features. One of the new features is that the WITH clause in a SQL query now allows for a PL/SQL function. I created the following sample query: <code> WITH FUNCTION get_number RE...
Categories: DBA Blogs

How do I determine where the ora_home directory is using SQL or PL/SQL?

Tom Kyte - Thu, 2017-12-14 04:06
I tried the following: <code> DECLARE theresult varchar2(1000); begin dbms_system.get_env('ORACLE_HOME', theresult); dbms_output.put_line('result = ' || theresult); end; </code> but got the following error <code>line 2: ORA-0655...
Categories: DBA Blogs

nls_date_format difference between v$parameter and database_properties

Tom Kyte - Thu, 2017-12-14 04:06
When I query v$parameter, nls_date_format is YYYY-MM-DD When I query database_properties, nls_date_format is DD-MON-RR Why is it different? Could this cause problems?
Categories: DBA Blogs

Both talks accepted for Collaborate 18

Bobby Durrett's DBA Blog - Wed, 2017-12-13 10:35

IOUG accepted both my Toastmasters and Python talks for Collaborate 18. RMOUG also accepted them both so I will be doing these two talks in both February and April. I am a little surprised because I have had talks rejected by IOUG in the past. There are a lot of great speakers competing for speaking slots. This is my first time for RMOUG so I did not know how hard it would be to get a talk accepted. I put both talks in for both conferences not knowing if either would be accepted and both were at both conferences!  So, 2018 will be a busier year than normal for me in terms of speaking at conferences. My last conference was two years ago at Oracle OpenWorld where I spoke about Delphix, a tool that I use with the Oracle database. Next year I’m talking about two things that I feel passionate about. The talks are not about the Oracle database but they are about things I have learned that have helped me in my Oracle database work. They are about how Toastmasters has helped me improve my speaking and leadership skills and about why the Python programming language has become my favorite general purpose scripting tool. I am looking forward to giving the talks. If you are able to attend one of the conferences maybe you could check out one of my talks. Fun.

Bobby

Categories: DBA Blogs

RMAN Parallelism question

Tom Kyte - Wed, 2017-12-13 09:26
Tom, I am reading Oracle? Database Backup and Recovery Advanced User's Guide, 10g Release 2 (10.2), Part Number B14191-02,http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconfg003.htm#sthref521 The document says: 1) "As a...
Categories: DBA Blogs

partitioned index

Tom Kyte - Wed, 2017-12-13 09:26
Hi Tom, please explain the differences between global partitioned index and local partitioned index. Thanks!
Categories: DBA Blogs

Oracle MOOC: Developing Chatbots with Oracle Intelligent Bots

Oracle Intelligent Bots with Oracle Mobile Cloud Services gives you the ability to create an artificially intelligent bot that can converse with your users, to determine their intent and perform...

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

How to verify the parallel execution in a custom User-Defined Aggregate Function

Tom Kyte - Tue, 2017-12-12 15:06
I want to verify the parallel execution in my User-Defined Aggregate Function. So I put some DBMS_OUTPUT inside the code - but it seems not to work correctly... You can reproduce the behaviour by simple create the example user-defined aggregate fu...
Categories: DBA Blogs

How to concatenate string having length greater than 4000 characters

Tom Kyte - Tue, 2017-12-12 15:06
I am using xmlagg function to concatenate records with comma separated values. But i am getting error when columns count is than 300 I getting below error <code>Error starting at line : 8 in command - select rtrim (xmlagg (xmlelement (e...
Categories: DBA Blogs

Shrink partition table

Tom Kyte - Tue, 2017-12-12 15:06
Hi, The shrink table doesn't defragments the partition tables. executed : alter table test_table_1 shrink space and checked with below query wasted space before and after but the values are identical. <code>select table_name,rou...
Categories: DBA Blogs

Imdp xml schema date format issue ORA-01858

Tom Kyte - Tue, 2017-12-12 15:06
Hi, I exported a schema from Oracle 11.2.0.1 and trying to import it in Oracle 12c. My Oracle schema contains xml schema and xmltype columns in a table. My xml fragment is <code><Tag0> <Tag1> <Tag2 Id="10202" date1="2017-11-15T13:36:34.00000...
Categories: DBA Blogs

Removal of Archive Files when using OS to backup offline DB

Tom Kyte - Tue, 2017-12-12 15:06
I have been thrown in at the deep end and given an Oracle DB to look after. I have no prior experience of Oracle so everything I am doing is new and a massive learning curve. The current DB data set is approx 400GB and we are working with the appl...
Categories: DBA Blogs

ORA-00240: control file enqueue held for more than 120 seconds 12c

Pakistan's First Oracle Blog - Mon, 2017-12-11 22:30
Occasionally, In Oracle 12c database, you may get ORA-00240: control file enqueue held for more than 120 seconds  error in the alert log file.


Well, as this error contains the word control file so it looks scary but if you are getting it infrequently and the instance stays up and running then there is no need to worry and this can be ignored as a fleeting glitch.

But if it starts happening too often and worst case if it hangs or crashes the instance then more than likely its  a bug and you need to apply either the latest PSU or any one-off patch available from Oracle support after raising SR.

There have been some occurances where this error occured due to high number of sessions and conflicting with the ulimit of the operating system resulting in hanging of ASM and RDBMS. Some time it could be due to shared pool latch contention as per few MOS documents.

So if its rare, then ignore it and move on with the life as there are other plenty of things to worry about. If its frequent and a show-stopper then by all means raise a SEV-1 SR with Oracle support as soon as possible.
Categories: DBA Blogs

Domain Indexes -- 1 : CONTEXT Indexes

Hemant K Chitale - Sun, 2017-12-10 03:17
A CONTEXT Index is one of a class of Domain Indexes.  It is used to build text-retrieval application.
Instead of a regular B-Tree index that captures the entire text of a VARCHAR2 column as a key, you can build an index that consists of "Tokens", words extracted from a long-ish text in the database column.  Searching the index is based on the CONTAINS operator.

Here is a quick demo in 11.2.0.4 :

First I setup a new user for this demo.  I could have used an existing user but my "HEMANT" user has DBA privileges and I want to demonstrate CONTEXT without such privileges.
Here the key grant is "CTXAPP" that is granted to the user.

SQL> create user ctxuser identified by ctxuser default tablespace users;

User created.

SQL> grant create session, create table to ctxuser;

Grant succeeded.

SQL> grant ctxapp to ctxuser;

Grant succeeded.

SQL> alter user ctxuser quota unlimited on users;

User altered.

SQL>


Next, this user creates the demonstration table and index :

SQL> connect ctxuser/ctxuser
Connected.
SQL> create table my_text_table
2 (id_column number primary key,
3 my_text varchar2(2000));

Table created.

SQL> create index my_text_index
2 on my_text_table(my_text)
3 indextype is ctxsys.context;

Index created.

SQL>
SQL> insert into my_text_table
2 values (1,'This is a long piece of text written by Hemant');

1 row created.

SQL> insert into my_text_table
2 values (2,'Another long text to be captured by the index');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> -- this update to the index would be a regular background job
SQL> exec ctx_ddl.sync_index('MY_TEXT_INDEX');

PL/SQL procedure successfully completed.

SQL>


Note the call to CTX_DDL.SYNC_INDEX.  Unlike a regular B-Tree index, the CONTEXT (Domain) Index is, by default, *not* updated in real-time when DML occurs against the base table.  (Hopefully, in a future post, I may demonstrate real-time updates to a CTXCAT index, different from a CONTEXT Index).
Only remember that this CONTEXT index is not automatically updated.  If new rows are added to the table, they are not visible through the index until a SYNC_INDEX operation is performed.  The SYNC_INDEX can be scheduled as a job with another call to DBMS_JOB or DBMS_SCHEDULER or itself as part of the CREATE INDEX statement.

Now, let me demonstrate usage of the Index with the CONTAINS operator.

SQL> select id_column as id,
2 my_text
3 from my_text_table
4 where contains (my_text, 'written by Hemant') > 0
5 /

ID
----------
MY_TEXT
--------------------------------------------------------------------------------
1
This is a long piece of text written by Hemant


SQL> select my_text
2 from my_text_table
3 where contains (my_text, 'Another long') > 0
4 /

MY_TEXT
--------------------------------------------------------------------------------
Another long text to be captured by the index

SQL>


Yes, the CONTAINS operator is a bit awkward.  It will be some time before you (or your developers) get used to the syntax !

Besides CTX_DDL, there are a number of other packages in the prebuilt CTXSYS schema that are available :
 CTX_CLS
 CTX_DDL
 CTX_DOC
 CTX_OUTPUT
 CTX_QUERY
 CTX_REPORT
 CTX_THES
 CTX_ULEXER

Since I have created a separate database user, I can also demonstrate the additional objects that are created when the INDEXTYPE IS CTXSYS.CONTEXT.

SQL> select object_type, object_name, to_char(created,'DD-MON-RR HH24:MI') Crtd
2 from user_objects
3 order by object_type, object_name
4 /

OBJECT_TYPE OBJECT_NAME CRTD
------------------- ------------------------------ ------------------------
INDEX DR$MY_TEXT_INDEX$X 10-DEC-17 16:48
INDEX DRC$MY_TEXT_INDEX$R 10-DEC-17 16:48
INDEX MY_TEXT_INDEX 10-DEC-17 16:48
INDEX SYS_C0017472 10-DEC-17 16:48
INDEX SYS_IL0000045133C00006$$ 10-DEC-17 16:48
INDEX SYS_IL0000045138C00002$$ 10-DEC-17 16:48
INDEX SYS_IOT_TOP_45136 10-DEC-17 16:48
INDEX SYS_IOT_TOP_45142 10-DEC-17 16:48
LOB SYS_LOB0000045133C00006$$ 10-DEC-17 16:48
LOB SYS_LOB0000045138C00002$$ 10-DEC-17 16:48
TABLE DR$MY_TEXT_INDEX$I 10-DEC-17 16:48
TABLE DR$MY_TEXT_INDEX$K 10-DEC-17 16:48
TABLE DR$MY_TEXT_INDEX$N 10-DEC-17 16:48
TABLE DR$MY_TEXT_INDEX$R 10-DEC-17 16:48
TABLE MY_TEXT_TABLE 10-DEC-17 16:48

15 rows selected.

SQL>
SQL> select table_name, constraint_name, index_name
2 from user_constraints
3 where constraint_type = 'P'
4 order by table_name, constraint_name
5 /

TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
INDEX_NAME
------------------------------
DR$MY_TEXT_INDEX$K SYS_IOT_TOP_45136
SYS_IOT_TOP_45136

DR$MY_TEXT_INDEX$N SYS_IOT_TOP_45142
SYS_IOT_TOP_45142

DR$MY_TEXT_INDEX$R DRC$MY_TEXT_INDEX$R
DRC$MY_TEXT_INDEX$R

MY_TEXT_TABLE SYS_C0017472
SYS_C0017472


SQL>


Yes, that is a large number of database objects besides MY_TEXT_TABLE and MY_TEXT_INDEX.  SYS_C0017472 is, of course, the Primary Key Index on MY_TEXT_TABLE (on the ID_COLUMN column).  The others are interesting.

The "Tokens" I mentioned in the first paragraph are, for the purpose of this table MY_TEXT_TABLE, in the DR$MY_TEXT_INDEX$I.

SQL> desc DR$MY_TEXT_INDEX$I
Name Null? Type
----------------------------------------- -------- ----------------------------
TOKEN_TEXT NOT NULL VARCHAR2(64)
TOKEN_TYPE NOT NULL NUMBER(10)
TOKEN_FIRST NOT NULL NUMBER(10)
TOKEN_LAST NOT NULL NUMBER(10)
TOKEN_COUNT NOT NULL NUMBER(10)
TOKEN_INFO BLOB

SQL> select token_text, token_count
2 from dr$my_text_index$i
3 /

TOKEN_TEXT TOKEN_COUNT
---------------------------------------------------------------- -----------
ANOTHER 1
CAPTURED 1
HEMANT 1
INDEX 1
LONG 2
PIECE 1
TEXT 2
WRITTEN 1

8 rows selected.

SQL>


I have been busy in the last few months and have not published much this quarter. Hopefully, I will get more time in the coming weeks to explore CONTEXT, CTXCAT and Domain Indexes.
.
.
.

Categories: DBA Blogs

how to count the number of records in a file

Tom Kyte - Fri, 2017-12-08 16:06
How do i get to count the number of records in a flat file(.csv/.dat) including the header and trailer records.The code was return in a plsql using utl_file.Can you suggest me the best method to implement the logic in Plsql.And the trailer record sho...
Categories: DBA Blogs

advise given by segment advisor 12c

Tom Kyte - Fri, 2017-12-08 16:06
Hi Tom, My question is, Does <b>segment advisor in 12cR1</b>, advise the use of <b>online table redefination for tables</b> in both d<b>ictionary managed</b> and <b>locally managed tablespace</b>? In addition, What about the <b>use of segm...
Categories: DBA Blogs

Dynamic fields in External File

Tom Kyte - Fri, 2017-12-08 16:06
Sorry if its a weird requirement. I have an input feed coming in like the following(only two rows per file).. col1, col2, col3,Attribute_1,Attribute_2,Attribute_3,......,Attribute_n col1, col2, col3,Value_1,Value_2,Value_3,......,Value_n ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs