Tag Archives: db

Getting List of Users and Roles on Oracle DB

This is a simple SQL statement to get you a list of all users and their roles in the Oracle DB: “SELECT * FROM DBA_role_privs;” And if you want to get the list of users related to a specific role, … Continue reading

Posted in Uncategorized | Tagged , , , , , , , , | Leave a comment

How to determine history of DB startup in Oracle 11g

SQL> SELECT STARTUP_TIME FROM dba_hist_database_instance ORDER BY startup_time DESC; STARTUP_TIME ————————————————————————— 29-SEP-13 03.43.07.000 AM 29-SEP-13 03.42.31.000 AM 29-SEP-13 03.12.45.000 AM 29-SEP-13 03.11.28.000 AM 28-SEP-13 11.56.35.000 PM 07-SEP-13 10.04.07.000 PM 6 rows selected. Credit goes to Abdulrahman Galal from Oracle.

Posted in Uncategorized | Tagged , , , | Leave a comment

How to manually modify DB initialization parameters by creating pfile from spfile

pfile is the initialization parameters of DB in plain text, while spfile is the binary one, so to be able to change it, you have to: Extract the pfile from the spfile: “create pfile=’my_path/file_name.ora‘ from spfile;” Shutdown the DB: “shutdown … Continue reading

Posted in Oracle | Tagged , , , , , , | Leave a comment

Managing the Oracle DB 11g Scheduler Windows (DBMS_SCHEDULER)

To display existing scheduler windows, you should query the “dba_scheduler_windows” table: e.g. SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,ENABLED FROM dba_scheduler_windows In my case, I needed to disable a window, so that it doesn’t run any scheduled jobs, so I had to run the following: … Continue reading

Posted in Oracle | Tagged , , , , , , | Leave a comment

Two Simple Methods to Move/Rename Oracle DB Data Files

First Method (must be done as sysdba): 1. First, shut down the database: “shutdown immediate;” 2. Rename and/or move data files to new destination using OS commands (e.g. mv for linux) 3. Startup DB in mount mode: “startup mount;” 4. … Continue reading

Posted in Uncategorized | Tagged , , , , , | Leave a comment

SQL Statement to get version of your Oracle DB

> sqlplus / as sysdba SQL> select * from v$version; BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production PL/SQL Release 11.2.0.2.0 – Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 – Production NLSRTL Version 11.2.0.2.0 … Continue reading

Posted in Uncategorized | Tagged , , , | Leave a comment

Get List of ASM Disks in an 11gR2 Database

select * from v$asm_disk

Posted in Oracle | Tagged , , , | Leave a comment