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';