Error:ORA-30926: unable to get a stable set of rows in the source tables
Error:ORA-30926: unable to get a stable set of rows in the source tables
Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause.
Action:
On many websites you will see the action you need to take is
“Remove any non-deterministic where clauses and reissue the dml.”
This is not that clear – at least for me.
So here is an explanation with example:
The error occurs when the target table (where you want to merge) has a primary key/unique key
constraints and the source table (from where data to be merged into target) or source query
contains more than 1 record for the unique key.
In other words if you have multiple records in the source table that matches the unique key value
of one single record in the target table you will get this error.
See this example below:
create table t1(a number, b number, constraint pk_t1 primary key(a));
create table t2(a number, b number);
insert into t1 values (1,1);
insert into t1 values (2,1);
insert into t1 values (3,1);
insert into t1 values (4,1); Continue reading
Clone Oracle database – cloning Tutorial
People describe database cloning in different ways. Basically Oracle database cloning simply means copying an Oracle database from another database. For example you want to refresh your test oracle database using production data. In that case you can do database cloning by simply getting rid of your test database
and recreating it with production database including data.
You may need/want to clone a database for any number of reasons such as:
- 1. Your database is somehow corrupted
- 2. Your database rollback segment or other tablespaces are corrupt and you can not recover them
- 3. You simply want to refresh your database from another database
Whatever the reasons are, the cloning steps remain mainly same. Here are the steps that you need to execute to clone Oracle database.
Assumptions:
- You can not OPEN your corrupt database but you can login as sysdba.
- You have access to a functioning database which you want to copy
- You have access DBA credentials
Step1: Make a list of your logfiles, datafiles and controlfiles of your corrupt database. You will need this list to delete them physically.
Logon to the corrupt database as:
sqlplus / as sysdba
or
svrmgrl
connect / as sysdba
Run following commands to get the list of your logfiles, datafiles etc:
select name from v$controlfile;
select * from v$logfile;
select name from v$datafile;
Step2: Shutdown the corrput database using
shutdown abort;
Step3. Physically delete all logfiles, datafiles and controlfiles from the machine.
Step4: Logon to functioning database machine.
Now logon to the database as
sqlplus / as sysdba
or
svrmgrl
connect / as sysdba
whatever is applicable to you according to yuor Oracle database version.
3. Run
alter database backup controlfile to trace;
This command will generate a text formatted trace file that will have the physical structure of the functioning oracle database. You are going to need this file later.
4. Run
show parameter user_dump_dest;
This command will give you the location of your Oracle trace file generated on
Step4.3
5. From another session to your functioning database machine go to the location of your user_dump_dest and make sure there is a trace file and it looks something like this:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “testdb” NORESETLOGS ARCHIVELOG
MAXLOGFILES 64
MAXLOGMEMBERS 5
MAXDATAFILES 64
MAXINSTANCES 32
MAXLOGHISTORY 17871
LOGFILE
GROUP 1 (‘/u01/oradata/testdb/oralog1a.rdo’,
‘/u02/oradata/testdb/oralog1b.rdo’) SIZE 25M,
GROUP 2 (‘/u01/oradata/testdb/oralog2a.rdo’,
‘/u02/oradata/testdb/oralog2a.rdo’) SIZE 25M
DATAFILE
‘/u01/oradata/testdb/system01.dbf’,
‘/u01/oradata/testdb/mydatabase.dbf’,
‘/u02/oradata/testdb/app_data_01.dbf’,
‘/u02/oradata/testdb/app_data_02.dbf’,
‘/u02/oradata/testdb/app_index_01.dbf’
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
5. Now copy the content of this file in your desktop in a test file called
“create_controlfile.sql”.
Remove the lines “RECOVER DATABASE” and “ALTER SYSTEM ARCHIVE LOG ALL”
and “ALTER DATABASE OPEN;” from create_controlfile.sql;
6. Now shutdown your functioning database and exit, run
shutdown immediate;
exit;
Step5. Once database is shutdown the next step is to copy all files from
your functioning database to the new location (machine of your corrupt database).
If you need to ftp the files then always use “bin”.
Copy all these files (logfiles, datafiles and controlfiles and init.ora) in exactly same locations as they are in the functionning database machine (check create_controlfile.sql for reference). If you need to create directories
then create them. Physical location of each of these file should be exactly same.
Step6. When all files are copied to their correct locations, go to Machine where your database is corrupted.
Then login to oracle as
sqlplus / as sysdba
or
svrmgrl
connect / as sysdba
Step7: Now run the controlfile script:
@create_controlfile.sql;
Your cloned Oracle database should now come up.
Login to your database to see if it is functioning correctly.
But it is very easy and straight forward process. If you have question let me know.
Oracle 11G’s Strong Verifier and Case Sensitive Password
by: Paul Fleming
As an Oracle DBA, you probably have heard a lot about the new security features in Oracle 11G such as database vault, audit vault, strong verifier, enhanced ASO, etc. But with a busy schedule, how do you find the time to study all the topics in depth? Are all of them going to affect your daily DBA operations? What should you know first and foremost? Of course, depending on the particular database you are administering, you will have your unique need. But as a rule of thumb, what tends to affect you the most is almost never those cool new features. The reason is simple: if you don’t use them, they won’t affect you. What tends to affect you is the change in the existing features that might lead to compatibility or interoperability issues. When that script you have used for years suddenly stops working, you will be disappointed. When your boss or above calls to ask what happened, you could feel terrible.
In this article, we discuss the new password case sensitivity feature in 11G. Every Oracle session starts with authentication. If the authentication fails at the beginning of your script, everything that follows will almost certainly be broken. So what was changed, why, and what should you know? Continue reading
Oracle Global Temporary Table Example
They are special kind of tables in Oracle which have structure and definition same as other usual Oracle tables but where data is not persistent.
Data is only session specific or transaction specific. This means that users can insert data into the table simultaneously but they will never see each others data.
Syntax:
CREATE GLOBAL TEMPORARY TABLE SCHEMA_NAME.TABLE_NAME
(
COL1 DATATYPE,
COL2 DATATYPE,
.
.
.
COLNn DATATYPE
)
ON COMMIT DELETE ROWS
NOCACHE;
There are two options available for ON COMMIT clause
Oracle tablespace concept and increasing size of Oracle tablespace
Oracle Tablespace Concept
In Oracle world you will frequently hear about the word “tablespace”. So what is a table Tablespace?
To put very simply it is the Logical area of Oracle where all your data goes. The Tablespace holds your data for your tables and indexes. It other words the tablespace is a container where your data is kept. A tablespace is a logical concept only, it has no physical existence.
A tablespace is comprised of one or more datafiles. The files are the real operating system files that physically exist in your computer’s disks. Continue reading
Recent Comments