If you run this command, Oracle waits until all users have committed or rolled back any outstanding transactions before closing the database. To test this out in Oracle 12.1, I tried to close a database from a session with an outstanding transaction. This failed with an ORA-01097. Once I had committed the transaction, I was able to close the database successfully:SQL> show user
USER is "SYS"
SQL> create table tab1(col1 number)
Here are a couple of examples with the USER keyword, which I tested on Oracle 11.2. You can use it after the SHOW command or in a SELECT statement as follows. It returns the name of the user running the current session:
SQL> conn fred/bloggs
SQL> show user
USER is "FRED"
SQL> select user from dual
This was tested on Oracle 11.2. I had a session in the middle of a long-running operation:
SQL> conn user1/user1
SQL> exec dbms_lock.sleep(3600);
I tried to kill it like this but Oracle gave me an ORA-00031 instead:
SQL> select sid, serial# from v$session
2 where username = 'USER1'
SQL> alter system kill session '7,62'
I tried to do a FAST refresh of a materialized view in Oracle 11.2 but it failed with an ORA-12004:
ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for materialized view
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 2
I noticed this in an Oracle 188.8.131.52.0 database. DBMS_STATS failed with an ORA-00600 and the first argument was . On investigation, it seemed to have something to do with the fact that the table had a function based index:
SQL> SELECT COUNT(*) FROM DBA_INDEXES