ExternalTables_OracleDirectory

Hi Folks,

I have a task to create an oracle directory and assign access privileges to the users. And create an external table; load the data through .CSV file.

So that I have a created a directory in oracle and assigned access privileges to the users as below:

General Related Data Dictionary Objects dir$ all_directories ku$_directory_t dba_directories ku$_directory_view System Privileges GRANT create any directory TO <user_name>; GRANT drop any directory TO <user_name>; GRANT create any directory TO uwclass; GRANT drop any directory TO uwclass; Create Directory Create A Directory CREATE OR REPLACE DIRECTORY <directory_name> AS '<operating_system_path>'; conn / as sysdba desc dba_directories set linesize 121 col owner format a15 col directory_name format a20 col directory_path format a70 SELECT * FROM dba_directories; CREATE OR REPLACE DIRECTORY ctemp AS 'c: emp'; SELECT * FROM dba_directories; Granting Privileges To A Directory Grant Read On A Directory GRANT READ ON DIRECTORY <directory_name> TO <schema_name> col grantor format a20 col grantee format a20 col table_schema format a20 col table_name format a20 col privilege format a10 SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'CTEMP'; GRANT READ ON DIRECTORY ctemp TO uwclass; SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'CTEMP'; Grant Write On A Directory GRANT WRITE ON DIRECTORY <directory_name> TO <schema_name> GRANT WRITE ON DIRECTORY ctemp TO uwclass; SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'CTEMP'; Revoke Read On A Directory REVOKE READ ON DIRECTORY <directory_name> FROM <schema_name> REVOKE READ ON DIRECTORY ctemp FROM uwclass; Revoke Write On A Directory REVOKE WRITE ON DIRECTORY <directory_name> FROM <schema_name> REVOKE WRITE ON DIRECTORY ctemp FROM uwclass; Drop Directory Drop A Directory DROP DIRECTORY <directory_name>; SELECT * FROM dba_directories; DROP DIRECTORY ctemp; SELECT * FROM dba_directories;

Creating an External Table:

Create a directory object pointing to the location of the files.

CREATE OR REPLACE DIRECTORY test2 AS 'D:/test2';

EXAMPLE:

create table forecastforOct2013

(YYYYMM number(20),

YYYYMMDD number(20),

DAY_NUMBER varchar2(25),

ship_day number(20),

channel varchar2(25),

reporting_group varchar2(26),

item_type varchar2(25),

forecast_qty_lbs varchar2(26),

forecast_amt varchar2(25)

)

organization external

(

type oracle_loader

default directory test2

access parameters

(

records delimited by newline

badfile 'forecastforOct2013.bad'

discardfile 'forecastforOct2013.dsc'

logfile 'forecastforOct2013.log'

fields terminated by ','
optionally enclosed by '"'

missing field values are null

)

location ('forecastforOct2013.csv')

)
reject limit unlimited ;

1. 1.      Before executing this script we have created a directory called “test2” under D:/ folder and assigned read, wrote permissions to the oracle user .
2. 2.      Place the file (here: forecastforOct2013) in test2 directory.
3. 3.      Make sure that you have all the access permissions to the test2 directory and forecast... Files.

1. Reference: http://www.oracle-base.com/articles/9i/external-tables-9i.php
2. https://forums.oracle.com/thread/2576750

OC4J Configuration issue - Unable to openDBCONSOLE

[oracle@localhost ~]$ emctl status dbconsole
OC4J Configuration issue. /u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_localhost.localhost.com_fmw not found.
[oracle@localhost ~]$ emctl start dbconsole
OC4J Configuration issue. /u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_localhost.localhost.com_fmw not found.
This is due to a misconfiguration of dbconsole during database setup. The fastest solution is usually to recreate repository and configuration.
First, deconfigure dbconsole:
[oracle@localhost ~]$ emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Aug 5, 2013 7:54:35 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: fmw
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
Aug 5, 2013 7:54:54 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/fmw/emca_2013_08_05_19_54_34.log.
Aug 5, 2013 7:54:55 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Aug 5, 2013 7:54:55 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Aug 5, 2013 7:57:04 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Aug 5, 2013 7:57:04 PM

Now, let's configure it again
[oracle@localhost ~]$ emca -config dbcontrol db -repos create
STARTED EMCA at Aug 5, 2013 7:58:22 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: fmw
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/dbhome_1 ]:
Password for SYS user:
Password for DBSNMP 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/product/11.2.0/dbhome_1
Local hostname ................ localhost.localhost.com
Listener ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/dbhome_1
Listener port number ................ 1521
Database SID ................ fmw
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: Y
Aug 5, 2013 7:58:53 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/fmw/emca_2013_08_05_19_58_21.log.
Aug 5, 2013 7:58:54 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Aug 5, 2013 8:05:56 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Aug 5, 2013 8:06:02 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Aug 5, 2013 8:07:14 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Aug 5, 2013 8:07:17 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Aug 5, 2013 8:07:17 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Aug 5, 2013 8:08:07 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Aug 5, 2013 8:08:07 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Aug 5, 2013 8:08:53 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Aug 5, 2013 8:08:53 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Aug 5, 2013 8:10:16 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Aug 5, 2013 8:10:17 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://localhost.localhost.com:5500/em <<<<<<<<<<<
Aug 5, 2013 8:10:28 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /u01/app/oracle/product/11.2.0/dbhome_1/localhost.localhost.com_fmw/sysman/config/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager Configuration completed successfully
FINISHED EMCA at Aug 5, 2013 8:10:28 PM

[oracle@localhost ~]$

How To Install MySQL in Linux

Most of the Linux flavors comes with MySQL.  If you want use MySQL, my recommendation is that you download the latest and stable version of MySQL and install it yourself. Later you can upgrade it to the latest version when it becomes available. In this post, I will discuss how I installed MySQL on Red hat or Oracle Linux platform.

1. Download the latest stable release of MySQL :

Download MySQL from mysql.com. Please download the community edition of MySQL for your appropriate Linux platform. I downloaded the “Red Hat Enterprise Linux 5 RPM (x86_64bit)”. Make sure to download MySQL Server, Client and “Headers and libraries” from the download page.

• MySQL-client-5.5.32-1.rhel5.x86_64.rpm
• MySQL-devel-5.5.32-1.rhel5.x86_64.rpm
• MySQL-server-5.5.32-1.rhel5.x86_64.rpm

2. Remove the existing default MySQL that came with the Linux distribution.

Do not perform this on an system where the MySQL database is getting used by some application.

[local-host]# rpm -qa | grep -i mysql
mysql-5.0.22-2.1.0.1
mysqlclient10-3.23.58-4.RHEL5.4

[local-host]# rpm -e mysql --nodeps

No Mysql is running on the host.

3. Install the downloaded MySQL package

Install the MySQL Server and Client packages as shown below.

[local-host]# rpm -ivh MySQL-server-5.5.32-1.rhel5.x86_64.rpm MySQL-client-5.5.32-1.rhel5.x86_64.rpm



## if it will not work shows any message like below : use force option to install MYSQL-server :



# rpm -ivh --force  MySQL-server-5.6.16-1.el6.x86_64.rpm


file /usr/share/mysql/czech/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.52-1.el6_0.1.x86_64
        file /usr/share/mysql/danish/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.52-1.el6_0.1.x86_64
        file /usr/share/mysql/dutch/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.52-1.el6_0.1.x86_64
Preparing...   ########################################### [100%]
1:MySQL-client ########################################### [ 50%]
2:MySQL-server ########################################### [100%]
This will also display the following output and start the MySQL daemon automatically.
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h medica2 password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

Starting MySQL.[  OK  ]
Giving mysqld 2 seconds to start
** If it is not get started automatically find below how to start 


Install the “Header and Libraries” that are part of the MySQL-devel packages.


[local-host]# rpm -ivh MySQL-devel-5.5.32-0.rhel5.x86_64.rpm
Preparing...   ########################################### [100%]
1:MySQL-devel  ########################################### [100%]

4.  Perform post-install security activities on MySQL.
At a bare minimum you should set a password for the root user as shown below:

[local-user]# /usr/bin/mysqladmin -u root password 'My2Secure$Password'
The best option is to run the mysql_secure_installation script that 
will take care of all the typical security related items on the MySQL as
 shown below. On a high level this does the following items:

Change the root password
Remove the anonymous user
Disallow root login from remote machines
Remove the default sample test database



5.  Verify the MySQL installation:
You can check the MySQL installed version by performing mysql -V as shown below:

[local-host]# mysql -V
mysql  Ver 14.14 Distrib 5.1.25-rc, for redhat-linux-gnu (i686) using readline 5.1
Connect to the MySQL database using the root user and make sure the connection is successfull.

[local-host]# mysql -u root -p  /** if it is not connect start service first **/
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Follows the steps below to stop and start MySQL
[local-host]# service mysql status
MySQL running (12588)                                      [  OK  ]
[local-host]# service mysql stop
Shutting down MySQL.                                       [  OK  ]
[local-host]# service mysql start
Starting MySQL.                                            [  OK  ]
Issues I faced: 
1) When you are not able to connect MySQL, start the mysql service first.
  #service mysql start.
2) When you are not able to connect from the remote host give the permission as     below.
   [local-host]# mysql -u root -p
    mysql> GRANT ALL PRIVILEGES ON *.* TO 'root@'%' IDENTIFIED BY 'PASSWORD' WITH    GRANT OPTION;
If you want to give a permission to connect Mysql to a perticular remote machine give that IP instead of '%'.
Here '%' represents all Remote Hosts.

Importing 11.2.0.3 database schema into 11.2.0.1 version

Hi,
Today I have got an a mail from my manager says, import a production database schema into our UAT environment for testing purpose.

Source database version : 11.2.0.3
Used method for Export: Data pump
Target database version : 11.2.0.1.

I had bit confusion in my mind that we need to import from higher version to lower version..is that possible ?
Other side have clarity that both are 11g ...I think it may work (no any prior exp like this)..
So started importing ..

Source log file:

Steps I followed :
1) Dump file having 125 MB size. so I thought using the default data pump directory.
Select * from dba_directories;

2) I copied dump file,log file into above DATA_PUMP_DIR location.
3)Connected to server ran bash profile.
4)Created two table spaces as per requirement. We need not to create same name table spaces, we can use remap_tablespaces parameter. But i got an errors related spatial indexes.

In some of the blogs i found we can ran out of these issues if we gave create table, sequence like privileges to the imported schema.But no luck to me...

5) Executed the below query by going to respected data pump directory..

Target Log file :

Alert Log file errors in 11.2.0.1 DB.Fatal NI connect error 12537 and 12547.

Hi,

I have found below messages in one of our 11.2.0.1 Oracle development DB alert log file.

1)

  Fatal NI connect error 12537, connecting to:

  (LOCAL=NO)

  VERSION INFORMATION:

TNS for Linux: Version 11.2.0.1.0 - Production

Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

  Time: 09-DEC-2013 20:27:35

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12537

    

TNS-12537: TNS:connection closed

    ns secondary err code: 12560

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

opiodr aborting process unknown ospid (12547) as a result of ORA-609

Mon Dec 09 20:27:35 2013

2) 

Fatal NI connect error 12547, connecting to:

 (LOCAL=NO)

  VERSION INFORMATION:

TNS for Linux: Version 11.2.0.1.0 - Production

Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

  Time: 09-OCT-2013 23:05:36

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12547

    

TNS-12547: TNS:lost contact

    ns secondary err code: 12560

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

opiodr aborting process unknown ospid (10574) as a result of ORA-609

Wed Oct 09 23:05:56 2013

Clearing Resource Manager plan via parameter

Thu Oct 10 03:03:13 2013

3) 

Fatal NI connect error 12170.

  VERSION INFORMATION:

TNS for Linux: Version 11.2.0.1.0 - Production

Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

  Time: 10-OCT-2013 03:03:13

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12535

    

TNS-12535: TNS:operation timed out

    ns secondary err code: 12560

    nt main err code: 505

    

TNS-00505: Operation timed out

    nt secondary err code: 110

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.12.161)(PORT=55325))

Thu Oct 10 05:49:45 2013

4) Errors in file /u01/app/diag/rdbms/epm/epm/trace/epm_j000_15056.trc  (incident=31383):

ORA-00600: internal error code, arguments: [17059], [0x0B98A25A0], [0x0B98A2E38], [0x0C24173C8], [], [], [], [], [], [], [], []

Incident details in: /u01/app/diag/rdbms/epm/epm/incident/incdir_31383/epm_j000_15056_i31383.trc

Thu Jan 02 21:34:12 2014

Errors in file /u01/app/diag/rdbms/epm/epm/trace/epm_j000_15056.trc  (incident=31384):

ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [17059], [0x0B98A25A0], [0x0B98A2E38], [0x0C24173C8], [], [], [], [], [], [], [], []

Incident details in: /u01/app/diag/rdbms/epm/epm/incident/incdir_31384/epm_j000_15056_i31384.trc

Thu Jan 02 21:34:12 2014

Trace dumping is performing id=[cdmp_20140102213412]

Pfile and Startup Process :

 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileidm.ora

System parameters with non-default values:

  processes                = 500

  sessions                 = 772

  memory_target            = 2400M

  control_files            = "/u01/app/oracle/oradata/idm/control01.ctl"

  control_files            = "/u01/app/oracle/oradata/idm/control02.ctl"

  db_block_size            = 8192

  compatible               = "11.2.0.0.0"

  undo_tablespace          = "UNDOTBS1"

  remote_login_passwordfile= "EXCLUSIVE"

  db_domain                = "bias.com"

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=idmXDB)"

  audit_file_dest          = "/u01/app/oracle/admin/idm/adump"

  audit_trail              = "DB"

  db_name                  = "idm"

  open_cursors             = 800

  diagnostic_dest          = "/u01/app/oracle"

• Thu Oct 17 21:42:11 2013 PMON started with pid=2, OS id=2818 
• Thu Oct 17 21:42:11 2013 VKTM started with pid=3, OS id=2820 at elevated priority
• Thu Oct 17 21:42:11 2013 GEN0 started with pid=4, OS id=2824 
• VKTM running at (10)millisec precision with DBRM quantum (100)ms
• Thu Oct 17 21:42:11 2013 DIAG started with pid=5, OS id=2826 
• Thu Oct 17 21:42:11 2013 DBRM started with pid=6, OS id=2828 
• Thu Oct 17 21:42:11 2013 PSP0 started with pid=7, OS id=2830 
• Thu Oct 17 21:42:11 2013 DIA0 started with pid=8, OS id=2832 
• Thu Oct 17 21:42:11 2013 MMAN started with pid=9, OS id=2834 
• Thu Oct 17 21:42:11 2013 DBW0 started with pid=10, OS id=2836 
• Thu Oct 17 21:42:11 2013 LGWR started with pid=11, OS id=2838 
• Thu Oct 17 21:42:11 2013 CKPT started with pid=12, OS id=2840 
• Thu Oct 17 21:42:11 2013 SMON started with pid=13, OS id=2842 
• Thu Oct 17 21:42:11 2013 RECO started with pid=14, OS id=2844 
• Thu Oct 17 21:42:11 2013 MMON started with pid=15, OS id=2846 

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

Thu Oct 17 21:42:11 2013

MMNL started with pid=16, OS id=2848 

starting up 1 shared server(s) ...

ORACLE_BASE not set in environment. It is recommended

that ORACLE_BASE be set in the environment

Reusing ORACLE_BASE from an earlier startup = /u01/app/oracle

Thu Oct 17 21:42:12 2013

ALTER DATABASE   MOUNT

Successful mount of redo thread 1, with mount id 961017972

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

Thu Oct 17 21:42:16 2013

ALTER DATABASE OPEN

Beginning crash recovery of 1 threads

Started redo scan

Completed redo scan

Alert Log Errors :

ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option

Hii,

To day I have faced the problem with  ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option errors while opening the database which is created newly.
There may be 3 possibilites to get above errors. I have solved my problem by using 3 rd option.

1) giving wrong or old ORACLE_HOME or PATH variables while opening newly upgraded DAtabase.
Solution:
set or export ORACLE_HOME= <newly upgraded ORACLE_HOME>
set or export PATH=<newly upgraded PATH variables>

2) After upgraing the ORACLE_HOME, if you missed to run catupgrd.sql script, we will get above errors while starting the datbase.

Solution:

sqlplus / as sysdba
sql>startup upgrade
sql>@<ORACLE_HOME>/rdbms/admin/catupgrd.sql
sql>@<ORACLE_HOME>/rdbms/admin/utlrp.sql
sql>shut immediate
sql>startup

3) After creation of new database if we missed to execute catalog.sql,catproc.sql or  executed with errors also we will get above errors.

Solution:

sqlplus / as sysdba
sql>startup upgrade
sql>@<ORACLE_HOME>/rdbms/admin/catalog.sql
sql>@<ORACLE_HOME>/rdbms/admin/catproc.sql
sql>shut immediate
sql>startup

Queries I often used on table spaces sizes and space used

To check the allocated space and used space in tablespaces:

1) SELECT d.status "Status", d.tablespace_name "Name",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
        TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') "Used (M)",
        TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
        FROM sys.dba_tablespaces d,
        (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
        (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
        d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
        (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status
        "Status", d.tablespace_name "Name",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
        TO_CHAR(NVL(t.bytes,0)/1024/1024,'99999999.999') "Used (M)",
        TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
        FROM sys.dba_tablespaces d,
        (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
        (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
        WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
        D.EXTENT_MANAGEMENT LIKE 'LOCAL' AND D.CONTENTS LIKE 'TEMPORARY';

2)
 SELECT /* + RULE */  DF.TABLESPACE_NAME "Tablespace",
       DF.BYTES / (1024 * 1024 * 1024) "Size (GB)",
       SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
       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;
 SELECT * FROM dba_temp_free_space; 

2)  To check the schemas sizes in database: 

SELECT s.owner,SUM (s.BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB
FROM dba_segments s
GROUP BY S.OWNER;

3) Learn about TEMPORARY   TABALE SPACE HERE

4) Query to find the long running queries in database :

select distinct sesid,serial, spid pid, osuser,schemaname,machine, floor(last_call_et/60) last_call, EVENT,program, sql_text, Mins, P1, P2, P3,hash_value
from
    (select sesid,serial, spid, sql_text, osuser,schemaname,machine, last_call_et, floor((sysdate - to_date(decode(start_time,'// ::',to_char(sysdate,'dd/mm/yyyy
hh24:mi:ss'), start_time), 'dd/mm/yyyy hh24:mi:ss'))* 1440) Mins, EVENT,program, P1, P2, P3,hash_value
    from
        (SELECT s.sid sesid,s.serial# serial,s.program, t.sql_text, s.osuser,s.schemaname,s.machine, s.last_call_et, substr(last_load_time,9,2) || '/' || substr(last_load_time,6,2) ||'/' ||
substr(last_load_time,1,4) || ' ' ||
        substr(last_load_time,12,2) || ':' || substr(last_load_time,15,2) || ':' || substr(last_load_time,18,2) start_time, U.event, U.p1, U.p2, U.p3,
        V.spid,T.hash_value
        FROM v$session S,
        v$sql T,
        v$session_wait U,
        v$process V
        WHERE S.status = 'ACTIVE'
        AND S.sql_address = T.address
        and exists (select 1 from v$sqltext_with_newlines y where y.hash_value = S.sql_hash_value)
        and T.SQL_TEXT <> 'COMMIT'
        AND S.sql_hash_value = T.hash_value
        AND U.SID = S.SID
        and V.addr = S.paddr
        and S.type='USER'
        )
    )
order by LAST_CALL desc;

5) 

## To truncate all tables ###

1.      Select 'truncate TABLE schemaowner.'||table_name||'  ;' FROM dba_tables where owner='schemaowner';

           

2.     

## To move all the Tables and indexes from one TBS to another ##

 

1.      select 'ALTER TABLE schemaowner.'||table_name|| ' MOVE TABLESPACE  BI_EDW_DEV_DATA;' FROM dba_tables  where owner='schemaowner';

2.      select 'ALTER INDEX schemaowner.'||index_name||' REBUILD TABLESPACE BI_EDW_DEV_IDX ;' FROM DBA_INDEXES WHERE OWNER='schemaowner';