Archive for February, 2012

oracle datapump backup restore in Linux

You can easily take data pump backup of an Oracle Schema in Linux environment following those easy steps. I have taken backup of SCOTT Schema and restore it again from this Backup.

However, SCOTT Schema is locked in Default Installation. You can unlock it using following sql command.


ALTER USER SCOTT ACCOUNT UNLOCK;

Continue reading

Advertisements

Oracle Listener Commands in Linux

Here, I tried to put together some very basic Oracle Listener Commands in Linux.

Oracle Listener Status:

$ lsnrctl status

Oracle Listener Start:

$ lsnrctl start

Continue reading

Tablespace status in Oracle

I find this query (googling) that produces nice formatted tablespace status. Here is the query


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;

Continue reading

Creating, Dropping and Altering Tablespace using SQL Commands in Oracle

You can create Tablespace using this SQL Command.


create tablespace table_space_name
datafile '/app/oracle/product/11.2.0/oradata/practiceab/table_space_test.dbf' size 100k
autoextend on next 10m maxsize 300m;

To drop any Tablespace in Oracle use this following query.


DROP TABLESPACE table_space_name
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

Continue reading

Oracle Database shutdown and startup Commands

Sometimes, Programmers may have to go through some Database Administrative Tasks on Oracle Development Database. Here, I tried to put together Some Linux/Oracle Commands useful to shutdown and start up Oracle Database.

First of all, Log on to System(Linux) as “oracle” user or su to “oracle” using the following command.


$ su - oracle

Continue reading

Schema Name and Size using SQL

You can use this query to retrieve Schema Name and Size from Oracle Database.

 
select 
owner SchemaName, 
round(sum(bytes)/1024/1024/1024,2) TotalSizeGB 
from dba_segments 
group by owner 
order by 2 desc 

Thanks
A Rahim Khan