Reclaim unused space in oracle datafiles

After dropping large number of tables or users the space occupied by tables/users will not be released in oracle.
As the allocation happened for the first time the space is occupied and it may be required in future, thus the space occupied remain reserved for future requirements.
The space occupied by data-files remain reserved unless an explicit way to resize has been performed.
One of the ways to reduce the data-file size is to know the free-size of the data-files, so as to reduce the data-file size based on the free-size.

SQL> select a.file_name,round(a.bytes/1024/1024) totalsize,b.freesize from dba_data_files a, (select file_id,round(sum(bytes/1024/1024)) freesize from dba_free_space group by file_id) b where a.file_id=b.file_id(+);
FILE_NAME
--------------------------------------------------------------------------------
 TOTALSIZE   FREESIZE
---------- ----------
/oracle/dbs/DBORA/system01.dbf
       800          1
/oracle/dbs/DBORA/DBORAINDEX.dbf
      6274       3283
/oracle/dbs/DBORA/sysaux01.dbf
       550         36

From the above result the freesize of the datafiles for the database are listed. The free-size is listed in MB, relating with that the data-file sizes can be resized as like below.

SQL> alter  database datafile '/oracle/dbs/DBORA/DBORAINDEX.dbf' RESIZE 5G;
Database altered.
SQL> select a.file_name,round(a.bytes/1024/1024) totalsize,b.freesize from dba_data_files a, (select file_id,round(sum(bytes/1
024/1024)) freesize from dba_free_space group by file_id) b where a.file_id=b.file_id(+);
FILE_NAME
--------------------------------------------------------------------------------
 TOTALSIZE   FREESIZE
---------- ----------
/oracle/dbs/DBORA/system01.dbf
       800          1
/oracle/dbs/DBORA/DBORAINDEX.dbf
      5120       2129
/oracle/dbs/DBORA/sysaux01.dbf
       550         36

Oracle tables starts with BIN$

DROP table puts the tables in recycle bin, and it can be removed from recycle bin by using PURGE command.
The tables in recycle bin can be viewed by 'show recyclebin';

SQL> DROP TABLE RECYCLE;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
RECYCLE          BIN$FGO4f+kYTnW0fc0rTXSvHQ==$0 TABLE        2014-09-12:19:11:48
SQL>
SQL> PURGE TABLE RECYCLE
  2  ;
Table purged.
SQL> show recyclebin;
SQL>

Detailed description can be found here

http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#ADMIN01511

ORA-01119: error in creating database file

After dropping tablespace with including contents, may end up with "ORA-01119: error in creating database file".

Reasons could be
1. Not deleting the datafile with "INCLUDING CONTENTS AND DATAFILES"
2. Provided directory path does not exist
3. Error accessing the directory (read/write permission or misspelling of directory names)
4. Datafile already existing in the current directory

Here is the case with not deleting datafile with drop tablespace,

SQL> DROP TABLESPACE DATATS INCLUDING CONTENTS;
Tablespace dropped.

SQL> CREATE TABLESPACE DATATS LOGGING DATAFILE '/home/database/DATATS1.dbf' SIZE 1G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
*
ERROR at line 1:
ORA-01119: error in creating database file
'/home/database/DATATS1.dbf'
ORA-27038: created file already exists
Additional information: 1

SQL> DROP TABLESPACE DATATS INCLUDING CONTENTS and datafiles;
DROP TABLESPACE DATATS INCLUDING CONTENTS and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'DATATS' does not exist

$ ls -lrt
total 63169492
-rw-r----- 1 oracle dbs   359723476 Oct  6 16:59 DATATS1.dbf
-rw-r----- 1 oracle dbs   863962592 Oct  6 23:03 temp01.dbf
-rw-r----- 1 oracle dbs   831239922 Oct  7 10:24 system01.dbf
-rw-r----- 1 oracle dbs   513430411 Oct  7 10:24 sysaux01.dbf
-rw-r----- 1 oracle dbs   108758112 Oct  7 10:24 redo03a.log
-rw-r----- 1 oracle dbs   104858342 Oct  7 10:24 redo02a.log
-rw-r----- 1 oracle dbs   222677423 Oct  7 10:24 INDEXTS1.dbf
.
.
.

$ rm -r DATATS1.dbf

SQL> CREATE TABLESPACE DATATS LOGGING DATAFILE '/home/database/DATATS1.dbf' SIZE 1G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.

using BETWEEN Operator with Integer and Text Value

BETWEEN operator in SQL selects values within a range. The values can be numbers or text.

SQL> Select * from boys;

    BOY_ID BOY                      TOY_ID DEPT
---------- -------------------- ---------- --------------------
         1 Jana                          3 elec
         6 Sam                           8 aero
         2 Giri                          3 csc
         4 Giri                          7 csc 
BETWEEN operator on integer values are selected as below

SQL> Select * from boys where TOY_ID BETWEEN 1 AND 7;

    BOY_ID BOY                      TOY_ID DEPT
---------- -------------------- ---------- --------------------
         1 Jana                          3 elec
         2 Mess                          3 csc
         4 Mess                          7 csc

On using text value with BETWEEN operator, the text value is compared against the column values and result set is produced.
In this example the boy 'Sam' is not selected as 'Sam' is  > 'S'. (i.e. Same > Sam > S)

SQL> Select * from boys where boy BETWEEN 'J' AND 'S';

    BOY_ID BOY                      TOY_ID DEPT
---------- -------------------- ---------- --------------------
         1 Jana                          3 elec
         2 Mess                          3 csc
         4 Mess                          7 csc

On using full text 'Sam' on BETWEEN operator, the row holding column value 'Sam' 'is selected.

SQL> Select * from boys where boy BETWEEN 'J' AND 'Sam';

    BOY_ID BOY                      TOY_ID DEPT
---------- -------------------- ---------- --------------------
         1 Jana                          3 elec
         6 Sam                           8 aero
         2 Mess                          3 csc
         4 Mess                          7 csc






 

Insert string with apostrophe(') in sql

when using Insert statement to inset string type fields and the values has an apostrophe('), how

the inset statements can be handled in such cases.


SQL> insert into donuts values('jelly's,9);
insert into donuts values('jelly's,9)
                                   *
ERROR at line 1:
ORA-00917: missing comma


SQL> insert into donuts values('jelly''s,9);
ERROR:
ORA-01756: quoted string not properly terminated

The Error displayed here is due to string data types that are delimited by the single quotation mark and the apostrophe(') is considered as the end of the string  delimiter.

The possible way is to escape single apostrophe (') with an extra apostrophe as below.


SQL> insert into donuts values('jelly''s',9);

1 row created.

Now the string with apostrophe is inserted into the table.

SQL> select * from donuts;

NAME	PRICE
----------	----------
jelly	9
jell's	9

count tables for each user in oracle database

sql query to count no of tables owned by each user in oracle database

SELECT COUNT(*), owner  FROM ALL_TABLES group by owner;

SQL> SELECT COUNT(*), owner  FROM ALL_TABLES group by owner;
  COUNT(*) OWNER
---------- ------------------------------
        39 WIN
        39 REDON
      121 PANK
    3456 ANA
        32 MONK
        72 PAT
        93 GREF
    3456 CHIN
        10 KILM
        20 BLOG
      155 SYSTEM
   
11 rows selected.