Wednesday, March 28, 2018

Use of GLOBAL_NAMES parameter with respect to DB links

GLOBAL_NAMES parameter is a combination of the database name and the domain name like “rose.corp.com”, in which “rose” is the database name and “corp.com” is the domain name.

When you set the initialization parameter GLOBAL_NAMES to TRUE, the database ensures that the name of the database link is the same as the global database name of the remote database.

For example, if the global database name for rose is rose.corp.com, and GLOBAL_NAMES is TRUE, then the link name must be called rose.corp.com.

If you set the initialization parameter GLOBAL_NAMES to FALSE, then you are not required to use global naming. You can name the database link whatever you want. For example, you can name a database link to rose.corp.com as flower.

What is Database link and its types

database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Parameters in Init.ora file related to Database Links

Ø  global_names 
Ø  open_links
è Maximum number of open links per session
è Deafult value is 4
è Range of values – 0 to 255
Ø  open_links_per_instance
è Default value is 4
è Range of values – 0 to 4294967295 (4 GB -1)
è In RAC, multiple instances can have different values.


Types of Database Links

Ø  Private:
-   The more secure db link when created in a specific schema, only the owner or the PL/SQL program created within that schema can use this database link to access respective schema objects in the corresponding remote database.
-    By default, a database link will be a private one until and unless “Public” or “Global” are explicitly mentioned while creating the DB link.

Ø  Public:
-      This is a database-wide db link
-     Can be used by all the users and PL/SQL programs in that database to access respective schema objects in the corresponding remote database
-      When many users need access to the same schema in a remote Oracle database, we can create a single public database link for all users in a database. 

Ø  Global:
-     This is a network/global-wide db link
-    Defined in an OID or Oracle Names Server. Anyone on the network can use to access respective schema objects in the corresponding remote database.
-    When an Oracle network uses Oracle Names, an administrator can easily manage global database links for all databases in the system. Database link management is centralized and simple since the changes on OID will reflect the changes while the Client connects to the Server. 

Sunday, March 25, 2018

Query to find schema size in the Oracle database

set linesize 120  
set pages 500

select owner,tablespace_name,sum(bytes)/1024/1024/1024 from dba_segments where owner in (‘<schema_name1>’,’<schema_name2>’) group by owner,tablespace_name;

Using the above query, we can find the following data:


Ø  Owner                                     à Schema owner
Ø  Tablespace_Name                  à tablespace in which the schema object resides
Ø  Sum(bytes)/1024/1024/1024  àused space (in GB) by the schema objects in the respective tablespace




Saturday, March 24, 2018

Query to find Tablespace Used & Free Space

set linesize 120
set pages 500

SELECT ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free"
FROM (SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY ts.tablespace_name;


Using the above query, we can find the following data:

Ø  Tablespace Name
Ø  File Count   à Number of datafiles in the respective tablespace
Ø  Size(MB)     à Total allocated space (in MB) for the respective tablespace
Ø  Used(MB)  à Used space (in MB) in the respective tablespace
Ø  Free(MB)    à Free space (in MB) in the respective tablespace
Ø  Max Ext(MB)à If “auto-extend” enabled for the respective tablespace, the value of this column will be the maximum extendable size. If its value is zero, then the respective tablespace is not auto-extensible