Archive | SQLPlus

Oracle 11g SQL Error Logging

We’re working on the installation scripts for an internal PL/SQL applications. Multiple scripts are called from the main one. To capture errors from script execution, normally the spool syntax will be used in the scripts to pipe out all executions into log files, and then later the deployment team members will examine them using find/search […]

Continue Reading 5

Easy Connect (EZCONNECT) Naming Method with Instance Name

Oftentimes I use the Easy Connect (EZCONNECT) to connect to a new database just to quickly verify database connectivity. The syntax I usually use is as follows: CONNECT username/password@[//]host[:port][/service_name] CONNECT scott/tiger@host1-vip:1522/RACDB In RAC, this will connect to whatever instance based on the service’s load balancing goal. I just learned that with the 11g net services […]

Continue Reading 3

SQL and PL/SQL reserved words

I found this tip from forum discussing about Oracle’s reserved words. You can use it to quickly view reserved words for both SQL and PL/SQL. SQL> help reserved RESERVED WORDS (PL/SQL) ———————– PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used for identifier names (unless enclosed in “quotes”). An asterisk (*) […]

Continue Reading 4

Generating HTML reports from SQL Plus

Last week I was asked by one of IT managers on how to generate reports from database. He’d like to remind his direct reports on ticket’s backlog status. His requirement is just simply to run a simple SQL statement and email the result out weekly. I quickly told him that you can just cron or […]

Continue Reading 17

11g SQL*Plus

In 11g SQL*Plus, when session is terminated, somehow it displays the process ID, SID and Serial#. Not quite sure what is really for? I guess it is for debugging purpose. 10g Client user @db10w1> select * from dual; select * from dual * ERROR at line 1: ORA-01012: not logged on 11g Client user @db10w1> […]

Continue Reading 0

show spparameters

I found one of the interesting features in SQL*Plus 11g for Oracle11g database when doing “help show” from the tool. SHOW SPPARAMETERS [parameter_name] Show specific parameters – SYSTEM @db11r1> show spparameters sga SID NAME TYPE VALUE ——– —————————– ———– —————— * lock_sga boolean * pre_page_sga boolean * sga_max_size big integer * sga_target big integer Show […]

Continue Reading 4

Powered by WordPress. Designed by WooThemes