Tag Archives: sqlplus

How to switch from one user to another in sqlplus

for example, you’re connected as sysdba using “sqlplus / as sysdba”, all you have to do is type “connect”, then enter the desired username and password, and there you go!

Posted in Uncategorized | Tagged , , , | 1 Comment

How to get current logged-in user in sqlplus

select user from dual;

Posted in Uncategorized | Tagged , , | 1 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

How to compile all Oracle DB 11gR2 Invalid Objects

Login to SQLPlus Run the following statement: “@?/rdbms/admin/utlrp.sql”

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

Bash Script to run a sql file (Draft) – Part 1

#!/usr/bin/bash # This script calls the Ad_1.sql script and spools the output with date stamp to a log file# Update: You MUST add your environment variables one way or another, e.g. look below export ORACLE_BASE=/oracleexport ORACLE_HOME=/oracle/OraHome_1PATH=${PATH}:${ORACLE_HOME}/bin; export PATHexport LIBPATH=${LIBPATH}:${ORACLE_HOME}/lib32:${ORACLE_HOME}/lib if … Continue reading

| Tagged , | Leave a comment

Putting the Current Date in a Spool File Name in sqlplus

 Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command: column dcol new_value mydate noprintselect to_char(sysdate,’YYYYMMDD’) dcol from dual;spool &mydate.report.txt— my report goes hereselect … Continue reading

| Tagged | Leave a comment