DBA Blogs

Impdp fails with errors on TEMP and UNDO

Tom Kyte - Thu, 2019-01-10 13:46
Hi, In impdp i have received many times TEMP and UNDO tablespace issue, like unable to extend tablespace. I am import the data into the default permanent tablespace then Why TEMP and UNDO is required. --------- Thanks
Categories: DBA Blogs

Trigger to set a column value

Tom Kyte - Thu, 2019-01-10 13:46
Hi, I have one requirement on trigger. On EMP TABLE it has column Status. If any insert happens then status has to update 1 , If any updates done the status 2. We have to write in one Trigger. Could you please write a trigger to that...
Categories: DBA Blogs

Adding a check constraint to existing table to avoid both columns to be null and allow all other 3 combinations

Tom Kyte - Thu, 2019-01-10 13:46
I have a requirement that has below output: Col1 col2 NOTNULL NOTNULL(my table already has this data and I want to keep it) NULL NOTNULL(my table already has this data and I want to keep it) NOTNULL NULL (my table already has this ...
Categories: DBA Blogs

Error ORA-01950 on trying to use ALTER TABLE for modifying partitioning scheme

Tom Kyte - Thu, 2019-01-10 13:46
Hello All, I am trying to perform some tests in LiveSQL related to changing the partitioning scheme of a table in 18c. Based on an example from the 18c documentation, I successfully created a table and several indexes, but when I attempted to ...
Categories: DBA Blogs

Getting started Workshops for Autonomous DW / TP

Oracle Autonomous Database is built around the market leading Oracle database and comes with fully automated data warehouse edition & Online Transactions processing edition with specific features...

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

The best way to expire the rman backups

Tom Kyte - Wed, 2019-01-09 19:26
Hi Tom, Just to get your advice on the best way to expire the rman backups. 1. We don't set retention on the RMAN catalogue level. 2. We do set retention on the Backup media level. 3. We rely on the crosscheck job to tell the rman catalog whi...
Categories: DBA Blogs

Please help, can't drop a corrupt table

Tom Kyte - Wed, 2019-01-09 19:26
Hi all, we have a corrupt object named "TO_BE_DROPPED" in our development database. Previously it had a different name, but someone renamed it, because it cannot be deleted, only renamed. It cannot be dropped by any means we could find. Apparently...
Categories: DBA Blogs

Execute Immediate INTO Clause

Tom Kyte - Wed, 2019-01-09 19:26
Hi Tom, I am trying to pull the count of tables in the particular schema. I am using pl/sql block for this. Looks like there is a error with EXECUTE IMMEDIATE INTO clause in this block. Can you please help on this? DECLARE l_session_name ...
Categories: DBA Blogs

January 25th AZORA Meetup with Viscosity

Bobby Durrett's DBA Blog - Wed, 2019-01-09 17:36

If you are in the Phoenix, Arizona area on Friday, January 25th please come to the AZORA meetup with talks by Viscosity. Please RSVP on the meetup site so we know how much food to order: Meetup link. We have a great lineup of speakers with talks about new features of the latest Oracle database versions. I know that I have not kept up with all the latest features, especially with all the other technical training that is on my plate. So, I am looking forward to setting aside this dedicated time to learn about Oracle database features from highly qualified presenters.

Plus, it will be fun and includes lunch! Come join us for our first meeting of the new year.

Bobby

Categories: DBA Blogs

Migration of Basicfile to Securefile

Tom Kyte - Wed, 2019-01-09 01:06
We've a database that was upgraded from 11g standard edition to 12c standard edition. According to Oracle documentation, we should migrate from basicfile storage to securefile. All the recommendations we found so far are for enterprise edition and s...
Categories: DBA Blogs

Alphanumeric Counter

Tom Kyte - Wed, 2019-01-09 01:06
Hi, i would like to know if it is possible to generate a alphanumeric counter like below. A001 A002 . . A999 AA01 AA02 . . AA99 AB01 AB02 . . AB99 AC01 . . . ZZZZ how can we implement this in a function? i have tried couple o...
Categories: DBA Blogs

Bulk insert of more than 6 million every day in OLTP system

Tom Kyte - Tue, 2019-01-08 06:46
Hi Tom, Following loop is my biggest problem in live system. Following loop will run every day to insert more than 6 million records in OLTP system and taking 20 hours. Memory wise we don't have any issues. <b>6TB of memory 195 CPU cores 2.5 ...
Categories: DBA Blogs

User's table not shown in DBA_TABLES

Tom Kyte - Tue, 2019-01-08 06:46
I have a curious dilemma... To summarize, I have a user 'ABC' with a table 'POINTS'. I can query DBA_OBJECTS to see that user's table, but that table does not appear in DBA_TABLES. Is there some difference between these two system views? <code>SE...
Categories: DBA Blogs

SQL NULL and JSON null. To be the same or not to be !?

Tom Kyte - Tue, 2019-01-08 06:46
Hello Beda, I have a question regarding keys having a "null" value in JSON. The JSON documentation: https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/json-data.html#GUID-FBC22D72-AA64-4B0A-92A2-837B32902E2C says the fol...
Categories: DBA Blogs

New “Let’s Talk Database” events in Australia/NZ in February/March 2019 !!

Richard Foote - Mon, 2019-01-07 18:58
Very excited to announce some new “Let’s Talk Database” events scheduled for February 2019 in various locations in Australia/NZ: Canberra: Wednesday, 20 February 2019: Registration Link Sydney: Thursday, 21 Feburary 2019: Registration Link Brisbane: Friday, 22 Feburary 2019: Registration Link Melbourne: Wednesday, 27 February 2019: Registration Link Wellington: Friday, 1 March 2019: Registration Link   […]
Categories: DBA Blogs

ORA-01031 in view DML with dual reference

Tom Kyte - Mon, 2019-01-07 12:26
At a customer I work for there is a design standard that each DB-view should start with a so-called 'comments' block. This is to ensure that comments are stored in the data dictionary in the DB. create or replace view xxxx as with comments as ( ...
Categories: DBA Blogs

what are tables with MDXT in the name and can I delete them?

Tom Kyte - Mon, 2019-01-07 12:26
Hi, I have a datawarehouse with 5000 tables in it. <code> select count(*) from tab where TABTYPE = 'TABLE' </code> In this datawarehouse there are also tables with MDXT or MDRT or BIN in the tablename, more then 4600!! 400 actually use 4...
Categories: DBA Blogs

Replicating Data to another database in the same server

Tom Kyte - Mon, 2019-01-07 12:26
Hello, Ask Tom Team. Happy New Year for all of you. I have two single instance databases (A and B) in the same server. I need to replicate from A to B (some columns of some tables) in order to customers can view the data through a web applicat...
Categories: DBA Blogs

REMAP_TABLESPACE not working during impdp

Tom Kyte - Mon, 2019-01-07 12:26
Hello, I have a Pluggable database OTB1 with multiple schemas with different tablespaces. Tablespaces names are in lowercase. (SCHEMAS AND TABLESPACES NAMES ARE SAME, BUT ONE IN LOWER CASE AND OTHER IN UPPER CASE) <code>SQL> select username fro...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs