Pubudu: Data and Decisions

You can execute a function defined in a package in two ways. Solution 1 declare     result number; begin        result := package_name.function_name (150); end; Solution 2 select package_name.function_name (15000) from dual;

Below query can be used to find out the usage of tablespaces in your database.(Free and Used space). This can be find out through the Oracle EM also. SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace, fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) FROM dba_temp_files...

You may want to recreate Oracle Enterprise Manager due to several reasons.. Below are the details to recreate the EM. Drop Database Console To drop (remove) the configuration files and repository for Database Console, run: $ emca -deconfig dbcontrol db -repos drop   ------------------------------------------------------------------------------------------------------------------ Eg: [oracle@local ~]$ emca -deconfig dbcontrol db -repos drop STARTED EMCA at Apr 6, 2011 11:46:55 AM EM Configuration Assistant, Version 10.2.0.1.0 Production Copyright (c) 2003, 2005, Oracle. All rights reserved. Enter the following information: Database SID: my830 Listener port number: 1521 Password for SYS user: Password for SYSMAN user: Do you wish to continue? [yes(Y)/no(N)]: y Apr 6, 2011 11:47:09 AM oracle.sysman.emcp.EMConfig perform INFO: This operation is being logged at /u01/app/oracle/OraHome_1/cfgtoollogs/emca/my830/emca_2011-04-06_11-46-55-AM.log. Apr 6, 2011 11:47:09 AM orac...

You can query dba_users view to get user relevant data of your Oracle database. Eg: SELECT * FROM DBA_USERS WHERE DEFAULT_TABLESPACE IN ('TBS1','TBS2') ORDER BY DEFAULT_TABLESPACE,USERNAME

The Flash Recovery Area  is a unified storage location for all recovery related files and activities in an Oracle database. To enable Flash Recovery Area you just need to specify 2 parameters in the following order:         - DB_RECOVERY_FILE_DEST_SIZE (specifies max space to use)         - DB_RECOVERY_FILE_DEST (Location) You can check current settings by issuing "show parameter" in sqlplus prompt. eg: show parameter DB_RECOVERY_FILE_DEST_SIZE You can specify those parameters in the init.ora file or by ALTER SYSTEM SET command with the scope=both option. Examples: ALTER SYSTEM SET db_recovery_file_dest_size=10G scope=both; ALTER SYSTEM SET db_recovery_file_dest='/oradata/flash_recovery_area';

This error is giving because Oracle detects an inconsistency between the mirrored copies of the control file. All copies of the control file must have the same internal sequence number for oracle to start up the database. Solution To fix the error you have to start your database with single copy of the control file and then shut the database down and then copy the good copy of control file onto the other mirror copies. Step 01 - If database is still up do a shutdown immediate. Step 02 - If you use pfile then edit the CONTROL_FILES parameter from init.ora and modify it to include just one copy of control file. You may want to issue create pfile from spfile to create a pfile before editing this parameter. Step 03 - Start the database in restricted mode. startup restrict or mount the database. startup mount If it is fine go to step 04. Step 04 - Shut the database down. SQL>shutown Step 05 - Copy the good mirrored copy of the control file that you just used to bring t...

When you try to login as sys using sys/password as sysdba , you may get the error ORA-09817: Write to audit file failed. And If you tried to login as a normal user you may get  the error - ORA-00257: archiver error. Connect internal only, until freed. These errors are giving most probably because your Archive destination is full. Check archive destionation :  SQL> ARCHIVE LOG LIST Database log mode              Archive Mode Automatic archival             Enabled Archive destination            USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     1324060 Next log sequence to archive   1324066 Current log sequence           1324066 SQL> show parameter DB_RE NAME      ...