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 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 :

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.


Next, this user creates the demonstration table and index :

SQL> connect ctxuser/ctxuser
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> 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> -- 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.


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 /

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 /

Another long text to be captured by the index


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 :

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 /

------------------- ------------------------------ ------------------------
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

15 rows selected.

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 /

------------------------------ ------------------------------





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.

Name Null? Type
----------------------------------------- -------- ----------------------------

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

---------------------------------------------------------------- -----------

8 rows selected.


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

Reconstruct sale from audit commands

Tom Kyte - Fri, 2017-12-08 16:06
We are running on, 12.1 and 12.2. SQL> show parameter audit_trail NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail ...
Categories: DBA Blogs

Reg: Elapsed time

Tom Kyte - Fri, 2017-12-08 16:06
Hello Team, Quite confused with elapsed time definition. elapsed time is equal to db time. but DBtime= cpu time + wait time. so its correct or not. somewhere i found elapsed time is nothing but wall time. plz comment on above statem...
Categories: DBA Blogs

Data Guard vs Active Data Guard

Tom Kyte - Thu, 2017-12-07 21:46
Hi, Please could i get some answers for the following: What is the difference between DG and ADG ? What are the benefits of DG ? What are the benefits of ADG ? What are the risks / constraints of using a DR environment for near real ti...
Categories: DBA Blogs

Grouping Id

Tom Kyte - Thu, 2017-12-07 01:06
Hi Tom I never really understood the usage of GROUPING_ID function in OTN.I heard it avoids using multiple GROUPING functions.Can you please illustrate with a small example Thanks
Categories: DBA Blogs

Dynamic Sql to get the value of the column which is formed by concatenating two strings.

Tom Kyte - Mon, 2017-12-04 18:06
Hi Team, I have a query like this I will get the column name at run time something like IF conditions 1 then Column A. IF conditions 2 then Column B. IF conditions 3 then Column C. IF conditions 4 then Column D. Once i get to know whi...
Categories: DBA Blogs

Partner webcast - Create new opportunities with Oracle Analytics and Big Data

      Create new opportunities with Oracle Analytics and Big...

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

I dropped a table in oracle but when i saw the indexes became like 'BIN$...' i rebuild them the state is still VALID

Tom Kyte - Sun, 2017-12-03 23:46
I dropped the table with cascade option, after importing the table the indexes are there with BIN$... name and the state is VALID. Are they really valid i try to rebuild its rebuilding but name is not changing.
Categories: DBA Blogs

Forms Builder won't connect to database - ORA 28040

Tom Kyte - Fri, 2017-12-01 16:46
I've installed an Oracle 12.2 database on my PC running Windows 10. That seems to be fine and I can connect to the database and the sample HR database is all there. I have then installed the Oracle Developer Suite v10.1.2. That installation appears t...
Categories: DBA Blogs

Service and module

Tom Kyte - Fri, 2017-12-01 16:46
Dear Team, Hope doing well..!!!! please help me to understand difference between service and module in oracle database. Whats use of module? Thanks Pradeep
Categories: DBA Blogs

Enters Amazon Aurora Serverless

Pakistan's First Oracle Blog - Thu, 2017-11-30 23:06
More often than not, database administrators around the technologies have to fight out high load on their databases. It could be ad hoc queries, urgent reports, overflown jobs, or simply high frequency and volume of queries by the end users.

DBAs try their best to do a generous capacity planning to ensure optimal response time and throughput for the end users. There are various scenarios where it becomes very hard to predict the demand. Storage and processing needs in case of unpredictable load are hard to foretell in advance.

Cloud computing offers the promise of unmatched scalability for processing and storage needs. Amazon AWS has introduced a new service which gets closer to that ultimate scalability. Amazon Aurora is hosted relational database service by AWS. You set your instance size and storage need while setting Aurora up. If your processing requirements change, you change your instance size and if you need more read throughput you add more read replicas.

But that is good for the loads we know about and can more ore less predict. What about the loads which appear out of blue? May be for a blogging site, where some post has suddenly gone viral and it has started getting million of views instead of hundreds? And then the traffic disappears after some time suddenly just like it appeared out of nowhere and may be after some days it happens for some another post?

In this case if you are running Amazon Aurora, it would be fairly expensive to just increase the instance size or read replicas in the anticipation that some traffic burst would come. It might not, but then it might.

In front of this uncertainty, enters Amazon Aurora Serverless. With this Serverless Aurora, you don't select your instance size. You simply specify an endpoint and then all the queries are routed to that endpoint. Behind that endpoint lies a a warm proxy fleet of database capacity which can scale as per your requirements within 5 seconds.

It's all on-demand and ideal for transient spiky loads. What's more sweet is that billing is on second by second basis and deals in Aurora capacity units and minimum is 1-minute for each newly address resource.
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs