Oracle how to move and rename datafiles

First of all, you don't have to restart the database. Renaming data files is very common due to the following reasons. 

Maybe you don't like the name of data file and want to keep a naming method, or the most important, you want to move the datafile to a new, faster filesystem with more empty space. Moving datafiles is something that you need when you will start searching how I/O is spread on you filesystems! 

How to 
For our example. Let's say that the name of the tablespace is TBS1 and the datafile is '/filesystem01/tbs01.dbf' and you want to move it to '/filesystem02/tbs01.dbf' 

1. First take the tablespace offline

ALTER TABLESPACE TBS1 OFFLINE;

If someone is using the tablespace, then you can't take it offline! You must kill all sessions using it. The time needed to take it offline, depends on the size of tablespace, a 10Mbytes tablespace may take 1 sec, a 1TByte tablespace may take some minutes… so be patient. 

2. Move the datafile to the new filesystem with the os command.

$>mv /filesystem01/tbs01.dbf /filesystem02/tbs01.dbf

3. Do the renaming to update the dictionary and controlfile

ALTER TABLESPACE TBS1 RENAME DATAFILE '/filesystem01/tbs01.dbf' 

TO '/filesystem02/tbs01.dbf';

4. Take back tablespace online.

ALTER TABLESPACE TBS1 ONLINE;

Oracle How to find user object grants

Here  with this query you can find the objects grants for a specific user.

SELECT tpm.NAME PRIVILEGE,

       DECODE (MOD (oa.OPTION$, 2), 1, 'YES', 'NO') grantable,
       ue.NAME Grantee, ur.NAME grantor, u.NAME owner,
       DECODE (o.TYPE#,
               0, 'NEXT OBJECT',
               1, 'INDEX',
               2, 'TABLE',
               3, 'CLUSTER',
               4, 'VIEW',
               5, 'SYNONYM',
               6, 'SEQUENCE',
               7, 'PROCEDURE',
               8, 'FUNCTION',
               9, 'PACKAGE',
               11, 'PACKAGE BODY',
               12, 'TRIGGER',
               13, 'TYPE',
               14, 'TYPE BODY',
               19, 'TABLE PARTITION',
               20, 'INDEX PARTITION',
               21, 'LOB',
               22, 'LIBRARY',
               23, 'DIRECTORY',
               24, 'QUEUE',
               28, 'JAVA SOURCE',
               29, 'JAVA CLASS',
               30, 'JAVA RESOURCE',
               32, 'INDEXTYPE',
               33, 'OPERATOR',
               34, 'TABLE SUBPARTITION',
               35, 'INDEX SUBPARTITION',
               40, 'LOB PARTITION',
               41, 'LOB SUBPARTITION',
               42, 'MATERIALIZED VIEW',
               43, 'DIMENSION',
               44, 'CONTEXT',
               46, 'RULE SET',
               47, 'RESOURCE PLAN',
               48, 'CONSUMER GROUP',
               51, 'SUBSCRIPTION',
               52, 'LOCATION',
               55, 'XML SCHEMA',
               56, 'JAVA DATA',
               57, 'SECURITY PROFILE',
               59, 'RULE',
               62, 'EVALUATION CONTEXT',
               'UNDEFINED'
              ) object_type,
       o.NAME object_name, '' column_name
  FROM SYS.objauth$ oa, SYS.obj$ o, SYS.USER$ u, SYS.USER$ ur, SYS.USER$ ue,

 table_privilege_map tpm
 WHERE oa.obj# = o.obj#
   AND oa.grantor# = ur.USER#
   AND oa.Grantee# = ue.USER#
   AND oa.col# IS NULL
   AND oa.PRIVILEGE# = tpm.PRIVILEGE
   AND u.USER# = o.owner#
   AND o.TYPE# IN (2, 4, 6, 9, 7, 8, 42, 23, 22, 13, 33, 32)
   AND ue.NAME = 'NISA_DL'
UNION ALL                                               -- column level grants
SELECT tpm.NAME PRIVILEGE,
       DECODE (MOD (oa.OPTION$, 2), 1, 'YES', 'NO') grantable,
       ue.NAME Grantee, ur.NAME grantor, u.NAME owner,
       DECODE (o.TYPE#,
               0, 'NEXT OBJECT',
               1, 'INDEX',
               2, 'TABLE',
               3, 'CLUSTER',
               4, 'VIEW',
               5, 'SYNONYM',
               6, 'SEQUENCE',
               7, 'PROCEDURE',
               8, 'FUNCTION',
               9, 'PACKAGE',
               11, 'PACKAGE BODY',
               12, 'TRIGGER',
               13, 'TYPE',
               14, 'TYPE BODY',
               19, 'TABLE PARTITION',
               20, 'INDEX PARTITION',
               21, 'LOB',
               22, 'LIBRARY',
               23, 'DIRECTORY',
               24, 'QUEUE',
               28, 'JAVA SOURCE',
               29, 'JAVA CLASS',
               30, 'JAVA RESOURCE',
               32, 'INDEXTYPE',
               33, 'OPERATOR',
               34, 'TABLE SUBPARTITION',
               35, 'INDEX SUBPARTITION',
               40, 'LOB PARTITION',
               41, 'LOB SUBPARTITION',
               42, 'MATERIALIZED VIEW',
               43, 'DIMENSION',
               44, 'CONTEXT',
               46, 'RULE SET',
               47, 'RESOURCE PLAN',
               48, 'CONSUMER GROUP',
               51, 'SUBSCRIPTION',
               52, 'LOCATION',
               55, 'XML SCHEMA',
               56, 'JAVA DATA',
               57, 'SECURITY PROFILE',
               59, 'RULE',
               62, 'EVALUATION CONTEXT',
               'UNDEFINED'
              ) object_type,
       o.NAME object_name, c.NAME column_name
  FROM SYS.objauth$ oa, SYS.obj$ o, SYS.USER$ u, SYS.USER$ ur, SYS.USER$ ue, SYS.col$ c,

 table_privilege_map

 tpm
 WHERE oa.obj# = o.obj#
   AND oa.grantor# = ur.USER#
   AND oa.Grantee# = ue.USER#
   AND oa.obj# = c.obj#
   AND oa.col# = c.col#
   AND BITAND (c.property, 32) = 0                     /* not hidden column */
   AND oa.col# IS NOT NULL
   AND oa.PRIVILEGE# = tpm.PRIVILEGE
   AND u.USER# = o.owner#
   AND o.TYPE# IN (2, 4)
   AND ue.NAME = 'NISA_DL';

Oracle How to calculate export dump size

Sometimes you have to export all the database or many schemas but you don't have all the necessary space needed, or you don't know exactly how much space the dmp file it will take. 

Oracle with exports (or expdp) stores at the dmp file the table data. Indexes are just "Create index statements" which will be created after loading the table data. So the key factor of how big will be your dmp file, depends on how much table data you have. 

The following query calculates how much table data each schema takes on your database

SELECT owner, ROUND(SUM(size_mb)) MBytes FROM 
(
SELECT owner, segment_name, segment_type, partition_name,

ROUND(bytes/(1024*1024),2) SIZE_MB,

tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION',

 'TABLE SUBPARTITION') 
--AND TABLESPACE_NAME LIKE 'COSTE%' 
--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%' 
--AND partition_name LIKE 'USAGE_FCT_NEW%'
--AND OWNER = 'TARGET_DW' 
--AND ROUND(bytes/(1024*1024),2) > 1000)
) 
GROUP BY owner 
ORDER BY MBytes DESC;

OWNER                              MBYTES
------------------------------ ----------
TARGET_DW                         3774208
TARGET_POC                         673192
STAGE_DW                           469263
PRESTAGE_DW                        389326
SHADOW_DW                          257233
PRESENT_PERIF                      148063
SNAP                               141565
PKIOUSIS                           117535
DM_SPSS                             44760
MONITOR_DW                          35336
CUSTOMER_VIEW                       29807

for example if you export the SHADOW_DW schema, it will create a dmp file approximately 257233MB->250GBytes. 

Tip: 
Bear in mind that the actual dump file will have smaller size than 250GB!!!, because in most cases the TABLE DATA are fragmented!. Export-Datapump, removes fragmentation!. 

Indexes, packages, procedures, views, etc, are not taking too much space in the dmp file, are just DDL statements.

SELECT 'expdp SYSTEM/oracle DUMPFILE=' || owner || '_%U.dmp

 DIRECTORY=exp_dir PARALLEL=10 LOGFILE='

 || owner ||'.log schemas= ' || owner SQL
FROM (
SELECT owner, ROUND(SUM(size_mb)) MBytes FROM 
(
SELECT owner, segment_name, segment_type, partition_name,

 ROUND(bytes/(1024*1024),2) SIZE_MB,

 tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION',

 'TABLE SUBPARTITION') 
--AND TABLESPACE_NAME LIKE 'COSTE%' 
--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%' 
--AND partition_name LIKE 'USAGE_FCT_NEW%'
AND OWNER <> 'SYSTEM' 
--AND ROUND(bytes/(1024*1024),2) > 1000)
) 
GROUP BY owner 
ORDER BY MBytes DESC);

Oracle how to Send emails from the database

A simple procedure to send emails from the database.
You just need to know the IP of the SMTP email server.

CREATE OR REPLACE PROCEDURE mail_send(sender IN VARCHAR2,

recipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2)
IS

mail_conn utl_smtp.connection;
test varchar2(200);
crlf varchar2(2):= CHR( 13 ) || CHR( 10 );
mesg varchar2(2000);

BEGIN

mail_conn := utl_smtp.open_connection('60.212.02.34');
utl_smtp.helo(mail_conn, 'albert.einstein.com');
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);

mesg:= subject || ' ' || crlf || message || crlf;
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
END;
/

For example login to sqlplus and run

BEGIN
mail_send('spiderman@dbc.com','ospiderman@dbc.com', 'tablespace error', 'You need to add more space!!!!');
END;
/

You can also setup an emailing list, a simple table with the email of the recipients.

CREATE TABLE HELPDESK.S_RECIPIENTS
(
  RECIPIENT   VARCHAR2(200)
)
TABLESPACE HELPDESK
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;

INSERT INTO HELPDESK.S_RECIPIENTS VALUES ('spiderman@dbc.com');
INSERT INTO HELPDESK.S_RECIPIENTS VALUES ('ospiderman@dbc.com');
COMMIT;

Oracle how to truncate table from remote database

When you try to truncate a table on a remote database, you will get the following error: 

ORA-02021: DDL operations are not allowed on a remote database. 
You have a database link to the remote database so you can see objects there and execute them (e.g. procedures, functions, packages, triggers, etc). The solution is to create the following procedure on the remote database, then execute it from the local one.

CREATE OR REPLACE PROCEDURE Trunc_RMT_Table(p_table_name VARCHAR2) AS

   v_sql_error_code PLS_INTEGER;
   v_sql_error_message VARCHAR2(512);

BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table_name;
EXCEPTION
   WHEN OTHERS THEN
      v_sql_error_code := SQLCODE;
      v_sql_error_message := SQLERRM(v_sql_error_code);
      DBMS_OUTPUT.ENABLE(5000);
      DBMS_OUTPUT.PUT_LINE('OTHER ERROR');
      DBMS_OUTPUT.PUT_LINE(v_sql_error_message);
END Truncate_Remote_Table;
/

To execute the procedure, use the following from the local database:

BEGIN
   Trunc_RMT_Table@db_link(remote_table_name);
END;
/