I need to change systimestamp from +01:00 to +08:00.
SYSTIMESTAMP
14-JUN-12 03.29.47.657901 PM +01:00 =====> I need to change it as +08:00
how can i change it?
I added TZ=SGT in my listener.ora file and bounced the listener. Even though systimestamp is still showing --> +01:00
I have a staging which connects to RAC clusters via dblink..I have a scenario to collect the hostname,systimestamp whenever i do a query from this rac clusters
So in general below would be cool
select UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric would work perfect
But on dblink..
select UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric@dblink.is returning local timestamp and hostname which is not intended
So i even tried subquery..
select (select host_name,systimestamp from v$instance),t UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric@dblink
this would work in certain case ...But it certain cases it would throw an error saying mutiple rows from asubquery .All ineed is to get hostname,systimestamp append to all sql i fetch from any this rac clusters through this dblinks.
I saw bunch of other posts but I could find the post that exactly explaining about where the value returned as systimestamp / sysdate comes from or impacted Here is my situation I have an access to this db (let me call db A) and when I access it, I get following result. I don’t have full access to this db so I cannot experiment a lot here.
SYSTIMESTAMP CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE
--------------------------------------------------------- --------------------------------------------------------- ------------------------------------------------ --------------------- ----------------------------
27-MAR-13 02.31.55.041411 AM +00:00 26-MAR-13 07.31.55.041416 PM -07:00 26-MAR-13 07.31.55.041416 PM +00:00 -07:00
I’m in PST timezone.I have my db which I have full access as well as its host.I can make result like db A on my db if I started up db and its listener while TZ environment variable equal to UTC.Now I saw in other post that someone was trying to retrieve systimestamp value in a job executed via dbms_scheduler.run_job.
So I did that in two ways. 1 with use_current_session = true and 2 is false for the same.On my db, results are the same (both returns time in UTC) but on db A, I got UTC time when use_current_session = true and PST when use_current_session = false.
So questions are:
What could be the difference in setup between my db and db A?
Is there a query, logfile, or anything I should check to find out what can be the difference?
I tried to find the cause with my db and I could see the same result as db A which is to see UTC time if use_current_session = true and PST time if use_current_session = false by bringing up the db listener after I set TZ environment variable equal to PST8PDT. However this causes systimestamp from sqlplus session become also a PST time.
The reason I’m playing around with the setup and checking systimestamp value is because we are facing the situation where everywhere except pl/sql job submitted by enterprise scheduler service is pointing wrong timezone (PST instead of UTC)
I have a table A with a column B timestamp(6). The tables contains around 300000 rows..I have created index 'idx' on the column B.When i compare column 'B' with systimestamp, it does not use the index, whereas if i compare 'B' with sysdate it uses the index.
Eg :
select count(*) from a where b<=sysdate;
The above used the index 'idx' and executed in 1 second
select count(*) from a where b<=systimestamp;
The above does not use the index and executed in 19 seconds.
We Require to Change the Sequence Next Value.
If it possible to Update the Sequence Next Value in Oracle.
I want to change the partition name for my existing max value partition. I tried searching in forums but couldn't get the info.
For Example: I have partition PART_30001230, i want to change this to part_maxvalue
I have a table in that table ename column the data type is VARCHAR2(40).
In that column the name is like kumar's and Caño
I have created text file using spool it's showing like
Reño kumar¿s
Why only for kumar's it's showing like "¿" this.
i have requirement to change the oracle home from /u04/....../dbhome_2 to /u03/...../dbhome_4
DB=11g
application version=R12.
I observed a strange behaviour of a query after using stored outline on it. I created a stored outline for a query in one database before creating the outline i had set 2 session level parameters optimizer_index_caching and optimizer_cost adjust. i then took an export of the stored outline and then imported into another db. but here the plan of the query seems to be different from the database from which the outline was taken. why is this change? My version of oracle is 10.2.0.5.0
View 3 Replies View RelatedI'm having a problem with a column that is too short for it's data. It causes multiple lines for the same record. How can I change a column width in Oracle? I'm using a select statement to see the data.
View 1 Replies View RelatedI'm in the process of making a list of changes. What database application changes would be classified as standard changes and would not require a CAB meeting and which ones would be deemed critical that a CAB meeting would be required before going ahead with the change.
View 2 Replies View RelatedI have a simple question, hope it has a simple answer. I changed the default date format for a SCHEMA using a TRIGGER and AFTER LOGON, so it set the NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'. It does work fine, however, the problem is when I connect using a client with JDBC driver. When using SQL PLUS I get the date in the format specified above YYYY-MM-DD HH24:MI:SS, however, when using a client (Aqua Data 6.5.8 I know it is kinda old) and SQuirrel SQL 3.4.0 I always get the date in the YYYY-MM-DD format. I started to think that the issue is with the JDBC because it works fine when I connect with SQLPLUS.
View 5 Replies View RelatedIn sql server we can able to change the order of a column but some says in oracle also we can do it. But as i think it is not possible.
View 3 Replies View RelatedI would like the change the name in run time find the attachment.
View 3 Replies View RelatedI have created an Object Type and this Type is mapped to a column datatype in a table.This Table has values inserted.
create or replace
type column_type as object (
col_name varchar2(30),
col_comment varchar2(4000)
);
[code]....
ORA-02303: cannot drop or replace a type with type or table dependents...how to resolve this issue without having to delete any column from the table?
I want to change the date format at database level in 10g express edition.
When i try to run the command,
ALTER SYSTEM SET NLS_DATE_FORMAT='DD/MM/YYYY',
it throws error like specified initialisation parametr is not modifiabale with this option.
what are the other ways to chnage the database date format to the desired one.?
I have below data
SQL> desc IMEI
Name Null? Type
----------------------------------------- -------- ----------------------------
MSISDN NOT NULL VARCHAR2(20)
IMEI NOT NULL VARCHAR2(16)
DATE_MOD NUMBER(13)
IMSI VARCHAR2(18)
ICCID VARCHAR2(20)
T_PROF RAW(20)
EXTRA_DATA VARCHAR2(100)
SQL> select DATE_MOD from IMEI;
DATE_MOD
----------
1.2199E+12
1.2348E+12
1.2278E+12
1.2263E+12
1.2278E+12
1.2378E+12
1.2414E+12
How can I change date_mod to normal date format like year/month/day hour:minutes:second ?
I'm in an environment with Weblogic Oracle Forms 11G (Oracle Fusion Middleware). I can not update a form that is in use. I can not change the fmx file.
Can I change this? Where can I configure this?
Whenever there is a change in the c_flag, we need to identify the change and do some processing. How do I identify the change from either 1 to 0 or 0 to 1. The change can be either way. The point of importance is to identify the change.
CREATE TABLE tab1 ( id NUMBER(10) NOT NULL,
description VARCHAR2(50) NOT NULL,
Start_Date DATE,
id_num NUMBER(10),
c_flag varchar2(2));
[code]....
I need to change Monday like first day of week and not Sunday.
select to_char(to_date('01-01-2012'),'w') from dual
Result: 1
select to_char(to_date('02-01-2012'),'w') from dual
Result:1
and the idea is that 02-01-2012 must be the second week
I need to change data file path, and i got some document also but one thing confusing me, we need to offline the Tablespace, so my concern is all the table space like system, sys, user, temp etc. need to offline and then alter the database?
View 9 Replies View RelatedWe need to audit what data has changed from Vendor_B table when compared to Vendor_A table.
There is two tables:
a) VENDOR_A
b) VENDOR_B
VENDOR_A and VENDOR_B table does not share the same structure but have 1 column in common - the ID and may or may not having changed Coordinates.
We need to audit what coordinates has changed from Vendor_B data when compared to Vendor A and get the report back in the format of:
<pre>
VENDOR_A VENDOR_B
ID Change_date LONGITUDE LATITUDE LONGITUDE LATITUDE
873 1/02/2013 -33.46711315 151.3195056 -33.46211316 151.3245057
694 3/02/2013 -33.46721315 151.3198056 -33.46214312 151.3265053
</pre>
---------------------------------------------------------------------------------------------------------------------------------------------------
--Found a sample query from Ask Tom but I am not sure how I can apply them to get the structure above.
---------------------------------------------------------------------------------------------------------------------------------------------------
<pre>
create table base_tab (pk number, column1 number, column2 varchar2(30), column3 date);
Create table refresh_tab (pk number, column1 number, column2 varchar2(30), column3 date);
Create table diff (pk number, column1 number, column2 varchar2(30), column3 date, base_tab number,
refresh_tab number, action varchar2(30));
[Code] .....
--clean out the differences table
truncate table diff;
--------------------------------
-- build the difference data
------------------------------------
insert into diff select pk,column1,column2, column3,
count(src1) base_tab, count(src2) refresh_tab, null action
from
( select a.*,
[Code]....
What the "change" RMAN command does? For example, what is the effect of following command:
change archivelog until time 'SYSDATE-7' delete
And what is the difference between following commands:
change archivelog all validate;change archivelog all crosscheck;change archivelog all;
I checked the following doc but could not find anything:[URL]I am using Oracle 10gR2 on RHEL 64-bit.
Current date format in database session is as below.
SQL> select sysdate from dual;
SYSDATE
---------
28-DEC-11
I would like to change the date format as below (date and time)
TO_CHAR(SYSDATE,'DD-M
---------------------
28-dec 01:09
I have a question regarding this date format change. Would there be any impact to database if i change the NLS DATE FORMAT as above example (date and time) at database level /system level ?
I have been asked to support an application which was developed in Forms 4.5. I have experience in Oracle database but I'm almost zero in any front end development.
As a first task I have been asked to remove a couple of field from one of the screen and then change the value of one of the field.
I have code as below
Scenario1
FOR i in (Select .................... from table1 Where id in(select id from Sample_table where status='Submit'))
Loop
.
.
.
End loop;
Scenario2
FOR i in (Select .................... from table1 Where id in(select id from Sample_table where status='Saved'))
Loop
.
.
.
End loop;
Scenario3
FOR i in (Select .................... from table1 Where id in(select id from Sample_table where status='Inprogress'))
Loop
.
.
.
End loop;Query is same here but only chage is where clause
Total length of this query is exceeding varchar2 length. so i cant use variable to hold this query and append where clause dynamically.
Do we have any other method to change where clause dynamically?
How to change the status of remote_login_password file!
View 14 Replies View RelatedThe source server (9.2) is /oracle/data/xxx the destination will be /u02/oradata/xxx how do I get the imp to change the paths.
I know you can't do db_file_name_convert, as that only works in dataguard with rman, and it looks like you can only set compatible down to 10.2, it's a very small DB, (5Mb) but it's a live license server DB, I need it intact.
I tried a straight import, but that just barfed with "IMP-00015: following statement failed because the object already exists:" on every statement.
EDIT: it's lying, the /oracle path doesn't exist, just FYI.
I need know the impact in my oracle database 10g R2, if i change root/oracle passwords in my Oracle RAC environment, my database using ASM and the nodes is in Red Hat 4.7.
View 1 Replies View RelatedWhat would be the steps involved if I will change the IP address of my Oracle GoldenGate target database?
Below are the steps that I thought of:
1. OGG Target: Stop the running change capture replicat and GG manager
2. OGG Target: Implement change process of IP address up to database startup
3. OGG Source: Stop the running data pump extract
4. OGG Source: Update data pump extract parameter RMTHOST pointing to the new IP address of the target
5. OGG Source: Start the data pump extract
6. OGG Target: start GG manager and the change capture replicat
Will it work or I missed a critical step?
OGG Source (PROD): Oracle 10g; Oracle GoldenGate 11.1.1; OS HPUX
OGG Target (Datawarehouse): Oracle 11g; Oracle GoldenGate 11.2.1.0.1; OS Linux