Is it possible to know how many times a user connected to an Oracle database?
If possible where we can find this information?
Is it possible to know how many times a user connected to an Oracle database
oracle
Related Solutions
I had no problems with a VirtualBox 4.14 Windows 8 preview VM and x64 Oracle 11gR2.
My VM uses 40 GB Hard Disk. While installing it downloaded .Net Framework 3.51 I started with a fresh Windows 8 VM in VirtualBox 4.14 and installed guest additions and run Windows update once. Afterwards I installed Oracle and got the items below on my Start page.
I find the databasefiles of my new database Bk_W8 in C:\app\berndk\oradata\Bk_W8. That is the location I would use for additional tablespaces.
The rest can be done by scripts which can be executed by sqlplus from this machine or from another client and is not Windows 8 specific.
Edit:
By right-clicking on the item on the start page, via advanced | open file location I found the path to the start menu:
C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Oracle - OraDb11g_home1\Konfigurations- und Migrations-Tools
I guess you will find
C:\ProgramData\Microsoft\Windows\Start Menu\Programs
the other is specific for a German Oracle installation.
To get access to the hidden C:\ProgramData folder see this link showing how to access folder options in Windows 8
Final hint: My VM currently uses 23 GB for the virtual hard disk.
OK I'm going to retry this with the Oracle Database Express Edition 11g Release 2 (September 2011). But that takes some time.
Installation done: The is the Web interface of Oracle 11g XE in Windows 8
For the question of creating addition databaseinstances: from this question on SO we learn, that it is not possible.
Creating tablespaces and users can be done using sqlplus. Which I found at C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe.
Now going to remove that Virtual machine. I think there are better ways to learn Oracle.
User creation:
select dbms_metadata.get_ddl( 'USER', 'PHIL' ) from dual;
Default role:
select dbms_metadata.get_granted_ddl( 'DEFAULT_ROLE', 'PHIL' ) from dual;
System grants:
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'PHIL' ) from dual;
Object grants:
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'PHIL' ) from dual;
Role grants:
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'PHIL' ) from dual;
Quotas:
select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'PHIL' ) from dual;
If any of the above have no output, you'll get an exception that looks something like this:
SQL> select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'PHIL' ) from dual;
ERROR:
ORA-31608: specified object of type TABLESPACE_QUOTA not found
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5991
ORA-06512: at line 1
no rows selected
SQL>
Then do a search and replace on the output to change the username.
Best Answer
If audit is enabled, you can check DBA_AUDIT_SESSION/USER_AUDIT_SESSION (More info about auditing - Audit doc). Another option is to check dba_hist_active_sess_history - you will need to write a query that counts distinct serials/sessions to get rough number of user logins assuming user did anything during the session. Also, you won't be able to go too far in the past with dba_hist_active_sess_history - it's a view based on tables populated by AWR which automatically deletes old records (as far as I remember default retention period is 1 week ).