Data Guard :: Column Name Is Null In V$archive_log?
Jul 22, 2010
In V$Archived_log, the column "name" showed No any content?
select name,SEQUENCE#,ARCHIVED,APPLIED,DELETED,STATUS,COMPLETION_TIME from v$archived_log
NAME SEQUENCE# ARC APP DEL S COMPLETIO
------------------------------ ---------- --- --- --- - ---------
2565 YES NO YES D 20-JUL-10
2566 YES NO YES D 21-JUL-10
2567 YES NO YES D 21-JUL-10
2568 YES NO YES D 22-JUL-10
2569 YES NO YES D 22-JUL-10
2570 YES NO YES D 22-JUL-10
View 1 Replies
ADVERTISEMENT
Aug 16, 2012
I got missed the archivelog from primary DB.now its got deleted. But now it is stored in Tape_backup. i don't know about tape backup. how to access and restore the archive_log from tape backup. My tape setup is Legato..ultrium.
os:Hp-ux
DB:Oracle9i
View 6 Replies
View Related
Mar 16, 2011
I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.
I am using below query.
ALTER TABLE T_test
modify (paid_to_date null)
View 9 Replies
View Related
Jan 9, 2012
when i follow this steps mention on this website
[URL].........
to modify column from null to not null i got this error and on this website its show successful
my steps are
first i create a table
SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
2 Stu_Class varchar(10));
Table created.
Then insert some rows into Stu_Table
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');
1 row created.
SQL> select * from Stu_Table;
ST STU_NAME STU_CLASS
-- ---------- ----------
1 Komal
2 Ajay
3 Rakesh
4 Bhanu
5 Santosh
Table Structure is like this
SQL> Describe Stu_Table
Name Null? Type
----------------------------------------- -------- ----------------------------
STU_ID VARCHAR2(2)
STU_NAME VARCHAR2(10)
STU_CLASS VARCHAR2(10)
now when i try to modify this Stu_id column to not null its give me error.
SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
and when i try to add new column with not null its also gives me error
SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
View 6 Replies
View Related
Oct 25, 2012
Initially i have inserted the data into table like
Date xxx yyyy
1/1/12 1 1
2/1/12 null null
3/1/12 null null
4/1/12 1 1
5/1/12 1 1
6/1/12 null null
in above example data is null for some date here my requirement is how can i copy before not null data(1/1/12) to *2/1/12, 3/1/12* .
View 3 Replies
View Related
Jul 5, 2011
We have physical data guard configured version (10.2.0.4). We are in need to upgrade primary & standby database to 11G R2. Can we perform rolling upgrade.
View 3 Replies
View Related
Oct 17, 2012
Controlling User Access to Tables in a Logical Standby Database can be controlled using the following command:
ALTER DATABASE GUARD STANDBY;
My simple question is: how can I know the current active Guard setting in the standby database?
Oracle 11g R2.
View 1 Replies
View Related
Dec 2, 2011
i've got a project and im wondering whether it is possible to create an if statement that says something like
"If column A is NOT NULL then column B cannot be NULL"
would I have to do this as a trigger or a constraint?
View 2 Replies
View Related
Oct 31, 2012
I have a table with column A which contains very few null values. I need to select these rows. I am considering two options:
a) create function based index on NVL(A, 0) and use this in where clause NVL(A, 0)=0 (column doesn't have values 0)
b) create function based index on NVL2(A, 0, NULL) and and use this in where clause NVL2(A, 0, NULL) = 0
First idea was option A. But I realized in option B the index will be much smaller, because most of values of column A isn't NULL so NVL2 will return NULL and index will not have as much leafs as in NVL. It is good idea to use NVL2? Is there any against to use option B instead of A?
View 1 Replies
View Related
May 31, 2011
I have a table with columns emp_i, LOC_C and SUBSID_C. I want to find all emp_i's with LOC_C OR SUBSID_C as always NULL. Please note that the value should be NULL, always, for all dates.
--------------------------------------------
EX:
emp_i----LOC_C---SUBSID_C--Eff_Date
100------20------30--------01/01/2011
101------NULL----10--------05/01/2011
102------NULL----NULL------02/01/2011
101------20------NULL------02/01/2011
102------NULL----NULL------05/01/2011
103------NULL----20--------01/01/2011
103------NULL----NULL------02/01/2011
--------------------------------------------
The query ---
should return 102 as LOC_C OR SUBSID_C is ALWAYS NULL.
should return 103 as LOC_C is ALWAYS NULL.
should not return 101, as LOC_C is not ALWAYS NULL.
In other words, the query should give list of emp_i who never ever had a non-null value for LOC_C OR SUBSID_C. The purpose is to find the emp_i for which the columns LOC_C and SUBSID_C are never used.
I tried the query:
---------------------------------------------------
SELECT DISTINCT ORG_EMP_I FROM tab1
WHERE ORG_GRP_I = 58
AND ORG_EMP_I NOT IN (
SELECT DISTINCT org_emp_i
FROM tab1 ap
WHERE ap.ORG_GRP_I = 58
AND trim(ap.LOC_C) IS NOT NULL OR ap.ORG_SBSID_C IS NOT NULL )
---------------------------------------------------
View 10 Replies
View Related
Jun 15, 2012
I have 3 columns in a table: colX, colY, colZ.
Trying to find a way to prevent duplicates with these, but only if colX is not null.
For example, if there are already values for: colX = 1, colY = 1, colZ = 1
then:
Allowed: colX = null, colY = 1, colZ = 1
Not allowed: colX = 1, colY = 1, colZ = 1
I can't create a unique constraint on these columns because there are many null values for column colX, and as mentioned, when colX is null, colY and colZ can be any values.
I also tried using a before insert trigger to find duplicates before posting and raise an error if found, but this causes an ORA-04091 mutating error since the trigger in the table is referencing itself to check for duplicates.
Also, I know there is something called a function based index, but I cannot use those with my code, so I need another solution if possible.
View 4 Replies
View Related
Jul 24, 2012
I am having following table, and below are the out put and desired output.
CREATE TABLE tbl1
(
mon VARCHAR2(10)
, grp VARCHAR2(50)
, visits NUMBER
, redirect VARCHAR(50)
)
;
[Code]....
Query:
SELECT
mon
, grp
, SUM(visits)
FROM
tbl1
WHERE
redirect IS NOT NULL
GROUP BY mon, grp
;
Output:
May-12 Green 16
May-12 Blue 20
May-12 Yellow 13
Desired Output:
May-12 Green 16
May-12 Blue 20
May-12 Yellow 13
May-12 Red 0
May-12 Orange 0
How can this be acheived?
View 1 Replies
View Related
Aug 1, 2012
I'm having trouble creating a view that has a not null column. Using this script you can see that the resulting table doesn't have a not null constraint for the first column even though both source columns for that row are not null. Is there anyway to force the view to mark that first column as not null? (I need it for ODP.NET otherwise I get an error there)
DROP TABLE MYTABLE;
CREATE table MYTABLE
( COL1 NUMBER(2) NOT NULL,
col2 number(2)) ;
drop table mytable2;
CREATE table MYTABLE2
[code]....
View 7 Replies
View Related
Mar 12, 2010
Our organization is attempting to learn more about the partitioning features of Oracle 11g. I've been reading the partitioning manuals, and I have not found a clear answer on this topic, but I suspect I know the answer.
If you create a range partitioned table; using interval partitioning, say something like this:
CREATE table range_parti (
CODE NUMBER(5),
DESCRIPTION VARCHAR2(50),
CREATED_DATE DATE)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION my_parti VALUES LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);
but you try to insert a null value as the partition key, you get the following error:
SQL> INSERT INTO range_parti VALUES (1,'one',NULL);
INSERT INTO range_parti VALUES (1,'one',NULL)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Elapsed: 00:00:00.07
Is there no way to tell it to use a default partition for NULL values? Or specifically designate a partition for NULL values WITHOUT having to manually list out each partition? It seems it works if you don't use the INTERVAL keyword, list out your partitions, and use MAXVALUE. However, our hope to avoid having that as it creates monstrously huge DDL statements for tables that have lots of date ranges, and we will be forced to manually add new partitions each month as data is added/time passes.
It appears from my experience so far, if your column can allow nulls, you cannot use interval range partitioning on that column.
View 5 Replies
View Related
Dec 13, 2010
In the below code, do I need the 'NOT NULL' after the 'state char(2)'? I am guessing that I do not need it since I have the CHECK constraint on the column.
CREATE TABLE employee(
id PRIMARY KEY,
first varchar(20) NOT NULL,
middle varchar(20),
[code]....
View 10 Replies
View Related
Dec 13, 2010
We are migrating a proc application as described below.
Old Env: UNIX
Old DB: Oracle 8i
New Env: Linux
New DB: Oracle 11g
New modules are successfully compiled in Linux environment. But we are facing issues in writing the output of VARCHAR datatype to a file.
find below the extract of code.
EXEC SQL BEGIN DECLARE SECTION;
varchar mcolmnvarchar[4];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE crs CURSOR FOR
SELECT NVL(colmn,' ') FROM table1
memset(mcolmnvarchar.arr,'�',4); //Was added for only Linux migration. Not present in unix env.
EXEC SQL FETCH c1 INTO :mcolmnvarchar;
cout << "Data at Stage one"<< mcolmnvarchar << endl;
mcolmnvarchar.arr[mcolmnvarchar.len]='�';
cout << "Data at Stage two"<< mcolmnvarchar << endl;
fprintf(fptr,"%-4s",mcolmnvarchar.arr);
Above code works absolutely fine in Unix env with Oracle 8i. But with Linux env & Oracle 11g it is not working. No compilation or run time errors. Data at Stage one prints the output of database properly. But after null terminator code, Data at Stage two statement prints without any value. Value is lost after null terminator code.
View 7 Replies
View Related
Dec 22, 2011
I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.
I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.
WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......
DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes
The results I am expecting is
DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14
I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.
SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN
But I don't think my manager will allow me to deploy a Multi Row function .
View 2 Replies
View Related
Jun 25, 2013
i want to count group above report records! want to count department group employees there in time is not null!
my report query
SELECT div.division,
DEP.DEPARTMENT,
DEP.STRENGTH,
E.EMPLOYEECODE,
E.NAME,
DES.DESIGNATION,
[code].....
im doing it with formula column!
function CF_PRESENTFormula return Number is
v_emps number;
begin
select count(1)
into v_emps
[code]......
View 18 Replies
View Related
Dec 13, 2012
I have table with the values as below.
C1C2C3C4
NAMEJOHN10ABC
NAMESMITH30DEF
NAMEROBERT60XYZ
I dont want to print the repeated value(NAME) of C1 multiple times as below.
C1C2C3C4
NAMEJOHN10ABC
SMITH30DEF
ROBERT60XYZ
I could do it using the below query using union with the rownum.
select * from (
select rownum rn, c1,c2,c3,c4 from table_new
) where rn =1
union
select * from (
select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new
) where rn between 2 and 3
Is there any other way of displaying using a single sql query.
View 17 Replies
View Related
Jul 7, 2011
I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.
ex:
the output should look like this.
owner schema table_name total# recs in the table, column_name,
------ ------ ---------- ------------------------- -----------
# of records not null # of records null
---------------------- --------------------
View 12 Replies
View Related
Jul 5, 2012
How to insert null record (for some columns) in table using loop.
sample data of x_tab
order_id order_name
231 xxx
123
345
111 vvvv
View 5 Replies
View Related
Jul 1, 2013
previously i set null constraint to the column and creating some rows and need to change new entering values as not null constraint to the column in oracle without disturbing the old records. how can I do that.
View 5 Replies
View Related
Jun 21, 2013
I am having issue with IMPDP on ORACLE VIRTUAL COLUMNS.I am having following table with Virtual column defined with Not null. Expdp is fine without any issue.
DDL :
------
CREATE TABLE alert_hist
(
alertky INTEGER NOT NULL,
alertcreatedttm TIMESTAMP(6) DEFAULT systimestamp NOT NULL,
alertcreatedt DATE GENERATED ALWAYS AS (To_date(Trunc("alertcreatedttm"))) VIRTUAL NOT NULL
When I do the import (IMPDP) it got failed with the following error.
. . imported "TESTSCHEMA"."VALART" 359.1 KB 4536 rows
ORA-31693: Table data object "TESTSCHEMA"."ALERT_HIST" failed to load/unload and is being skipped due to error:
ORA-39097: Data Pump job encountered unexpected error -1
After that I dropped the Virtual Not null column and recreated that column with Nullable.
DDL :
-----
alter table alert_hist drop column alertcreatedt;
alter table alert_hist add alertcreatedt DATE GENERATED ALWAYS AS (To_date(Trunc("alertcreatedttm"))) VIRTUAL;
After that I took the expdp and impdp , it went fine with out any issue.
View 7 Replies
View Related
Jan 16, 2013
i have configured physical standby in my local system, to check logshipping i created a table at primary db, wen i tried to check in standby, it says table does not exist..below are primary & standby alert entries..
Primary alert log
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.98)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STAND)(SERVER=dedicat ed)(CID=(PROGRAM=d:oracle11gappadministratorproduct11.1.0db_1inORACLE.EXE)(HOST=A960M)(USER=SYSTEM))(SERVER=dedicated)))
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
[code]....
View 1 Replies
View Related
Nov 5, 2010
I want to change dbname,sid,data/control file locations in operational dataguard setup i plan to follow as below
1)shutdown primary and standby (stop managed recovery)
2)change db name in init.ora of primary and standby change database name control file location
3)create control file for primary from trace(script) make changes for db name and file locations
4)mount and open primary database
5)create standby control file
6) transfer standby control file to standby
7) mount stand by database and start manage recovery
If this steps will error free do i need to follow any thing additional to this or what is other best way for this or its not possible at all
View 1 Replies
View Related
Feb 24, 2013
Oracle Version: 11.1.0.7.0
Active Dataguard
Statspack has been configured for Active Dataguard on Primary database.We got an spike of Buffer busy waits for about 5 min in Active Dataguard, this was causing worse Application SQL's response time during this 5 min window.Below is what i got from statspack report for one hour
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 18611 21-Feb-13 22:00:02 236 2.2
End Snap: 18613 21-Feb-13 23:00:02 237 2.1
Elapsed: 60.00 (mins)
[code]...
Why there could sudden spike of demand on UNDO data in Active Data Guard ?
View 2 Replies
View Related
Mar 29, 2011
I am working in a bank as an system consultant, i have a SAN Storage Area and oracle as below.
SAN 1
This interface includes the DATA FILES of the oracle tablespace
SAN 2
SAN1 Mirrors the DATA FILES of the oracle tablespace to SAN 2
1. Can i rely on real time data recovery from SAN2 ?
2. if SAN1 (Data Files are currupted) will the SAN2 Data Files will be currupted as well.
3. If the SAN2 is currupted then what Oracle Features can be used to have uncurrupted data.
View 5 Replies
View Related
Jul 23, 2010
i did everything writen but when i do *SQL>alter database recover managed standby database disconnect from session;*
i go and look in the standby database AlertLog file ,and thats whats writen
*ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATADBSYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: غير قادر على فتح الملف
O/S-Error: (OS 3) The system cannot find the path specified.
[code]....
strange thing that it realises the primary database in drive F and it goes to it but i dont understand what could be the reason of this ,although im doing this command while primary database is shutdown!
View 2 Replies
View Related
May 2, 2012
I configure logical standby online .when I execute dbms_logstdby.buid,first
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
it was blcoked by other sesson,then i kill the holding session,but no work.then i cancel this step and execute it again . the error is
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
BEGIN DBMS_LOGSTDBY.BUILD; END;
*
ERROR at line 1:
ORA-01354: Supplemental log data must be added to run this command
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3669
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3755
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_INTERNAL_LOGSTDBY", line 370
ORA-06512: at "SYS.DBMS_LOGSTDBY", line 157
[code]....
View 3 Replies
View Related
Jun 15, 2012
I have set up a cross platform (Microsoft Windows IA (32-bit) -> Linux x86 64-bit) data guard and it worked fine.Then I did a switch over (which again worked) and found out the data is not getting replicated at all.. checked the data files available from the new primary database and found out they are in the windows format as below..
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:ORACLEAPPADMINISTRATORORADATAMFSSYSTEM01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSSYSAUX01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSUNDOTBS01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSUSERS01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSRMANRMAN_TS01.DBF
and physically they were created at '/home/app/oracle/product/11.2.0/db_1/dbs/' and as
D:ORACLEAPPADMINISTRATORORADATAMFSREDO02.LOG
D:ORACLEAPPADMINISTRATORORADATAMFSREDO03.LOG
D:ORACLEAPPADMINISTRATORORADATAMFSRMANRMAN_TS01.DBF
View 5 Replies
View Related
