RUN DDL STATEMENTS IN PL/SQL

IF U RUN DDL STATEMENTS IN PL/SQL U WILL GET FOLLOWING ERROR:
SQL> begin
2 create table TEST(ROLL number);
3 END;
4 /
create table TEST(ROLL number);
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge

THERE ARE MANY WAYS U CAN RUN DDL STATEMENTS IN PL/SQL.

STEP 1. USE EXECUTE IMMEDIATE AND DDL STATEMENTS WITH SINGLE QUOTE.
SQL> BEGIN
2 EXECUTE IMMEDIATE 'CREATE TABLE TEST(ROLL NUMBER)';
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> DESC ROLL
Name Null? Type
------ ------ -------------
ROLL NUMBER


WHEN U USE DDL INSIDE A PROCEDURE IT WILL RETURN ERROR ORA-01031. EXECUTE .TO AVOID THIS ERROR GRANT CREATE TABLE TO USER DCL STATEMENTS BEFORE PLACE DDL STATEMENTS

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 31 12:52:11 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected.
SQL> CREATE OR REPLACE PROCEDURE TEST_PRO AS
2 BEGIN
3 EXECUTE IMMEDIATE 'CREATE TABLE TEST1(ROLL NUMBER)';
4 END;
5 /




SQL> EXECUTE TEST_PRO;
BEGIN TEST_PRO; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "REJAUL.TEST_PRO", line 3
ORA-06512: at line 1

TO AVOID THIS ERROR SEE BELOW


SQL> ED
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE TEST_PRO AS
2 BEGIN
3 EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO REJAUL';
4 EXECUTE IMMEDIATE 'CREATE TABLE TEST1(ROLL NUMBER)';
5* END;
SQL> /

Procedure created.

SQL> EXECUTE TEST_PRO

PL/SQL procedure successfully completed.


STEP:2


1 CREATE OR REPLACE PROCEDURE TEST_PRO1 AS
2 v_cursor integer;
3 row_process integer;
4 BEGIN
5 v_cursor := DBMS_SQL.OPEN_CURSOR;
6 DBMS_SQL.PARSE(v_cursor, 'GRANT CREATE TABLE TO REJAUL',DBMS_SQL.NATIVE);
7 row_process := DBMS_SQL.EXECUTE(v_cursor);
8 DBMS_SQL.PARSE(v_cursor, 'CREATE TABLE TEST_REJAUL (A DATE)',DBMS_SQL.NATIVE);
9 row_process := DBMS_SQL.EXECUTE(v_cursor);
10 DBMS_SQL.CLOSE_CURSOR(v_cursor);
11* END;
SQL> /

Procedure created.

SQL> EXECUTE TEST_PRO1

PL/SQL procedure successfully completed.

SQL> DESC TEST_REJAUL
Name Null? Type
----------------------------------------- -------- ----------------------------
A DATE


WHEN YOU CONNECT SYS USER U NEEDNOT GET ANY PRIVILEGES.UR DDL STATEMENTS WILL EXECUTE WITHOUT ERRRORS.

Creating Flash Recovery Area IN ORACLE DATABASE

If You want to create the flash recovery area for your own database.you should consider the following:
• Where you want the FRA to be created
• How much space should be allocated to the FRA
Having the answers to these questions in mind, you can then use the following process to
create the flash recovery area:
1. Disable the parameters log_archive_dest and log_archive_duplex_dest, if they are set
in the database. You can do that by issuing the following commands:
alter system set log_archive_duplex_dest = ''scope=both;
alter system set log_archive_dest = ''scope=both;

2. Log on as a user with the sysdba role (such as the user sys) in preparation to create the
flash recovery area:
sqlplus / as sysdba
sqlplus sys/ as sysdba
3. Issue the following commands to size and create the flash recovery area:
alter system set db_recovery_size = 7G;
alter system set db_recovery_dest = '/home/oracle/flasharea';
The sequence of these commands is important; you have to issue them in that order,
not the reverse. However, do replace the size and path name with the values you have
chosen for your system.
That’s it; the flash recovery area is ready for operation.
Remember, you can always define a different location for archived redo logs. If you use a
different location, then you can’t just erase the values of the parameters log_archive_dest and
log_archive_duplex_dest, as suggested in the earlier solution:
alter system set log_archive_duplex_dest = '';
alter system set log_archive_dest = '';
To place your log files elsewhere than the flash recovery area, you should use a different
parameter to specify the archived redo log location; use log_archive_dest_1 instead of
log_archive_dest. Suppose log_archive_dest used to be /dbarch. You can use
log_archive_dest_1 to specify the same location for archived redo logs.
First, check the value of the parameter log_archive_dest:
SQL>show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
log_archive_dest string /dbarch
The current setting of the archived redo log destination is /dbarch. Next, set the
log_archive_dest_1 parameter to that location:
SQL> alter system set log_archive_dest_1 = 'location=/dbarch';
Note the different syntax for this parameter; it has a location clause. Now, set
log_archive_dest to NULL:
SQL> alter system set log_archive_dest = '';
If you have set the two parameters—log_archive_dest and log_archive_duplex_dest—in
the initialization parameter file, you should edit the file to remove these two parameters completely.
Remember to recycle the database after editing the file for the changes to take effect.

Dealing with an ORA-27211 Error

When attempting to allocate a channel for tape I/O, you receive an ORA-27211 error.

Set up a large pool memory area on your database. If you are using Oracle Database 10g or
newer, then consider using automatic shared memory management (ASMM) to have Oracle
automatically allocate memory to the large pool.
If you’re not comfortable with using ASMM, then you can manually set the initialization
parameter large_pool_size. If you want to manually set the large pool size, here is Oracle’s
recommended formula for sizing it:
large_pool_size = num_of_allocated_channels * (16 MB + (4 * size_of_tape_buffer ))
You can display the component name and associated memory area (shared, large, Java, or streams
pool) of structures using the SGA by querying the V$SGASTAT view.

If you have enabled the use of I/O slaves, we recommend that you set up a large pool memory
area. When using I/O slaves for synchronous I/O, RMAN will use memory in the large pool if
it is available. If a large pool memory is not available, RMAN will allocate memory from the
shared pool. If RMAN cannot allocate enough memory, it will acquire memory from the PGA
and write an ORA-04031 message to your alert.log file.
Using I/O slaves can improve performance when performing I/O to synchronous
devices such as tape drives. You enable tape I/O slaves by setting the backup_tape_io_slaves
parameter to TRUE. This causes an I/O server processes (slaves) to be assigned to each tape
channel being used.

If you are working with an OS that doesn’t support asynchronous I/O, then you can enable
disk I/O slaves via the dbwr_io_slaves parameter. If you set this parameter to a nonzero value,
RMAN will use four server processes to perform backup and recovery operations.

managing with an ORA-19511 Error

You’re receiving an ORA-19511 error message from your media management layer (MML).
then you
Inspect the contents of the sbtio.log file. It should have more detailed information about the
root cause of the problem. The sbtio.log file is usually located in the directory defined by the
user_dump_dest initialization variable or in the $ORACLE_HOME/rdbms/log directory.

When your MML returns the ORA-19511 error, this is usually an indication of one of the
following:
• The MML software has not been configured or installed correctly.
• An OS variable related to the MML has not been set correctly.
In this situation, Oracle is only passing back the error from the MML. If the text of message
ORA-19511 does not provide enough information to resolve the problem, then you should contact
the third-party MML vendor and engage their assistance to resolve the problem.
When you receive an ORA-19511 error message, it will typically be accompanied by other

You can find additional information about media management layer errors in MetaLink note


Media Management Layer Error Messages
Message Number Description
sbtopen 7000 Backup file not found
sbtopen 7001 File exists
sbtopen 7002 Bad mode specified
sbtopen 7003 Invalid block size specified
sbtopen 7004 No tape device found
sbtopen 7005 Device found, but busy
sbtopen 7006 Tape volume not found
sbtopen 7007 Tape volume in use
sbtopen 7008 I/O error
sbtopen 7009 Can’t connect with Media Manager
sbtopen 7010 Permission denied
sbtopen 7011 OS error
sbtopen 7012 Invalid argument to sbtopen
sbtclose 7020 Invalid file handle or file not open
sbtclose 7021 Invalid flags to sbtclose
sbtclose 7022 I/O error
sbtclose 7023 OS error
sbtclose 7024 Invalid argument to sbtclose
sbtclose 7025 Can’t connect with Media Manager
sbtwrite 7040 Invalid file handle or file not open
sbtwrite 7041 End of volume reached
sbtwrite 7042 I/O error
sbtwrite 7043 OS error
sbtwrite 7044 Invalid argument to sbtwrite
sbtread 7060 Invalid file handle or file not open
sbtread 7061 EOF encountered
sbtread 7062 End of volume reached
sbtread 7063 I/O error
sbtread 7064 OS error
sbtread 7065 Invalid argument to sbtread
sbtremove 7080 Backup file not found
sbtremove 7081 Backup file in use
sbtremove 7082 I/O error
sbtremove 7083 Can’t connect with Media Manager
sbtremove 7084 Permission denied
sbtremove 7085 OS error
sbtremove 7086 Invalid argument to sbtremove
sbtinfo 7090 Backup file not found
sbtinfo 7091 I/O error
sbtinfo 7092 Can’t connect with Media Manager
sbtinfo 7093 Permission denied
sbtinfo 7094 OS error
sbtinfo 7095 Invalid argument to sbtinfo
sbtinit 7110 Invalid argument to sbtinit
sbtinit 7111 OS error

Viewing RMAN Command History

You didn’t log your output to an OS file, and you now wonder whether there is a way to view
the RMAN command stack output.
you can get Solution:-


Use V$RMAN_OUTPUT to view the text messages that RMAN produces when performing
tasks. Run this query to view the historical RMAN command messages:
SQL> select
2 sid,
3 recid,
4 output
5 from v$rman_output
6 order by recid
7 /
The output looks like this:
SID RECID OUTPUT
------ ----- -----------------------------------------------------------------
154 31 Starting backup at 14-JAN-07
154 32 using target database control file instead of recovery catalog
154 33 allocated channel: ORA_DISK_1
154 34 channel ORA_DISK_1: sid=144 devtype=DISK
154 35 channel ORA_DISK_1: starting full datafile backupset
154 36 channel ORA_DISK_1: specifying datafile(s) in backupset
154 37 input datafile fno=00001 name=C:\ORACLE\10.2\ORADATA\ORCL\SYSTEM01.DBF
154 38 input datafile fno=00003 name=C:\ORACLE\10.2\ORADATA\ORCL\SYSAUX01.DBF
154 39 input datafile fno=00002 name=C:\ORACLE\10.2\ORADATA\ORCL\UNDOTBS01.DBF
154 40 input datafile fno=00005 name=C:\ORACLE\10.2\ORADATA\ORCL\TOOLS01.DBF
154 41 input datafile fno=00004 name=C:\ORACLE\10.2\ORADATA\ORCL\USERS01.DBF
154 42 channel ORA_DISK_1: starting piece 1 at 14-JAN-07
154 43 channel ORA_DISK_1: finished piece 1 at 14-JAN-07
154 44 piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\
BACKUPSET\2007_01_13\O1_MF_NNNDF_TAG20070113T160913_2TLSNTNZ_.BKP
154 45 channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35
154 46 Finished backup at 14-JAN-07
You can also join V$RMAN_OUTPUT to V$RMAN_STATUS to get additional information.
This useful query shows the type of command RMAN is running, its current status, and its
associated output messages:
SQL> select
2 a.sid,
3 a.recid,
4 b.operation,
5 b.status,
6 a.output
7 from v$rman_output a,
8 v$rman_status b
9 where a.rman_status_recid = b.recid
10 and a.rman_status_stamp = b.stamp
11 order by a.recid
12 /

Diagnosing NLS Character Set Issues

You’re trying to connect to RMAN, and you get an NLS error similar to the following:
ORA-12705: Cannot access NLS data files or invalid environment specified

There are usually two reasons for NLS character set problems:
• There’s a mismatch between the NLS character set of the client and that of the database
server.
• You have an NLS-related operating system variable that has been set incorrectly.
To determine whether there is an NLS character set mismatch, compare your target database
character set to your client character set. To display your target database character set,
issue the following SQL statement:
SQL> select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
VALUE
-----------
WE8ISO8859P1
Compare that to the operating system NLS_LANG setting on your client. In Unix, use the
echo command to display the relevant NLS parameters:
$ echo $NLS_LANG
In Windows, search the Registry Editor for the value of NLS_LANG as shown here: Start -
run - regedit - Edit - Find - NLS_LANG.
If you find that there is an NLS_LANG mismatch, then you can override the OS variable
manually. For example, in a Unix C shell environment, use the setenv OS command:
$ setenv NLS_LANG american_america.we8iso8859p1
In a Unix Korn shell environment, use the OS export command as follows:
$ export NLS_LANG=american_america.we8iso8859p1
In a Windows environment, use the set command, as shown here:
c:\> set NLS_LANG=american_america.we8iso8859p1
If you set NLS_LANG to a value that RMAN doesn’t recognize, then you will receive an
error like this:
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12705: Cannot access NLS data files or invalid environment specified
If you receive an error like that, ensure that your NLS_LANG operating system parameter
is set to a valid value.

Terminating RMAN Processes

You have an RMAN job that appears to be hung. You want to terminate the job.

You can terminate an RMAN job by using one of the following techniques:
• Press Ctrl+C from the RMAN interface. This approach, of course, works only if performed
from the online session responsible for the process you want to terminate.
• Manually kill the OS process.
• Terminate the server session corresponding to an RMAN channel using an alter
system kill SQL statement.
Pressing Ctrl+C from the RMAN interface is the easiest way to terminate a job (when it
works). If that isn’t successful or if you did not initiate the job interactively, then you’ll have to
try one of the following solutions.

Terminating a Unix Process
In a Unix environment, you can identify an RMAN operating system process number using
the Unix ps command as follows:
$ ps –ef | grep –v grep | grep –i rman
The process number is displayed in the second column of the output:
oracle 25010 24946 0 15:42:57 pts/2 0:01 rman target /
In this example, the process to terminate is 25010. You can then use the Unix kill command,
as shown here:
$ kill -9 25010
Terminating a Windows Process
On a Windows server, you can use the Task Manager utility to identify background processes.
You can start Task Manager in one of the following ways:
• Ctrl+Alt+Delete.
• Ctrl+Shift+Esc.
• Right-click an empty space in the taskbar, and choose Task Manager.
From the Task Manager, click Applications, and select the RMAN process you want to terminate.
Click End Task to terminate the process.

Using SQL to Terminate an RMAN Channel
Use the alter system kill session SQL statement to terminate a hung RMAN job. To do this,
you need to first identify the serial ID and serial number:
SQL> SELECT
2 s.sid
3 ,s.serial#
4 ,p.spid
5 ,s.client_info
6 FROM v$process p,
7 v$session s
8 WHERE p.addr = s.paddr
9 AND client_info LIKE '%rman%';
SID SERIAL# SPID CLIENT_INFO
----- ---------- ------------ -------------------------
157 18030 7344 rman channel=ORA_DISK_1
In this case, the SID is 157, and the serial number is 18030. You can then use the alter
system kill session SQL statement as follows:
SQL> alter system kill session '157,18030';
If multiple RMAN jobs are running, then you’ll have to identify the serial ID number of
the particular job you are interested in terminating. You can do this by viewing the RMAN
output from the terminal:
Starting backup at 15-feb-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
From the previous text, you can see that the serial ID is 145. After you have identified the
serial ID that you want to terminate, you can run the previous SQL query in this example to
identify the corresponding serial number to use in your alter system kill session command.

managing with RMAN-06059 Error

You’ve just switched from user-managed backups to using RMAN backups and attempt to run
the following command:
RMAN> backup database plus archivelog;
Your backup process doesn’t get very far when RMAN throws this error:
RMAN-03002: failure of backup command ...
RMAN-06059: expected archived log not found, loss of archived log compromises
recoverability
Your boss happens to be in your office when you were attempting to back up the database
and lets you know that “compromised recoverability” will translate into “compromised job
security.”
how u will solve the problem
You must update RMAN’s repository to reflect that archived redo log files either have been
physically deleted or have been moved to another location on disk. Use the crosscheck command
to inform RMAN that archived redo log files have been physically removed from disk, as
shown here:
RMAN> crosscheck archivelog all;
Now run your backup command again; this time it should succeed:
RMAN> backup database plus archivelog;
If the archived redo log files have been physically moved to a different location on disk,
then use the catalog command to update the RMAN repository with the new location of the
files:
RMAN> catalog start with '/oradump01/oldarchive';
The start with clause of the catalog command instructs RMAN to look in the specified
directory and update its repository with any archived redo log files, backup pieces, or image
copies located within that directory (and its subdirectories).

Services on windows platform

Unlike linux,Unix, where the Oracle instance starts as a number of processes, on Windows the
Oracle database starts as a service. A service allows the database instance to start without a
user being logged in. A service is conceptually similar to, though not quite the same as, a Unix
daemon.
So, an Oracle instance consists of two parts: the Windows service and the instance itself.
The service has to start first before you can connect to it and start the instance. Contrast this
with Unix, where you make a connection first to start the instance. When you install the Oracle
database software, this service is automatically created. To check for the service, select Start ➤
Programs ➤ Administrative Tools ➤ Services.

Another way to check the service name command is to use the SC command. Here is how
you can check the database service:
C:\>sc query OracleServiceorcl
SERVICE_NAME: OracleServiceorcl
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0


If the service is set to Manual, then you must start it. To start it, you can right-click the
name of the service and click Start. Alternatively, you can execute the following command
from the command prompt:
C:> net start OracleServiceorcl
This command starts the service OracleServiceorcl. Typically it also starts up the
instance.
Another command used on Windows is oradim. This command is used for a variety of
things—from creating an Oracle service to starting/stopping and even removing the services.
Here is how you can start the Oracle service using this tool:
C:\> oradim -startup -sid orcl-starttype srvc,inst
You can also use the oradim command to stop the service. To see a complete list of oradim
commands and options, type oradim at the command line without any arguments; this gives
you the syntax, as shown here:

ORADIM: [options]. Refer to manual.
Enter one of the following command:
Create an instance by specifying the following options:
-NEW -SID sid | -SRVC srvc | -ASMSID sid | -ASMSRVC srvc [-SYSPWD pass]
[-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]
[-SHUTMODE normal|immediate|abort] [-TIMEOUT secs] [-RUNAS osusr/ospass]
Edit an instance by specifying the following options:
-EDIT -SID sid | -ASMSID sid [-SYSPWD pass]
[-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]
[-SHUTMODE normal|immediate|abort] [-SHUTTYPE srvc|inst] [-RUNAS osusr/ospass]
Delete instances by specifying the following options:
-DELETE -SID sid | -ASMSID sid | -SRVC srvc | -ASMSRVC srvc
Startup services and instance by specifying the following options:
-STARTUP -SID sid | -ASMSID sid [-SYSPWD pass]
[-STARTTYPE srvc|inst|srvc,inst] [-PFILE filename | -SPFILE]
Shutdown service and instance by specifying the following options:
-SHUTDOWN -SID sid | -ASMSID sid [-SYSPWD pass]
[-SHUTTYPE srvc|inst|srvc,inst] [-SHUTMODE normal|immediate|abort]
Query for help by specifying the following parameters: -? | -h | -help

EM Database Control

There are three steps for getting started with EM Database Control:-

1) Install EM Database Control.
2) Ensure the Database Control process is running.
3) Access EM Database Control from your favorite web browser.


first step: Install EM Database Control
By default, EnterpriseManager Database Control is included with every installation of Oracle
Database 10g or newer. It will be installed for you unless you explicitly deselect it during the
installation of the Oracle RDBMS software (binaries).
If for some reason EM Database Control hasn’t been installed, then use the Oracle Universal
Installer to install it. Refer to the EM installation documentation that is specific to your OS
for instructions on how to install Enterprise Manager. Figure 19-1 is a partial snapshot of the
Oracle Universal Installer screen showing that Enterprise Manager has already been installed.
second:Step- Ensure the Console Process Is Running
The EM Database Control console process must be running before you can connect to EM via
your web browser. If you had the Oracle Installer automatically create a starter database, then
you probably already have a console process running on your database server. In Unix or Windows,
you can start, stop, and check on the status of the console process via the emctl utility.
The emctl utility is located in ORACLE_HOME/bin (just like all other Oracle utilities).
Ensure that ORACLE_HOME/bin is included in your OS PATH variable. Also verify that the
ORACLE_SID operating system variable is set to your target database SID. Once you’ve established
your database-related operating system variables, you can then run emctl commands.
Log in to the OS account that you installed the Oracle software with (usually named
oracle), and start the EM console as shown here:
$ emctl start dbconsole
To check on the status of the EM console, run the following command:
$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
https://5a0c04b78203433:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.

To stop the EM console, run the following command:
$ emctl stop dbconsole
third step:-
Accessing Enterprise Manager from Browser
Once your console process is running, you can access EM Database Control via your favorite
web browser via the following URL:
http://:/em
For example, if your hostname is sangram and your port number is 1158, your URL to
access EM would be as follows:
http://sangram:1158/em