I can't seem to find the way to avoid ORA-2391 "exceeded simultaneous SESSIONS_PER_USER limit" when working with DBeaver 4.3.3.1. Everytime my session goes idle and I need to recconect I'm getting this error. I'm not sure if this is a DBeaver issue or I am missing something in my Oracle connection configuration. I already talked with DBA and he showed me that whenever I connect to my database DBeaver creates two sessions (two rows in table v$session) and when he tries to kill them, they hang out for a while until they finally go away and I can connect again. I am sure I don't have any other session opened with any other tool. My goal is to keep working with DBeaver since this tool has some amazing features, but without having to bother my DBA everytime I can't connect due to simultaneous sessions. If there could be any way to stop DBeaver from creating more than one session for just one connection, I will appreciate any help. PS:(I hope I'm clear with my question, English is not my native language)

I have came across this term ***Orphan locks*** and ***Orphan sessions*** and I really have *no idea* what that is. I have tried to *search* it on *Google* and I still haven't got any useful info/results regarding what it mean. *All I get till now is that orphan locks are some kind of session locks which results in deadlocks after executing query or queries.* (please correct me if I get it wrong) *Can someone please explain this a bit ? What are Orphan Locks ? Are they the same as Orphan sessions ? How do they form and what they have to do with sessions ?* p.s: I am still newbie to this so I apologise if it sounds all too basic.

I have a frontend written in MS Access that connects to a Postgres backend through ODBC. Sometimes the number of sessions reaches 100 and at that point I get an "ODBC call failed" error. I use PG Admin version 6.13 to administer the database. I noticed under Database activity -> sessions the following after some heavy usage on the database: There are over 70 of these in the session tab, which causes the application to crash. If I close the application, the entries also disappear. Running the SQL returns nothing, as there is no data type with the name "lo" in the table pg_type. The table pg_type is a system table, not one that I created. Any ideas how to fix this excessive number of session entries? And what is this "lo" type?

I am using "Zebra Session " as a replacement for PHP's built-in session management. Zebra Session stores sessions in a MySQL database, and uses "session locking" in order to ensure that data is correctly handled in a scenario with multiple concurrent AJAX requests. Session locking is implemented by using the SELECT GET_LOCK sql command within Zebra_Session's read function. Usually this works fine, but for the last few days these sql statements have been filling up my slow-log. Some of these statements have timed out at very inopportune moments. Here is an example statement from my slow log: # Query_time: 60.000150 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SELECT GET_LOCK("session_p494bdabuh56tddmbv19g8i9d7", 60); I realize that if the session had been previously locked the SELECT GET LOCK command will hang until the lock is released or the command times out, but I can't think of a situation where this would actually occur. I am using InnoDB - and here is the output of SHOW ENGINE INNODB STATUS: ===================================== 150224 11:55:16 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 38 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 72097 1_second, 72077 sleeps, 7094 10_second, 1251 background, 1250 flush srv_master_thread log flush and writes: 74597 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 18608, signal count 79708 Mutex spin waits 238350, rounds 331918, OS waits 6599 RW-shared spins 33512, rounds 348663, OS waits 10791 RW-excl spins 15495, rounds 99335, OS waits 1173 Spin rounds per wait: 1.39 mutex, 10.40 RW-shared, 6.41 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 19BC0896 Purge done for trx's n:o < 19BC0836 undo n:o < 0 History list length 999 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 144709, OS thread handle 0x7fdac5fc5700, query id 2569619 localhost root show engine innodb status ---TRANSACTION 0, not started MySQL thread id 140098, OS thread handle 0x7fdac6c36700, query id 2510580 38.105.174.189 root -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 101050 OS file reads, 767519 OS file writes, 210806 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 9.37 writes/s, 3.21 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 82794, seg size 82796, 1782 merges merged operations: insert 1718, delete mark 123, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249871, node heap has 1239 buffer(s) 11479.22 hash searches/s, 1909.58 non-hash searches/s --- LOG --- Log sequence number 53832600944 Log flushed up to 53832600944 Last checkpoint at 53832546459 0 pending log writes, 0 pending chkp writes 159378 log i/o's done, 2.74 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 10989076480; in additional pool allocated 0 Dictionary memory allocated 1152378 Buffer pool size 655360 Free buffers 542707 Database pages 111414 Old database pages 41146 Modified db pages 58 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 65, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 100882, created 10532, written 591116 0.00 reads/s, 0.00 creates/s, 6.45 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 111414, unzip_LRU len: 0 I/O sum:cur, unzip sum:cur -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 22747, id 140577666729728, state: sleeping Number of rows inserted 415334, updated 148965, deleted 29844, read 1412355001 0.55 inserts/s, 2.05 updates/s, 0.03 deletes/s, 31796.27 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT Here is the MySQL configuration file (my.cnf): [mysql] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] # GENERAL # user = root default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 # DATA STORAGE # datadir = /var/lib/mysql/ # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 1 query-cache-size = 2M query-cache-limit = 2M max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 512 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 256M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 10G # LOGGING # log-error = /var/log/mysql-error.log log-queries-not-using-indexes = 0 long_query_time = 3 slow-query-log = 1 slow-query-log-file = /var/log/mysql-slow.log I would appreciate some help in getting to the bottom of this.

We’ve been using SQL Server Maintenance Solutions for decades, but recently I’ve started encountering issues with missing log backups. There are no error messages, and the jobs are still scheduled, but when I check the job history, I see that it hasn’t run since [X time]. Upon further investigation, I found that the session executing the job is waiting on the OS (specifically on PREEMPTIVE_OS_GETPROCADDRESS). It also seems to be tied to the xp_create_subdir and xp_fileexist procs. The issue can be temporarily resolved by killing the process, though it won't clear until a restart. Once I do that, the jobs start running again. This issue happens anywhere from a few times a week to not at all for several months. I’d appreciate any help in identifying the root cause.

I have an Oracle instance to which several applications from different machines connect (http servers). Normally Oracle CPU usage is ~5-10% even on high server loads. But from time to time, one of http servers impose high (100%) CPU load on Oracle, which causes other oracle clients (in the case other http servers) timeout and do not work properly. Is it possible to limit CPU usage on a per process or per session basis? Or is it possible to limit CPU usage for clients connected to oracle from a special Machine/IP?

I want to drop all connections (sessions) that are currently opened to a specific PostgreSQL database but without restarting the server or disconnecting connections to other databases. How can I do that?

I have a heavy loaded application that holds connections persistently (it creates them on start and never releases). During the lifetime it creates and (re)uses prepared statements, which are stored in an LRU structure (so the least used are eventually released). And the question is: is it possible to obtain the number and other statistics of prepared statements for a given connection? There is nothing relevant on http://www.postgresql.org/docs/9.3/static/monitoring-stats.html so not sure if there are other interfaces to access postgresql stats. **UPD**: I probably was not clear enough: I need to obtain information using the normal DBA connection, since I cannot run arbitrary queries from the production software. For that very reason the pg_prepared_statements solution would not work.

I'm looking after a SQL server having long open sessions. There is no open transaction, or actively running query (requests) for this session. 1. What is the reason why a session may remain open? Is it because a transaction is opened and not closed? 2. What are the ramifications of having such an open session?

I've been working with the sys.dm_exec_sessions dynamic management view and I'm hoping to gain a better understanding of the last_request_start_time and last_request_end_time columns. From the documentation: > **last_request_start_time** datetime Time at which the last request on the session began. This includes the currently executing request. Not nullable. > **last_request_end_time** datetime Time of the last completion of a request on the session. Is nullable. From this, I'm interpreting that if there's currently an executing request (active request), the last_request_start_time will always be the start time of the currently executing request, and not the "last" request start time. If my understanding is correct, I'm having a hard time seeing the utility of these two columns. They don't always describe the same request, which seems counterintuitive. I've captured the following data on my server. In the second to last row, the last_request_start_time is 2023-10-22 14:11:20.667 and last_request_end_time is 2023-10-22 14:11:20.663.

+-------------------------+-------------------------+
| last_request_start_time | last_request_end_time   |
+-------------------------+-------------------------+
| 2023-10-21 15:20:54.410 | 2023-10-21 15:20:54.410 |
| 2023-10-21 15:20:54.410 | 2023-10-21 15:20:54.410 |
| 2023-10-21 15:20:54.410 | 2023-10-21 15:20:54.410 |
| 2023-10-22 14:11:20.667 | 2023-10-22 14:11:20.663 |
| 2023-10-21 15:20:22.690 | NULL                    |
+-------------------------+-------------------------+

Based on the above, does this imply that the current active request starts at 2023-10-22 14:11:20.667 and the last request ended at 2023-10-22 14:11:20.663? I appreciate your insights on this.

I've been working with several Dynamic Management Views (DMVs) in SQL Server to monitor transactions, specifically sys.dm_tran_session_transactions, sys.dm_tran_database_transactions, and sys.dm_tran_active_transactions. I understand that sys.dm_tran_session_transactions is used to match sessions and transactions, but I've noticed some inconsistencies. There are instances where I can identify a transaction from sys.dm_tran_database_transactions or sys.dm_tran_active_transactions, but I'm unable to find the corresponding session from the sys.dm_tran_session_transactions DMV. Why would this occur? Are there certain transactions, perhaps internal ones, that don't have corresponding session IDs? Any insight into this behavior would be greatly appreciated. Thank you in advance for your help. ## Update You can use below two queries to easily find a lot of such mismatched session IDs.

SELECT *
FROM sys.dm_tran_active_transactions at
    LEFT JOIN sys.dm_tran_session_transactions st
        ON st.transaction_id = at.transaction_id
WHERE st.session_id IS NULL;


SELECT *
FROM sys.dm_tran_database_transactions dt
    LEFT JOIN sys.dm_tran_session_transactions st
        ON st.transaction_id = dt.transaction_id
WHERE st.session_id IS NULL;

Let's say: - your application should use session IDs generated with a CSPRNG, which shall authenticate your user sessions - your system uses PHP 8.2 and MariaDB 10.6 Which of the approaches below performs better: Approach A (Index on hashed session ID): - the PK column of your sessions table is the one which holds the output of PHP's password_hash function, applied to a value generated via a CSPRNG. - upon every incoming request, you hash the session ID delivered by the client, and query the DB by that hash. Approach B (Index on decrypted INT session ID): - the PK column of your sessions table is a simple integer, which is encrypted before being delivered to your client. - upon every incoming request, you decrypt the session ID delivered by the client. - if the decryption fails, someone tampered with the token --> return 401 - if the decryption succeeds, you query by the accordingly obtained session ID. Is the performance hit you get from indexing hashes vs INTs negligible in such a case? I personally also like the fact that approach B provides a way to verify if someone tampered with the token, and avoids the DB-roundtrip if so. Do you see an approach to do so using hashes, as in approach A? **UPDATE** So let's say you have the following table:

CREATE TABLE sessdata (
  sid_hash BINARY(60) NOT NULL PRIMARY KEY,
  sdata JSON NOT NULL
);

With this, the simple insertion and subsequent attempted retrieval via hash using PHP and PDO fails:

$pdo = new PDO(
    dsn     : "mysql:host=localhost;dbname=$db_name;charset=utf8mb4",
    username: $db_user,
    password: $db_p,
    options : [
                  // Throw PDO Exception in case of any error
                  PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                  // Fetch the obtained result as associative array by default
                  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
              ]
);

$raw_binary_sid = random_bytes(32);

$hash = password_hash(
    password: $raw_binary_sid,
    algo    : PASSWORD_DEFAULT
);

// Keep this somewhere in your storage
$base64_urlsafe_nopadding = sodium_bin2base64(
    string: $raw_binary_sid,
    id    : SODIUM_BASE64_VARIANT_URLSAFE_NO_PADDING
);

$stmt = $pdo->prepare("INSERT INTO sessdata (sid_hash,sdata) VALUES (:hash,JSON_ARRAY())");

$stmt->execute(
    [
        'hash' => $hash
    ]
);

// Then in a separate PHP Session try to retrieve it, doesnt work 

$hash = password_hash(
    password: sodium_base642bin(
                  $base64_urlsafe_nopadding,
                  SODIUM_BASE64_VARIANT_URLSAFE_NO_PADDING
              ),
    algo    : PASSWORD_DEFAULT
);

$stmt = $pdo->prepare("SELECT * FROM sessdata WHERE sid_hash = :hash");

$stmt->execute(
    [
        'hash' => $hash
    ]
);

$stmt->fetchAll(); // empty array = no records found

What am I missing here? **UPDATE 2** Okay got the problem. PDO by default binds parameter to a query using the PDO::PARAM_STR data type / string data type. If I bind the parameter as PDO::PARAM_LOB data type instead during the insert and select operations, it all works. So in the end there are two final questions: 1. Is it correct that queries using the above-mentioned code will perform much faster than if I would index the hash column with the VARCHAR(60) data type? 2. Are truncated inserts impossible; e.g. will the 60-character long strings generated in PHP via password_hash('password',PASSWORD_BCRYPT) always match exactly the length specified via BINARY(60) ? Or is it wiser to use VARBINARY(60) to avoid danger of eventually padded / truncated values?

What is the difference between a connection and a session and how they are related?

I've decided to switch from PHP's native session management based on $_SESSION to use my own, due to many different issues I've had so far with PHP's possibilities, and also because I'm building an API that must be compatible with mobile app requests; so I cannot rely on $_SESSION anyway. I know that there are frameworks as symfony etc. out there for this, but using sth like that for these baby operations would be overkill. What I'm implementing at the moment is thus according to this logic ([source](https://redis.com/blog/json-web-tokens-jwt-are-dangerous-for-user-sessions/)) : The last point I'm wondering is that Redis / the linked article recommends to "Store Session ***and user info***" after a first successful authentication like a login or similar. However, I think that it would be much more beneficial to ***not do that***, and to only store the session identifier, kind of like a VIP card that will permit the client to launch requests in the secured area of the web app. My though was just that: - most of the concerned ***user info*** lies around in the DB anyway and in unencrypted form; most of it even in a single DB table. - I think it's generally a bad practice to store the same data in multiple locations of a DB, even if one is stored for "faster" or "facilitated" access in that sense. - The entire ***user info*** (= all data needed for all possible REST requests of the platform, gathered in one JSON) covers around 50 key-value pairs, and some of these values are encrypted, so I'd also need to encrypt the resulting ***user info***, resulting in huge en- / decryption operations upon every request (and page load, as also used for authentication), and an according additional column of sth like VARBINARY(600). - For a request, I never need more than 10 of those k-v data, and mostly only sth like 2 - 3. Given the considerations above, I wondered: is it not complete overkill and huge data transfer for nothing, to store the entire ***user info*** (sum of all user data I need in all of the REST requests of the platform) along the session identifier to mimic a session? Isn't it better to ONLY store the session identifier in the DB, and query only the needed ***user info*** when the actual request comes in (step 4a of the img above) ? Doesn't that make much more sense? I just feel like wasting so many resources if I store the entire ***user info*** along with the session identifier. As that also means that I'd be decrypting and sending a significant amount of payload in vain for different requests. So what is better practice here, store the ***user info*** at authentication (step 1b) or retrieve it when authentication to a request (step 4b) ?? To give you an idea; the resulting tradeoff would need to be made in between: - decrypting the data and sending much more ***user info*** than actually needed by the endpoint, with every request ***VS*** - doing at least 2 JOINS on PRIMARY KEYs upon every page load / request, in average, and eventually 1-2 smaller decryptions in that query, as well. In terms of performance, I'm clueless about what's smarter here.. And well another tradeoff is coding convenience, as not storing the whole session data set as such in the DB means one specific ***user info*** DB request per REST call.

Where can I find the list of characters which are valid for PostgreSQL configuration parameter names? By trial and error I found that '.' and '_' are valid, but ':' isn't. Quoting doesn't seem to help. set claims.custom_id to asdf; -- accepted set "claims.custom:id" to asdf; -- invalid configuration parameter name The PostgreSQL docs don't seem to cover this point - https://www.postgresql.org/docs/current/config-setting.html

In an effort to set a reasonable sessions_per_user profile setting for shared database accounts or other non-unique account (ie. an account an application uses to log into the database for all app user activity) I'm wondering if there's a view to look at or a script someone has created to see the maximum number of simultaneous sessions a user has had over some defined period of time. Note: some database are 11.2g and others are 19c if that makes a difference, hence the different version tags.

I need some guidance please. An application is going through migration and the processes seem to be quite cpu intensive. CPU utilisation on the server is very high - in the 90's. The headblocker session appears to be processing, as the 'Last Batch' column is current and no waiting time reported. There are however, a number of sessions which are in AWAITING COMMAND. [Microsoft doc](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-ver15) says not to kill these sessions: > Use KILL carefully, especially when critical processes are running. > You can't kill your own process. You also shouldn't kill the following > processes: AWAITING COMMAND I would appreciate some suggestions on how to proceed please. Thank you.

As an analyst I'm running multiple queries on our Oracle ERP via Power Query (Power BI). Now, I hit some form of session limit on my default profile's session limit. How do you evaluate how many sessions I use without accessing the Oracle database directly? Are they the number of tables you access in the FROM statements? Error message: >OLE DB or ODBC error: [DataSource.Error] Oracle: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit.

how Can I do to change SESSION_CACHED_CURSORS for all sessions? I found on google and oracle website: Alter Session... but I need to changed it for all the session and not only for one of them. How can I do it? UPDATE 1: SQL> alter session set session_cached_cursors=800 scope=both sid='*'; alter session set session_cached_cursors=800 scope=both sid='*' * ERROR at line 1: ORA-02248: invalid option for ALTER SESSION

I'm running a query which updates an Oracle table through Datastage. The query is taking a long time to execute so I want to examine the query/session on that Oracle table in the database and determine how the query is being executed. Could someone please advise how I can examine that active query/session in DB? I'm using DB visualizer tool.