PL/SQL :: Alter Session Query On Linux?

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


ADVERTISEMENT

SQL & PL/SQL :: Difference Between Alter Session And Alter System

Jun 19, 2013

What is the difference between alter session and alter system?

View 2 Replies View Related

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 View Related

SQL & PL/SQL :: Alter Session Number Format?

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

PL/SQL :: Alter Session In Select Statement?

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

Forms :: Possible To Alter A Session Within Oracle Form

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

PL/SQL :: Cannot Create Table After Change Schema With ALTER SESSION

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

Reports & Discoverer :: ORA-02248 - Invalid Option For ALTER SESSION

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

Client Tools :: Alter Session Of Connections In JBoss Connection Pool

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

SQL & PL/SQL :: Alter Column By Using The Query

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

Query Without Login To Oracle database From Linux OS?

Aug 21, 2013

can we query this without login to the oracle  database from linux os?database is up and running . /apps/oracle/product/11.2.0/dbhome/bin/sqlplus system/passwd select name from v$database;

View 5 Replies View Related

SQL & PL/SQL :: Query On V$session - Status Column

Dec 2, 2011

If a session runs a query, the status will be inactive after completing the database call. I know this concept. But I just want to know what will be session status, if it running a procedure contains many select queries, DML quries and loop? Whether it will be active until procedure completes or it will be switching active and inactive?

View 3 Replies View Related

Application Express :: Sql Query To Fetch Session ID

Feb 18, 2013

I am to trying to fetch session id of a previously submitted process of a search button......so that i can display the search results in a different page.....so is there any sql query or pl/sql procedure to fetch the session id.

View 1 Replies View Related

SQL & PL/SQL :: ORA-01001 - User Refresh SSRS Session And Again Run Query

Jan 11, 2012

We have a package procedure called from SSRS , which returns a REFCURSOR to the SSRS.Sometimes it throws the error. The user Refresh the SSRS session and again run the query , the error does not happen

Following is the error number

ORA-01001: invalid cursor

We have not used any static cursor , so this is not happening because we have opened a cursor and not closed it.The Cursor used is REF CURSOR and the output parameter is declared as

---------------
p_ref_cursor OUT SYS_REFCURSOR
----------

The Procedure returns the Select query in the REF CURSOR.

View 7 Replies View Related

Server Administration :: Query To Find Hostname For Oracle Session

May 20, 2010

How I can find the hostname for an oracle session id?

View 4 Replies View Related

Application Express :: Kill Current Session When Another Session With Same User Starts

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

Application Express :: (X) Process Taking 60% CPU In Two Session (30% Each Session)

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

Grant Select On V$session And Gv$session Directly?

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

Way To Alter Tablespace

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

SQL & PL/SQL :: Alter Sequence With No Max Value?

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

SQL & PL/SQL :: How To Alter PCTFREE

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

SQL & PL/SQL :: Alter Partition Key Column?

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

SQL & PL/SQL :: Grant Alter Table?

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

Alter Tablespace Rename

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

SQL & PL/SQL :: DDL Trigger After Alter Table

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

Alter Table Column And Its Contents?

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

Alter Sequence In Procedure Will Not Compile?

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

SQL & PL/SQL :: Change Password Without Using ALTER Statement?

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

SQL & PL/SQL :: How To Alter Reference Partition (add Or Modify)

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

SQL & PL/SQL :: Update Column - Add Two 00 After Alter Table

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







Copyrights 2005-15 www.BigResource.com, All rights reserved