Alter Session On NLS_DATE_FORMAT Being Ignored
Feb 24, 2011
I'm issuing an alter session setting NLS_DATE_FORMAT in a C batch process right after the connection takes place. The format I specify is YYYYMMDDHH24MISS, this is the format used all over the process. In my development environment this works perfectly, but I've had problems in other environments.
CASE A development environment: The process works fine, $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set.
CASE B Test envirnonment 1: The process failed.$NLS_LANG=American_America.WE8ISO8859P1 $NLS_DATE_FORMAT environment variable is not set.For some reason the $NLS_LANG variable seems to have more weight than the alter session command.. why? The process works fine after setting $NLS_DATE_FORMAT to the desired format.
CASE C Test envirnonment 2: The process failed. $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set. Can't get it to work here. why?
View 1 Replies
ADVERTISEMENT
Jun 19, 2013
What is the difference between alter session and alter system?
View 2 Replies
View Related
Dec 20, 2010
how to change the default format of a number value using alter session statement?
I've a problem when I show a value like this "0.123456": the select statement returns ".123456". Is there any way to force a zero value before the character separator?
View 11 Replies
View Related
Nov 1, 2013
I was trying to execute below query inside a sql script which is called from a shell script on linux environment. EXECUTE IMMEDIATE 'alter session set events ''10176 trace name context forever'' ';
This thing works at our test environment. But giving error "SP2-0670: Internal number conversion failed" when tried on live(production) environment. What could be the reason? I found another way of writing such queries on net as follows, will it be useful? EXECUTE IMMEDIATE q'|alter session set events '10176 trace name context forever'|
View 1 Replies
View Related
Oct 15, 2012
I'm using Oracle SQL Developer and querying on date fields. I use this "alter session" statement to set my default date time format to get my datetime values. My question is, is there a way I can incorporate the following statement into my "select" query so that Crystal can read it? I use the statement below before my select statement to query datetime differences. Problem is, Crystal doesn't like the statement below.
Using TO_CHAR( ) and formatting my date fields that way doesn't work when I have to take the difference between date fields to the minute/second level. That's why I use the alter session command below, but I cannot seem to incorporate it with my select statement as a Crystal data source.
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
View 14 Replies
View Related
Sep 3, 2010
Is it possible to alter a session within Oracle forms as I get the following error when trying to compile the following block.
BEGIN
EXECUTE IMMEDIATE ( 'alter session set nls_date_format = 'DD-MON-YYYY''');
END;
Error: Encountered the symbol "IMMEDIATE" when expecting one of the following
The basic requirement is to validate a date field on the form against the current system date, but as the form is in format DD-MON-YYYY and the Database on M/D/YYYY I am struggling to compare dates successfully.
View 5 Replies
View Related
Sep 1, 2012
SQL> ALTER SESSION SET CURRENT_SCHEMA = CLA_T3;
Session altered.
SQL> select sys_context('USERENV','SESSION_USER') current_user,
2 sys_context('USERENV','SESSION_SCHEMA') current_schema
3 from dual
4 ;
CURRENT_USER
--------------------------------------------------------------------------------
CURRENT_SCHEMA
--------------------------------------------------------------------------------
CSR_ETL
CLA_T3
SQL> set linesize 300;
SQL> /
CURRENT_USER
----------------------------------------------------------------------------------------------------
CURRENT_SCHEMA
----------------------------------------------------------------------------------------------------
CSR_ETL
CLA_T3
SQL> create table cla_t3.test (r number, b char(2));
create table cla_t3.test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> create table test (r number, b char(2));
create table test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges
After Setting current schema to 'CLA_T3', I am unable to create table in cla_t3 schema.
View 5 Replies
View Related
Aug 25, 2013
I got this error when try to connect to the database using discover administrator or user edition
ora - 02248 : invalid option for ALTER SESSION
i can connect to the database with sqlplus with no problem.
View 4 Replies
View Related
Aug 23, 2012
We have a Data Source with min_pool_size (10) and max_pool_size (20). A Data Source is by default a connection pool. By starting a transaction we are retrieving a connection from the pool (i.e., opening it to retrieve data, perform queries, inserts and updates). Our application server is JBoss. An application workflow uses many transactions to build a product. The same connection is not used by the application for the entire workflow; but, it uses and returns them to the connection pool. We do not use Java syntax like "rs.close():"... this is performed by iBATIS.
On the Linux side when we execute a "ps" command (ps -elf|grep -i ora) we see all the Oracle processes. A further refinement of that command (ps -elf|grep -i local=no)shows a list of the "waiting" connections in the connection pool. The DB may be queried with the following syntax:
SELECT schemaname, sid, serial# FROM gv$session where schemaname = 'APP_USER' order by SID;
A list of connected sid and serial numbers is returned, identifying which connections are in use. From here we are able to force the connection to trace by executing the following:
exec dbms_monitor.session_trace_enable(249, 6595, TRUE, FALSE); ! 249 and 6595 being SID and Serial# from query above
There should be no need to execute the inverse, since the connection is returned to the pool when the transaction is committed or rolled back.
exec dbms_monitor.session_trace_disable(249, 6595);
We are trying to trace in order to use the Quest Benchmark Factory. Their instructions request the following syntax be applied to each session:
alter session set events '10046 trace name context forever, level 4';
and again, the inverse should not be necessary.
alter session set events '10046 trace name context off'
When it became too cumbersome to alter each session as it appeared, we issued an "alter system" to monitor (trace) everything. The trace files filled the disk, and four hours of testing was stopped two hours in. Doing a system level trace is probably not a good idea.
My first inclination was to create a post-logon trigger to set trace in the session; however, these connections, coming from an JBoss connection pool, do not logon each time, and I presume that they are not all the "same session".
We opened a support ticket with Quest last Friday and do not have an answer yet. This was the third ticket with them, the first to get Benchmark Factory installed (the original installer did not work). The second ticket was to setting up a shared directory on Linux with a folder on Windows, a setup configuration required by their tool. The third ticket to address this issue.
They needed to contact "the developers" to answer the last two questions. Their latest suggestion is to fix ticket two so we "won't need to trace" anything. How do I set trace in these connections?
View 6 Replies
View Related
Feb 2, 2011
We have nld_date_format set to dd-mon-rr at database level
While querying I am using to_date('08-SEP-81','dd-MON-yy') which is not giving me the desired results However using the date directly or with to_date('08-SEP-81','dd-MON-rr') is giving correct results
I was expecting using to_date('08-SEP-81','dd-MON-yy') will override the database or instance settings and will return the results.
SQL> set pages 100
SQL> select ename,hiredate from emp;
ENAME HIREDATE
---------- ---------
SMITH 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
[code]....
14 rows selected.
SQL> select ename from emp where hiredate>to_date('08-SEP-81','dd-MON-yy');
no rows selected
SQL> select ename from emp where hiredate>'08-SEP-81';
ENAME
----------
MARTIN
SCOTT
KING
[code]....
7 rows selected.
SQL> select value from nls_instance_parameters where parameter='NLS_DATE_FORMAT';
VALUE
SQL> select value from nls_database_parameters where parameter='NLS_DATE_FORMAT';
VALUE
[code]....
7 rows selected.
View 4 Replies
View Related
Nov 19, 2012
I have an urgent requirement to kill an existing session if a new session starts for the same user. I have been reading lot of blogs and posts on the above topic, but could clearly tell me how to do it.
I thought of putting a process in 101 page when login button is pressed to catch this and kill the old session.
View 7 Replies
View Related
Oct 23, 2012
We are using Apex 4.0/Oracle 10gR2/ Hp-Ux, We noticed that there is a process Running in Two Sessions that seems running since 3 Hours using 60% CPU,** 30% given below sql it executes in two Sessions!
declare function x return boolean is begin -- if instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY')>0 then
declare
l_position number := 0;
begin
loop
l_position := instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY ',l_position + 1) ;
exit when instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY ',l_position + 1) = 0 ;
[code]....
View 4 Replies
View Related
Aug 28, 2013
Instead of giving permission directly to v$session and gv$session to a developer, Is there any other way to do the same?
View 3 Replies
View Related
Jan 22, 2013
I have issue of TABLESPACES on test instance. Tablespace files are TEMP1,TEMP2 and APPS_UNDOTS1. Initially TEMP1 and TEMP2 were of 4 GB but now the have grown to 32 GB each respectively. Resulting an occupied space of 64 GB on test server. I want to reuse that space on test instance as those tablespaces never crosses used space of 1%. Reason of this problem was my DBA set AUTOEXTEND as ON for tablespaces.
Attached File(s)
1.jpeg ( 1.8MB )
Number of downloads: 1
2.jpeg ( 1.6MB )
Number of downloads: 1
3.jpeg ( 1.52MB )
Number of downloads: 0
View 1 Replies
View Related
May 4, 2010
is there any way to alter only max value in sequence without specifying the max value.
i know we can alter it like :
-- Alter sequence
alter sequence TEST_SEQ
maxvalue 99999999;
can we alter it without providing the max value and let oracle choose default value for the same, same as we can do it when creating a new sequence.
View 5 Replies
View Related
Oct 1, 2010
I have a table Employee which gets updated and Inserted very frequently. Wanted to increase the PCTFREE value for this table
select table_name,pct_free,chain_cnt from user_tables
where table_name='EMPLOYEE'
EMPLOYEE NULL 0
is the output. I altered the pctfree like below
ALTER TABLE EMPLOYEE PCTFREE 30
and I can see Table altered but the value is not reflecting in above command again. Where else I should alter the value and cross check that value is changed.
View 3 Replies
View Related
Sep 13, 2012
I have a partition table which is partition on date_loaded column, can i alter the partition key column to orig_date_loaded column
View 9 Replies
View Related
Jul 26, 2011
if a user have alter table gant but could not alter .. what additional grant it need
SQL> alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3));
alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3))
*
ERROR at line 1:
ORA-00942: table or view does not exist
View 11 Replies
View Related
Sep 28, 2012
I would like to rename tablespace A to B , but when I do it tables after rename tablespace have columns tablespace_name.dba_tabkles in A . Is possibility to change it into without move ?
View 6 Replies
View Related
May 20, 2010
i have a database in which some tables. Now i want to alter column by using this query.
ALTER TABLE SALE_INVO_DETAIL_COPY
MODIFY ("QTY" number(5,2))
this column properties is QTY NUMBER(5)
now i want to convert it into QTY NUMBER (5,2)
View 2 Replies
View Related
Jul 22, 2010
I created a DDL trigger which manipulates columns of a table after an "alter table" statement.
For us this was all the time
"alter table XXX add NEW_COL varchar2(20)"
or
"alter table XXX modify NEW_COL varchar2(20)".
Unfortunatly we have now a requirement which statements "alter table XXX drop NEW_COL varchar2(20)".
My trigger works fine - you see no problem directly after statement execution but the table is then in an invalid status.
The connection will be destroyed after you tried to see the data of this table.
Also the export (creating a dump) will not work.
The problem I see is that I the database will do a "modify" on a column which has internal the status "dropped".
How I can get the information what kind of alter table statement will be executed?
Or is there any chance to select only columns from USER_TAB_COLUMNS without the "dropped" flag?
create or replace trigger TRIGGER
after alter
on SCHEMA
declare
vCharSet NLS_DATABASE_PARAMETERS.VALUE%TYPE;
begin
-- Select all columns of a table
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH
from USER_TAB_COLUMNS
where TABLE_NAME = vTABLE_NAME; ...
-- Loop for all columns from our select
...
vSTATEMENT := 'alter table ' || vTABLE_NAME || ' modify ' || VOBJECTREC.COLUMN_NAME || ' ' || VOBJECTREC.DATA_TYPE || '(' || vNEW_DATA_LENGTH || ')';
--For example:
-- alter table XXX modify NEW_COL varchar2(20)
execute immediate vSTATEMENT;
exception
...
end;
/
View 11 Replies
View Related
May 4, 2011
I have a table with usernames and passwords. The passwords are stored in plaintext. I would like to issue an ALTER command on the password field to store a hash instead, and then repopulate those fields with an encrypted version of the plaintext passwords that were there before.
I would prefer to do this in a procedure, as I am going to perform it in a test environment first, then eventually in the production environment.
View 2 Replies
View Related
Dec 31, 2005
When I try to compile a procedure with this command:
alter sequence myschema.seqmessages increment by 100;
The error says "encountered symbol "ALTER" when expecting...
Is there another way to alter a sequence from a procedure? In this case, I am altering a sequence in another schema that has granted the alter and select privileges for the sequence.
View 7 Replies
View Related
Aug 9, 2010
Requirement: I need to create a Function to allow users to change their own password when they are logging in to an application. Also, I would prefer to not use the ALTER command.
View 12 Replies
View Related
Apr 18, 2011
I have created the following partition. but i want to alter in future . how to alter the reference partition. is it avilable in reference partition.
CREATE TABLE EMD_FILE_LOAD_DETAILS_PR
(
FILE_REFERENCE_ID ,
FILE_NAME ,
FILE_TYPE ,
FILE_GROUP_NAME ,
[Code]...
View 1 Replies
View Related
Apr 14, 2012
I have a table PR in that some data is there for instance, My Mr_NO was Char(11) I modify MR_No to Char(13)My Table Structure now is:
MR_No Char(13),
Mr_Date Date
My Previous data is MR_NO=APN00209085
I want to add two 00 after alter the table I want my result data like APN0000209085.I am updating through this command
update PR set Substr(MR_No,4,2)='00'
ERROR at line 1: ORA-00927: missing equal sign
Result I want is APN0000209085
View 13 Replies
View Related
Mar 23, 2011
I need to Modify the column(MSGID) data type from RAW to BLOB for a Queue Table, I'm getting the following error.
BANNER
---------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE11.2.0.2.0Production
YUV >
YUV > DESC PDA_REPORT_MESSAGE_QTAB
Name Null?Type
----------------------------------------------------- -------- ------------------------------------
Q_NAME VARCHAR2(30)
MSGID NOT NULL RAW(16)
YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB)
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype
YUV >
YUV >
YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB)
*
ERROR at line 1:
ORA-24005: Inappropriate utilities used to perform DDL on AQ table PDADBA.PDA_REPORT_MESSAGE_QTAB
YUV >
View 10 Replies
View Related
Jan 8, 2013
I can use trigger to monitor ALTER TABLESPACE ddl statement against a particular tablespace in a schema.
CREATE OR REPLACE TRIGGER HOT_MAIL_DDL_CHANGE
AFTER ALTER TABLESPACE <tbs_name>
ON SHANNURA.SCHEMA
BEGIN
INSERT INTO HOT_MAIL_DDL_AUDIT_LOG VALUES
(SYSDATE,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
ORA_SYSEVENT,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME
);
END;
/
I think, line no.2 isn't a valid statement. Or can I use AUDIT instead - in fact, I want to monitor/audit only ALTER TABLESPACE ddl statement and that too for a particular tablespace only.
View 3 Replies
View Related
Jan 4, 2011
My tablespace contains two datafiles dfile1.dbf and dfile2.dbf on D drive of my filesystem. Now i copied these dbf files on pasted it on a location in E drive. Now i want my tablespace to use dbf files pasted in new location.
Will i have to Alter Tablespace Add Datafile with Reuse clause
View 2 Replies
View Related
Mar 5, 2012
My user tablespace has been full and just I create new tablespace like User_Tablespaces.
I want to Alter Default Tablespace and Quota define of all existing user on User_Tablespaces.
How we do in a SQL Query or PL/SQL query that can all existing users has been alter at a time in a single query.
View 2 Replies
View Related
