Execute a function defined in a package

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;

Tablespace Usage

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 fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

How to recreate Oracle EM in linux

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 oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Apr 6, 2011 11:47:16 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Apr 6, 2011 11:48:18 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 6, 2011 11:48:18 AM
 

------------------------------------------------------------------------------------------------------------------------ 

In the case the above command is giving an error and you cannot drop the EM,

try to do it manually by executing below commands in SQLPLUS prompt.

sql> drop user sysman cascade;
sql> drop role mgmt_user;
sql> drop user mgmt_view cascade;
sql> drop public synonym MGMT_TARGET_BLACKOUTS;
sql> drop public synonym SETEMVIEWUSERCONTEXT;

Create Database Console

To create the configuration files and repository for Database Console, run:

$ emca -config dbcontrol db -repos create

--------------------------------------------------------------------------------------------------

Eg:

[oracle@local ~]$ emca -config dbcontrol db -repos create

STARTED EMCA at Apr 6, 2011 11:50:37 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
Database SID: Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/OraHome_1

Database hostname ................ local.test.com
Listener port number ................ 1521
Database SID ................ lin11830
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 6, 2011 11:50:56 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-50-37-AM.log.
Apr 6, 2011 11:50:57 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Apr 6, 2011 11:53:00 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Apr 6, 2011 11:53:03 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Apr 6, 2011 11:54:38 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 6, 2011 11:54:38 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://local.test:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 6, 2011 11:54:38 AM

---------------------------------------------------------------------------------------------------

Recreate Database Console(This command is a collection of above two commands)

To recreate the configuration files and repository for Database Console, run:

$ emca -config dbcontrol db -repos recreate

Configure Database Console

$ emca -config dbcontrol db

Query User Details

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

Flash Recovery Area

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