Tag Archives: db

Getting List of Users and Roles on Oracle DB

Posted on January 24, 2014 by SoCRaT

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

Posted on September 29, 2013 by SoCRaT

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

Posted on January 19, 2013 by SoCRaT

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)

Posted on November 25, 2012 by SoCRaT

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

Posted on November 7, 2012 by SoCRaT

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