ORA-12983: cannot drop all columns in a table

Cause :- When you want to drop all column from a table you get this error. Table must remain at least one or more column.

SQL> CREATE TABLE TEST_COLUMN(ID NUMBER);

Table created.

SQL>
SQL> ALTER TABLE TEST_COLUMN DROP COLUMN ID;
ALTER TABLE TEST_COLUMN DROP COLUMN ID
*
ERROR at line 1:
ORA-12983: cannot drop all columns in a table


SQL>

ORA-12729: invalid character class in regular expression

Cause :- Invalid character set using in regular expression.

SQL> CREATE TABLE REGEXP_LIKE_TEST (ID NUMBER, NAME VARCHAR2(20), SALARY NUMBER);

Table created.

SQL>
SQL> ALTER TABLE REGEXP_LIKE_TEST
  2  ADD CONSTRAINT REGEXP_LIKE_TEST_CONST CHECK (REGEXP_LIKE(NAME,'[[:ALPHA:]]')) NOVALIDATE ;

Table altered.

SQL>
SQL> INSERT INTO REGEXP_LIKE_TEST VALUES(10, 'RAJIB',5000);
INSERT INTO REGEXP_LIKE_TEST VALUES(10, 'RAJIB',5000)
                 *
ERROR at line 1:
ORA-12729: invalid character class in regular expression

Solution :- Using valid character set within regular expression.


SQL> ALTER TABLE REGEXP_LIKE_TEST
  2  DROP CONSTRAINT REGEXP_LIKE_TEST_CONST;

Table altered.

SQL> ALTER TABLE REGEXP_LIKE_TEST
  2  ADD CONSTRAINT REGEXP_LIKE_TEST_CONST CHECK (REGEXP_LIKE(NAME,'[[:alpha:]]')) NOVALIDATE ;

Table altered.

SQL> INSERT INTO REGEXP_LIKE_TEST VALUES(10, 'RAJIB',5000);

1 row created.

SQL>

Multi-Table In oracle database.

Multi-table insert is a new feature of Oracle 9i Release 1 (9.0). An extension to INSERT..SELECT, this feature enables us to define multiple insert targets for a source data.

SQL> CONN RND/rnd
Connected.
SQL>
SQL>
SQL>
SQL> CREATE TABLE MULTI1 (ID NUMBER, NAME VARCHAR2(300), SALARY NUMBER);

Table created.

SQL>
SQL> CREATE TABLE MULTI2(ID NUMBER, NAME VARCHAR2(300), SALARY NUMBER);

Table created.

SQL>
SQL> INSERT ALL
  2  WHEN EMPLOYEE_ID >=140 THEN
  3  INTO MULTI1(ID, NAME, SALARY)
  4  VALUES (EMPLOYEE_ID, FIRST_NAME, SALARY)
  5  ELSE
  6  INTO MULTI2(ID, NAME, SALARY)
  7  VALUES (EMPLOYEE_ID, FIRST_NAME, SALARY)
  8  SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
  9  FROM EMP;

107 rows created.

SQL>
SQL> SELECT MAX(ID), MIN(ID)  FROM MULTI1;

   MAX(ID)    MIN(ID)
---------- ----------
       206        140

SQL>
SQL> SELECT MAX(ID), MIN(ID)  FROM MULTI2;

   MAX(ID)    MIN(ID)
---------- ----------
       139        100

SQL>

General comparison function uses in oracle database.

There are two types of general comparison function uses in oracle database.

1. GREATEST    Highest value from set of values.
2. LEAST    Lowest value from set of values.

EXAMPLE :-

SQL> select greatest(1,2,3,4) from dual;

GREATEST(1,2,3,4)
-----------------
                4

SQL>
SQL> select least(1,2,3,4) from dual;

LEAST(1,2,3,4)
--------------
             1

SQL> SELECT GREATEST(SALARY,COMMISSION_PCT), LEAST(SALARY,COMMISSION_PCT)
  2  FROM EMPLOYEES
  3  WHERE COMMISSION_PCT IS NOT NULL;

GREATEST(SALARY,COMMISSION_PCT) LEAST(SALARY,COMMISSION_PCT)
------------------------------- ----------------------------
                          14000                           .4
                          13500                           .3
                          12000                           .3
                          11000                           .3
                          10500                           .2
                          10000                           .3
                           9500                          .25
                           9000                          .25
                           8000                           .2
35 rows selected.


SQL> SELECT GREATEST('ABC','AAB','AAA') FROM DUAL;

GRE
---
ABC

SQL> SELECT GREATEST('ABC','ABD','AAA') FROM DUAL;

GRE
---
ABD

SQL> SELECT GREATEST('ABC','ABCD','AAA') FROM DUAL;

GREA
----
ABCD

SQL> SELECT LEAST('ABC','ABCD','AAA') FROM DUAL;

LEA
---
AAA

Using EXTRACT Function In Oracle Database

EXTRACT:- This is oracle datetime function. Using this function extracts and returns the value of a specified datetime field from a datetime or interval expression.

EXAMPLE:-

SQL> select sysdate from dual;

SYSDATE
---------
13-MAY-14

SQL>
SQL> select extract(month from trunc(sysdate)) from dual;

EXTRACT(MONTHFROMTRUNC(SYSDATE))
--------------------------------
                               5

SQL> select extract(day from trunc(sysdate)) from dual;

EXTRACT(DAYFROMTRUNC(SYSDATE))
------------------------------
                            13

SQL> select extract(year from trunc(sysdate)) from dual;

EXTRACT(YEARFROMTRUNC(SYSDATE))
-------------------------------
                           2014
SQL> SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;

EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
                             1998

SQL> SELECT last_name, employee_id, hire_date, department_id
  2    FROM employees
  3    WHERE EXTRACT(YEAR FROM TO_DATE(hire_date, 'DD-MON-RR')) > 2007
  4    AND department_id=80
  5    ORDER BY hire_date;

LAST_NAME                 EMPLOYEE_ID HIRE_DATE DEPARTMENT_ID
------------------------- ----------- --------- -------------
Johnson                           179 04-JAN-08            80
Marvins                           164 24-JAN-08            80
Zlotkey                           149 29-JAN-08            80
Lee                               165 23-FEB-08            80
Ande                              166 24-MAR-08            80
Kumar                             173 21-APR-08            80
Banda                             167 21-APR-08            80

7 rows selected.

How to Reclaim / Resize Table space.

Using this statement you can find how to resize you datafile.

SQL> ALTER DATABASE DATAFILE 'D:\APP\ORADATA\STLBAS\ORBSYS01.DBF' RESIZE 1122M;

Database altered.

Scripts:
-----------

SELECT    'ALTER DATABASE DATAFILE '''
       || FILE_NAME
       || ''' RESIZE '
       || US
       || 'M;'
  FROM (  SELECT DF.TABLESPACE_NAME,
                 DF.FILE_NAME,
                 SUM (DF.BYTES) / 1024 / 1024/1024 "Total Size (MB)",
                 SUM (FS.BYTES) / 1024 / 1024/1024 "Total Free (MB)",
                 ROUND (
                    ( (SUM (DF.BYTES) / 1024 / 1024/1024)
                     - (SUM (FS.BYTES) / 1024 / 1024/1024))
                    + 50)
                    US,
                 TRUNC ( ( (SUM (FS.BYTES) / (SUM (DF.BYTES)) * 100)), 1)
                 || '%'
                    "Free Percent"
            FROM DBA_DATA_FILES DF, DBA_FREE_SPACE FS
           WHERE FS.TABLESPACE_NAME = DF.TABLESPACE_NAME
                 AND DF.TABLESPACE_NAME NOT IN
                        ('USERS', 'UNDOTBS1', 'SYSAUX', 'SYSTEM')
        GROUP BY DF.TABLESPACE_NAME, DF.FILE_NAME
        ORDER BY 1)


Finding Data file Size :
----------------------------

  SELECT DF.TABLESPACE_NAME,
         DF.FILE_NAME,
         ROUND (SUM (DF.BYTES) / 1024 / 1024 / 1024, 2) "Total Size (MB)",
         ROUND (SUM (FS.BYTES) / 1024 / 1024 / 1024, 2) "Total Free (MB)",
         ROUND (
            ( (SUM (DF.BYTES) / 1024 / 1024 / 1024)
             - (SUM (FS.BYTES) / 1024 / 1024 / 1024))
            + 50)
            US,
         TRUNC ( ( (SUM (FS.BYTES) / (SUM (DF.BYTES)) * 100)), 1) || '%'
            "Free Percent"
    FROM DBA_DATA_FILES DF, DBA_FREE_SPACE FS
   WHERE FS.TABLESPACE_NAME = DF.TABLESPACE_NAME
         AND DF.TABLESPACE_NAME NOT IN
                ('USERS', 'UNDOTBS1', 'SYSAUX', 'SYSTEM')
GROUP BY DF.TABLESPACE_NAME, DF.FILE_NAME
ORDER BY 3 DESC

How to find tablespace size in oralc database.

Using this query you can find out you tablespace size and used percentage.

SQL> SELECT DF.TABLESPACE_NAME, SUM(DF.BYTES)/1024/1024 "Total Size (MB)", SUM(FS.BYTES)/1024/1024 "Total Free (MB)",
  2  TRUNC(((SUM(FS.BYTES)/(SUM(DF.BYTES))*100)),1)||'%' "Free Percent"
  3  FROM DBA_DATA_FILES DF, DBA_FREE_SPACE FS
  4  WHERE FS.TABLESPACE_NAME=DF.TABLESPACE_NAME
  5  AND DF.TABLESPACE_NAME NOT IN ('USERS','UNDOTBS1','SYSAUX','SYSTEM')
  6  GROUP BY DF.TABLESPACE_NAME;

TABLESPACE_NAME                Total Size (MB) Total Free (MB) Free Percent
------------------------------ --------------- --------------- -----------------------------------------
DATABAK                                    512             511 99.8%
MIG_BCBL                                  1024            1023 99.9%
ORBLOG                                    1024         801.375 78.2%
ORBIND                                 1072104         1163.25 .1%
RECLAIM_TS                                  52          1.5625 3%
DATA                                      9920        758.4375 7.6%

TABLESPACE_NAME                Total Size (MB) Total Free (MB) Free Percent
------------------------------ --------------- --------------- -----------------------------------------
ORBSYS                                    2048         1012.75 49.4%
EXAMPLE                                    300         21.5625 7.1%
ORBDT2                                    1024             626 61.1%
ORBMAV                                    2048         987.625 48.2%
ORBIMG                                    3324             181 5.4%
SHRINK_TEST                                  5               4 80%
ORBMAS                                    1024        788.8125 77%
ORBDT3                                    2248          58.875 2.6%
TBSTRAN                                   3072         1022.25 33.2%
ORBITS                                    9216        262.6875 2.8%

24 rows selected.

How to find Locked object in Oracle Database.

Hello dear, Using this example you can find out your locked object list from your database. You must to have DBA prevailed.
 

SQL> CREATE TABLE AAA(ID NUMBER);

Table created.

SQL> INSERT INTO  AAA VALUES(10);

1 row created.

SQL> SELECT A.SID,A.SERIAL#, A.USERNAME,C.OS_USER_NAME,A.TERMINAL,
  2  B.OBJECT_ID,B.OBJECT_NAME OBJECT_NAME
  3  FROM V$SESSION A, DBA_OBJECTS B, V$LOCKED_OBJECT C
  4  WHERE A.SID = C.SESSION_ID
  5  AND B.OBJECT_ID = C.OBJECT_ID;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
OS_USER_NAME                   TERMINAL          OBJECT_ID
------------------------------ ---------------- ----------
OBJECT_NAME
--------------------------------------------------------------------------------
       195          7 RND
SPFTL\rajib.pradhan            N1020                215166
AAA

SQL> commit;

Commit complete.

SQL> SELECT A.SID,A.SERIAL#, A.USERNAME,C.OS_USER_NAME,A.TERMINAL,
  2  B.OBJECT_ID,B.OBJECT_NAME OBJECT_NAME
  3  FROM V$SESSION A, DBA_OBJECTS B, V$LOCKED_OBJECT C
  4  WHERE A.SID = C.SESSION_ID
  5  AND B.OBJECT_ID = C.OBJECT_ID;

no rows selected

SQL>

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

Cause :- This error occur when you are try to drop Global Temporary Table which are locked.

Solution :- To solve this error you need to truncate table / kill this session / or exit form session and login again.


SQL> create global temporary table temp_data(id number, name varchar2(200)) on commit  PRESERVE ROWS;

Table created.

SQL> insert into temp_data values(10, 'Rajib');

1 row created.

SQL> select * from temp_data;

        ID
----------
NAME
--------------------------------------------------------------------------------
        10
Rajib


SQL>  commit;

Commit complete.

SQL>  select * from temp_data;

        ID
----------
NAME
--------------------------------------------------------------------------------
        10
Rajib


SQL>  drop table temp_data;
 drop table temp_data
            *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use


SQL> SELECT SID  FROM V$LOCK
  2  WHERE ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS
  3  WHERE OWNER='RND'
  4  AND OBJECT_NAME='TEMP_DATA');

       SID
----------
       195

SQL> TRUNCATE TABLE temp_data;

Table truncated.

SQL>  drop table temp_data;

Table dropped.

SQL> SELECT SID  FROM V$LOCK
  2  WHERE ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS
  3  WHERE OWNER='RND'
  4  AND OBJECT_NAME='TEMP_DATA');

no rows selected

SQL>

ORA-00972: identifier is too long

SQL> Select * from identifier_too_long_cause_extends_30;
Select * from identifier_too_long_cause_extends_30
              *
ERROR at line 1:
ORA-00972: identifier is too long


Cause : Oracle identifiers are not allowed to exceed 30 characters in length.

Solution : Please use Table name (object name) between 1 to 30 character long.