Server Administration :: Displaying Session Level Parameters
Aug 9, 2013
A way to show session parameters? Such as
CONSTRAINTS
USE_STORED_OUTLINES
ROW ARCHIVE VISIBILITY
CURRENT_SCHEMA
I can detect the CURRENT_SCHEMA with a query against the user env context, but I can't find any of the others there. Could there be an issue with these values being stored in PGA, and therefore not visible though any regular views? I did find an article that showed a query against an x$ data structure which showed something for different settings of CONSTRAINTS, but I can't find it again.
View 4 Replies
ADVERTISEMENT
Sep 26, 2011
I just upgraded my version of my oracle from 9 to 11.2g.When I tried to execute some queries in PL/SQL i keep getting an
annoying error : "ORA-01843 not a valid month."
I realized that if I execute the following query:alter session set NLS_DATE_LANGUAGE=AMERICAN;before every query that I want to execute, then it's executed fine.
I preformed the next queries:
1. select * from nls_database_parameters;
2. select * from nls_instance_parameters;
3. select * from nls_session_parameters;
and I saw that the fields in the "nls_session_parameters" vieware not derived from "nls_database_parameters" "nls_instance_parameters".
How can I derive the parameters from the instancedatabase view into the session view?I want that the "nls_date_language" parameter will always be 'AMERICAN'.
View 3 Replies
View Related
Jun 9, 2010
we have a software which keeps a check on v$session table so that a client can not have more than one sessions running simultaneously with the server. But if a client program terminates unexpectedl, it's session information stays in the v$session table, which prevents us from opening a new instance of the client program. Using
ALTER SYSTEM KILL SESSION 'SID, SERIAL#'
doesn't work since it takes ages to rollback and release the locks. And the session stays there with a 'KILLED' status.I want this session out of the v$session table and instantly.
View 4 Replies
View Related
Nov 22, 2010
How Can We Get Hardware Information In Oracle Database 8i for example harddisk size,volume,processor etc. i want database run only specified harddisk
View 1 Replies
View Related
Nov 3, 2012
While renaming the datafile.
1) ALTER TABLESPACE .... OFFLINE;
2) CHANGING THE DATAFILE NAME IN OS LEVEL;
3) ALTER TABLESPACE .... RENAME DATAFILE 'FILE_OLD' TO 'FILE_NEW';
4) ALTER TABLESPACE .... ONLINE;
In the above steps, I HAD FORGOTTON TO FOLLOW THE 2ND STEP.
At the step of 3rd, i got error message, now i am not able to change the name in OS level Also.
View 19 Replies
View Related
Jul 26, 2010
While creating the index we are getting the error "ORA-00603: ORACLE server session terminated by fatal error".
We have the space in tablespaces and also in the file systesm.
View 13 Replies
View Related
Mar 19, 2013
I recently discovered that there was a difference in my QA and prod environments, which I have since rectified.
SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE 1
SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
I found a script that allows me to compare values from v$parameter
set pagesize 1000
col name format a28
col local format a20
col remote format a20
select local.name, local.value local,
remote.value remote,
[code]....
Is there other SQL code or another methode out there that would find differences in my DB's such as the method_opt setting, which don't appear in v$parameter.
View 4 Replies
View Related
Jul 30, 2013
I just want to write some data in a particular table,But I dont want it to be archivedSo is it possible to disable archiving in session level Oracle 11g Rel 2
View 3 Replies
View Related
Aug 23, 2011
My disk is completely full so i decided to drop temporary tablespace and create a new one but unfortunately the query to drop temp tablespace hang in between to whic I come to know that there were some sessions which were using it for sort purpose.
check out the queries below with results:
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
2 a.username,a.osuser, a.status
3 FROM v$session a,v$sort_usage b
4 WHERE a.saddr = b.session_addr;
[code]....
Was able to killed three of the four sessions but not the last one.
View 20 Replies
View Related
Jun 11, 2007
I would like to log session activities. I would also like to be able to check if the session for which an activity has been logged is still active. The problem is, what do I need to uniquely identify a session?
DBMS_SESSION.UNIQUE_SESSION_ID - is explained as: unique for all sessions currently connected to this database. Meaning that if I restart the DB or connections are closed and opened, it might not be unique anymore, right?
SYS_CONTEXT('userenv','sessionid') - returns AUDSID, but is this guarantied to be unique and not been reused, forever? What I do not want to happen is, that I use a session identifier, which might reappear.
My best guess is currently:
SYS_CONTEXT('userenv','sessionid') + logon_time (from V$SESSION)
This combination should be really unique and should never reappear, right?
View 11 Replies
View Related
Apr 17, 2011
Can I extract data from our Oracle server regarding the no. of sessions per day? But the problem is that our Boss wants a previous dates from April 11-17, 2011.
View 4 Replies
View Related
Oct 7, 2010
When trying deploy an mapping, the database link get this error:
ORA-06550: PACKAGE BODY, line 0, column 0:
ORA-04052: error occurred when looking up remote object db1.someC@db1@LOC_db
ORA-00604: error occurred at recursive SQL level 1
ORA-12170: TNS:Connect timeout occurred
I can use tnsping towards the db. I can use
elect * from v$instance@db1@loc_db
View 1 Replies
View Related
May 5, 2010
I have an application in Form 6i and I have implemented expiration of Session-level database of profiles, the problem I have is that I do not know in which time expired the session because it shows me no message and the application is open, try to do something and begin to get errors .
View 2 Replies
View Related
Jun 13, 2012
I've been searching the web for examples of how to run a trace.It's needed for a session different then current in trace LEVEL 4 (I need the bind variables values in the trace).
Unfortunately, I couldn't trace with DBMS_SUPPORT.START_TRACE_IN_SESSION, I understand that this is because it was only introduced in Oracle 11g.
how can i trace a session in level 4 on Oracle 10g for another session?
View 8 Replies
View Related
Sep 1, 2010
Three databases: A, R1, R2. Database A is local and the other two databases are remote. Database A has one db link to each remote database (dblinkR1 and dblinkR2).
In some of my queries I need to get data from the remote databases, but the remote database to use depends on the application user, so, I would like to dynamically use one of the database links.
Something like this:
1 � user logs into the application;
2 � based on the user credentials the application defines the db link to use;
3 � query data using the defined db link.
(for example)
SELECT a.col1, a.col2 FROM table_A@dyndblink a ORDER BY a.col1;
@dyndblink would be a "pointer" to dblinkR1 or dblinkR1.
Is there a way to dynamically define the database link to use (at a session level)?
View 2 Replies
View Related
Oct 26, 2011
I created a view with parameters l_id and l_name, how can i find them in oracle view?
Create Table tb_test
(
Id Number,
Name Varchar2(64)
);
Create Or Replace View vw_tb_test(l_id,l_name)
As
Select Id,Name From tb_test;
View 3 Replies
View Related
Sep 15, 2011
What is the function by specified the size_clause of parameters method_opt?
dbms_stats.gather_table_stats
method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size_clause]
column|attribute [size_clause]
[,column|attribute [size_clause] ... ]
size_clause := SIZE [integer | auto | skewonly | repeat],
where integer is between 1 and 254
View 16 Replies
View Related
Jul 11, 2011
I can not change the parameters RESOURCE_MANAGER_PLAN, at first, I set the parameters to DAYTIME,but when I restart my db,the parameters hold old values named MAXCAP_PLAN. Why?
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
[code]....
View 5 Replies
View Related
Mar 18, 2013
I need to ask about PGA memory. Is this memory for every user or every session
i.e. PGA 200M
I have 2 users with 300 connection with one of them. Is 200M for 300 connection or for every connect.
View 2 Replies
View Related
Nov 22, 2012
when I was analyzing high CPU utilization issue, I saw that the most of the top PID's were INACTIVE in database. But it was utilizing more than 4% CPU. how it is utilizing CPU without doing any work in database?
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
24013 oracle 2167M 1581M sleep 53 0 0:01:53 20% oracle/1
13260 oracle 2167M 1580M sleep 30 0 0:01:16 14% oracle/1
24399 oracle 2167M 1582M sleep 59 0 0:03:01 4.2% oracle/1
11509 oracle 2178M 1593M sleep 59 0 0:02:10 2.8% oracle/11
[code].....
View 3 Replies
View Related
May 22, 2012
One user complained that, his session is closing automatically when his session exceeds exactly 01 hour 1 minute.
default profile is assigned for this user. I checked the idle_time value of this default profile. it is UNLIMITED only. What else I need to check to find out is there any timeout parameter has been set or not?
View 6 Replies
View Related
Feb 6, 2010
Which is the best way to kill a session?
alter system kill session sid,serail#
or
kill -9 spid
or
orakill sid,thread
I am using unix,windows operating system.
View 3 Replies
View Related
Apr 23, 2013
How to kill of a killed session?
I had killed inactive session using alter system kill session 'sid,serial#';
after that that sessions marked as killed and still get in v$session.
Then i try to get spid so that i can killed then from os command kill -9 spid
but when i try to get that join with v$process, no row select.
select * from v$session where username ='CAS' and status ='KILLED'
return 205 records.
select p.*,s.username,s.program,s.machine,s.status from v$process p,v$session s
where p.addr=s.paddr and s.status='KILLED'
return no record.
View 24 Replies
View Related
Nov 22, 2010
what does 'SNIPED' status in v$session mean? It is killed/ clear from instance automatically or need to kill manually for releasing the resource.
View 3 Replies
View Related
Feb 22, 2012
At my prod instance one sql query ran for about 9 hours.Now the sql query completed with success.Suddenly our ops team want to know the which sql query was running for 9 hours.As the query got success no record is being found at session level.
I know the SID.How Do I get the SQL query ?
View 4 Replies
View Related
Aug 31, 2010
When I was going through the Enterprise Manager Grid Control, I found an error due to which I looked at the trace file and it said:
ORA-07445: exception encountered: core dump ACCESS_VIOLATION unable_to_trans_pc PC:0x7C81BD02 ADDR:0x49444E49 UNABLE_TO_READ]
I searched and found that it has something to do with the SGA parameters. I saw that the shared_pool_size and the sga_target paramters are set to 0...Also there are certain SQLs hanging at some point. I thought I should change the above mentioned parameters.
My question now is, can I use the Alter System statements from the SQL Plus to change these parameters, and do they change immediately or do I need to reboot the Oracle instance for those changes to take effect? I would like to do:
alter system set sga_target=400m;
alter system set shared_pool_size=200m;
would these work and take effect immediately?
View 5 Replies
View Related
Jun 8, 2010
I am using TOAD to monitor user sessions. I have a user who uses nearly 80% of Overall resources in the mornings. He calls me daily and say that his work is done. But even then the TOAD shows that he is using same resources for some time.
My question is can i kill the session once he confirms his work is done OR do i need to wait till the redologs are cleared.
View 9 Replies
View Related
Aug 5, 2011
when i modify a column of table tb_hxl_user,just as flowing:
Alter Table TB_HXL_USER Modify CREATE_DATE Not Null
now i select the table TB_HXL_USER,just as flowing:
select * from TB_HXL_USER;
it is hang,and i found the wait event is library cache lock in v$session_waits,why?how can i avoid this.
View 8 Replies
View Related
Mar 23, 2010
How to Close All open cursors in other session.
NAME TYPE VALUE
------------------------------------ ----------- ---------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 500
session_cached_cursors integer 50
Ex. as above we have 500 max cursor
seesion show 499 and now we need to flust all open cursors
View 9 Replies
View Related
Mar 4, 2010
I am using 10.2.0.4 oracle database on Solaris 10. We are running some Peoplesoft upgrade on one of the database from last 6-7 hours. The status of the session is INACTIVE from couple of hours. And the sql_id is null for these sessions.
select sid,serial#,STATUS,to_char(logon_time,'DD-MON_YY HH24:MI:SS') from v$session where username like '%&usern%' ORDER BY 4 DESC;
533 2234 INACTIVE 04-MAR_10 01:59:02
525 5919 ACTIVE 04-MAR_10 01:54:50
534 4186 INACTIVE 04-MAR_10 01:05:15
520 2320 INACTIVE 04-MAR_10 00:29:44
[code].....
While when i see the current sql from the v$active_session_history, it keep on changing after every 10-15 minutes. Using below query to find the sql_text for the session from v$active_session_history.
select sql_id, sql_text
from v$sql
where sql_id in (
select sql_id
from v$active_session_history
where session_id=511
[code].....
We have not faced issue like this during Peoplesoft upgrade. what could be the reason that the status is not changing while the base sqls keep on changing after every 10 minutes.
View 8 Replies
View Related
