SQL Plus Easy Connect error TNS-12504

One method to connect to Oracle database is using Easy Connect, meaning that you don'n need any client side configuration.

With Easy Connect, you supply all information that is required for the Oracle Net connection as part of the connect string. Easy Connect connection strings take the following form:
<username>/<password>@<hostname>:<listener port>/<service name>
The listener port and service name are optional. If the listener port is not provided, Oracle Net assumes that the default port of 1521 is being used. If the service name is not provided, Oracle Net assumes that the database service name and host name provided in the connect string are identical.
SQL> connect sys@192.168.40.222/oratest as sysdba
raise error 
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
To avoid this error you must give also the password for user connecting to database
SQL> conn sys/passwd@192.168.40.222/oratest as sysdba
Connected.
SQL>

Get DDL for tables in Oracle

1. Use dbms_metadata
select dbms_metadata.get_ddl('TABLE','TEST','SCHEMA') from dual;
2. Using exp/imp
exp user/password file=dump.dmp tables=table1,table2 rows=n
imp user/password file=dump.dmp tables=table1,table2 show=y
3. Using DDL Wizard
www.ddlwizard.com

Enterprise Manger - can't locate compEMdbconsole.pm in @INC

When runnig emctl start dbconsole, I receive folowing error:


can't locate compEMdbconsole.pm in @INC


This is because when running emctl, it runs emctl from GRID_HOME, because PATH variable set in .profile was set to PATH=$PATH:GRID_HOME/bin:$ORACLE_HOME/bin
To view which emctl executable is run, 


which emctl


If it returns the path of GRID_HOME, then put ORACLE_HOME first in .profile.

Oracle parameters

Oracle views:
V$PARAMETER - lists all parameters in current session
V$SPPARAMETER - lists all spfile defined parameters. For all parameters that are not defined in SPFILE, ISSPECIFIED="FALSE'
V$PARAMETER2 - lists all session parameters
V$SYSTEM_PARAMETER - lists all system  parameters that are currently used by instance

Find SQL execution plans

There are several ways to display execution plans.
1. SQL Plus
If you run SQL, just put in SQL Plus


SET AUTOTRACE ON before running sql statement.



SQL> set autotrace on
SQL> select * from test.test;


no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         34  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
        330  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL>

2. Using EXPLAIN PLAN from SQL Plus and the query table plan_table where the explain plan is stored, or user DBMS_XPLAN.DISPLAY



SQL> delete plan_table;


2 rows deleted.


SQL> explain plan for select /* +rule */ * from test;


Explained.


SQL> select
  substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
  object_name                                                              "Object"
from
  plan_table
start with id = 0
connect by prior id=parent_id;  2    3    4    5    6    7  ^R
connect by prior id=parent_id;


SELECT STATEMENT ()




 TABLE ACCESS (FULL)
TEST





SQL> select * from table(dbms_xplan.display);


Plan hash value: 1357081020


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |    65 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     5 |    65 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)


12 rows selected.


SQL>

3. Using DBMS_XPLAN.DISPLAY_CURSOR to view explain plan for a specific sql_id

SQL> select /* TST */ * from test;

         2

         1

         1

         1

         1

SQL> select sql_id, sql_text from v$sql where sql_text like '%TST%';

92ca0n47j90vn

select /* TST */ * from test

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('92ca0n47j90vn',0));

SQL_ID  92ca0n47j90vn, child number 0

-------------------------------------

select /* TST */ * from test

Plan hash value: 1357081020

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |

|   1 |  TABLE ACCESS FULL| TEST |     5 |    65 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement (level=2)

17 rows selected.

SQL>

or use following SQL to view explain plans for sqls that contains some string

SQL> SELECT t.*

FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%  2  TST%';

SQL_ID  92ca0n47j90vn, child number 0

-------------------------------------

select /* TST */ * from test

Plan hash value: 1357081020

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |

|   1 |  TABLE ACCESS FULL| TEST |     5 |    65 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement (level=2)

Solaris 10 - Add disks available for Oracle ASM

# format
Searching for disks...done


AVAILABLE DISK SELECTIONS:
       0. c0d1 <DEFAULT cyl 3913 alt 2 hd 255 sec 63>
          /pci@0,0/pci-ide@7,1/ide@1/cmdk@1,0
       1. c1t0d0 <DEFAULT cyl 1302 alt 2 hd 255 sec 63>
          /pci@0,0/pci15ad,1976@10/sd@0,0
       2. c1t1d0 <DEFAULT cyl 2556 alt 2 hd 128 sec 32>
          /pci@0,0/pci15ad,1976@10/sd@1,0
Specify disk (enter its number): 2
selecting c1t1d0
[disk formatted]


FORMAT MENU:
        disk       - select a disk
        type       - select (define) a disk type
        partition  - select (define) a partition table
        current    - describe the current disk
        format     - format and analyze the disk
        fdisk      - run the fdisk program
        repair     - repair a defective sector
        label      - write label to the disk
        analyze    - surface analysis
        defect     - defect list management
        backup     - search for backup labels
        verify     - read and display labels
        save       - save new disk/partition definitions
        inquiry    - show vendor, product and revision
        volname    - set 8-character volume name
        !<cmd>     - execute <cmd>, then return
        quit
format> format> partition
PARTITION MENU:
        0      - change `0' partition
        1      - change `1' partition
        2      - change `2' partition
        3      - change `3' partition
        4      - change `4' partition
        5      - change `5' partition
        6      - change `6' partition
        7      - change `7' partition
        select - select a predefined table
        modify - modify a predefined partition table
        name   - name the current table
        print  - display the current table
        label  - write partition map and label to the disk
        !<cmd> - execute <cmd>, then return
        quit
partition> modify
Select partitioning base:
        0. Current partition table (original)
        1. All Free Hog
Choose base (enter number) [0]? 1


Part      Tag    Flag     Cylinders        Size            Blocks
  0       root    wm       0               0         (0/0/0)           0
  1       swap    wu       0               0         (0/0/0)           0
  2     backup    wu       0 - 2555        4.99GB    (2556/0/0) 10469376
  3 unassigned    wm       0               0         (0/0/0)           0
  4 unassigned    wm       0               0         (0/0/0)           0
  5 unassigned    wm       0               0         (0/0/0)           0
  6        usr    wm       0               0         (0/0/0)           0
  7 unassigned    wm       0               0         (0/0/0)           0
  8       boot    wu       0 -    0        2.00MB    (1/0/0)        4096
  9 alternates    wm       0               0         (0/0/0)           0


Do you wish to continue creating a new partition
table based on above table[yes]? yes
Free Hog partition[6]? 7
Enter size of partition '0' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '1' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '3' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '4' [0b, 0c, 0.00mb, 0.00gb]: 5g
`5.00gb' is out of range
Enter size of partition '4' [0b, 0c, 0.00mb, 0.00gb]: 4,9g
Please specify units in either b(blocks), c(cylinders), m(megabytes) or g(gigabytes)
Enter size of partition '4' [0b, 0c, 0.00mb, 0.00gb]: 4.9g
Enter size of partition '5' [0b, 0c, 0.00mb, 0.00gb]:
Enter size of partition '6' [0b, 0c, 0.00mb, 0.00gb]:


Part      Tag    Flag     Cylinders        Size            Blocks
  0       root    wm       0               0         (0/0/0)           0
  1       swap    wu       0               0         (0/0/0)           0
  2     backup    wu       0 - 2555        4.99GB    (2556/0/0) 10469376
  3 unassigned    wm       0               0         (0/0/0)           0
  4 unassigned    wm       1 - 2509        4.90GB    (2509/0/0) 10276864
  5 unassigned    wm       0               0         (0/0/0)           0
  6        usr    wm       0               0         (0/0/0)           0
  7 unassigned    wm    2510 - 2555       92.00MB    (46/0/0)     188416
  8       boot    wu       0 -    0        2.00MB    (1/0/0)        4096
  9 alternates    wm       0               0         (0/0/0)           0


Okay to make this the current partition table[yes]? yes
Enter table name (remember quotes): "asm2"


Ready to label disk, continue? y


partition> verify
`verify' is not expected.
partition> q


FORMAT MENU:
        disk       - select a disk
        type       - select (define) a disk type
        partition  - select (define) a partition table
        current    - describe the current disk
        format     - format and analyze the disk
        fdisk      - run the fdisk program
        repair     - repair a defective sector
        label      - write label to the disk
        analyze    - surface analysis
        defect     - defect list management
        backup     - search for backup labels
        verify     - read and display labels
        save       - save new disk/partition definitions
        inquiry    - show vendor, product and revision
        volname    - set 8-character volume name
        !<cmd>     - execute <cmd>, then return
        quit
format> verify


Primary label contents:


Volume name = <        >
ascii name  = <DEFAULT cyl 2556 alt 2 hd 128 sec 32>
pcyl        = 2558
ncyl        = 2556
acyl        =    2
bcyl        =    0
nhead       =  128
nsect       =   32
Part      Tag    Flag     Cylinders        Size            Blocks
  0 unassigned    wm       0               0         (0/0/0)           0
  1 unassigned    wm       0               0         (0/0/0)           0
  2     backup    wu       0 - 2555        4.99GB    (2556/0/0) 10469376
  3 unassigned    wm       0               0         (0/0/0)           0
  4 unassigned    wm       1 - 2509        4.90GB    (2509/0/0) 10276864
  5 unassigned    wm       0               0         (0/0/0)           0
  6 unassigned    wm       0               0         (0/0/0)           0
  7 unassigned    wm    2510 - 2555       92.00MB    (46/0/0)     188416
  8       boot    wu       0 -    0        2.00MB    (1/0/0)        4096
  9 unassigned    wm       0               0         (0/0/0)           0


format> q
bash-3.2# cd /dev/rdsk
bash-3.2# ls -lL c1t1d0s4
crw-r-----   1 root     sys       30, 132 Jan 26 16:29 c1t1d0s4
bash-3.2# cd /asmdisk
bash-3.2# ls
disk1  
bash-3.2# mknod disk2 c 30 132
bash-3.2# ls -l
total 0
crw-r--r--   1 oracle   oinstall  30, 68 Jan 26 16:31 disk1
crw-r--r--   1 root     root      30, 132 Jan 26 16:31 disk2
bash-3.2# chown oracle:oinstall disk2
#
#

Truncate tables from one schema

1. Create script to disable foreign keys


select 'ALTER TABLE'||' '||OWNER||'.'||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||' ;' from DBA_CONSTRAINTS where CONSTRAINT_TYPE = 'R' and OWNER='schema_owner' and STATUS = 'ENABLED';


2. Create script to enable foreign keys


SELECT 'ALTER TABLE'||' '||owner||'.'||table_name||' ENABLE CONSTRAINT '||constraint_name||' ;' FROM dba_constraints WHERE constraint_type = 'R' and owner='schema_owner' and status = 'ENABLED';


3. Create script to truncate all tables from schema


select 'TRUNCATE TABLE '||OWNER||'.'||TABLE_NAME||' ;' from DBA_TABLES where OWNER='SICAP_LM_03';


4. Run scripts


@DISABLE_CONSTR.SQL
@TRUNCATE_TABLES.SQL
@ENABLE_CONSTR.SQL

OR

You can use this script after you connect with user you want to truncate tables from.

-- disable all FK constraints from user schema
begin

  for C1 in (select TABLE_NAME, CONSTRAINT_NAME from user_CONSTRAINTS where CONSTRAINT_TYPE = 'R' and STATUS = 'ENABLED')
  loop
      begin
         execute immediate 'ALTER TABLE '||C1.TABLE_NAME||' DISABLE CONSTRAINT '||C1.CONSTRAINT_NAME ;
         dbms_output.put_line('Disable '||c1.constraint_name||' on table '||c1.table_name);
      exception
         when others then
               dbms_output.put_line(sqlerrm||' '||c1.table_name);
      end;        
   END LOOP;
 end;
/

-- truncate all tables from user schema
begin
  for c2 in (select table_name from user_tables)        
   loop
      begin
         execute immediate 'truncate table '||c2.table_name;
         dbms_output.put_line('truncate table '||c2.table_name);
      exception
         when others then
               dbms_output.put_line(sqlerrm||' '||c2.table_name);
      end;        
   end LOOP;
end;
/

-- enable all FK constraints from user schema
begin
    for C3 in (select TABLE_NAME, CONSTRAINT_NAME from user_CONSTRAINTS where CONSTRAINT_TYPE = 'R' and STATUS = 'DISABLED')
  loop
      begin
         execute immediate 'ALTER TABLE '||C3.TABLE_NAME||' ENABLE CONSTRAINT '||C3.CONSTRAINT_NAME ;
         dbms_output.put_line('Enable '||c3.constraint_name || ' on table '||c3.table_name);
      exception
         when others then
               dbms_output.put_line(sqlerrm||' '||c3.table_name);
      end;        
   end LOOP;
end;
/

Oracle INTERVAL datatype examples

SQL> select interval '300' month from dual;
INTERVAL'300'MONTH
---------------------------------------------------------------------------
+25-00


1 row selected.


SQL> select interval '1' month from dual;


INTERVAL'1'MONTH
---------------------------------------------------------------------------
+00-01


1 row selected.




SQL> select interval '2-1' year to month from dual;


INTERVAL'2-1'YEARTOMONTH
---------------------------------------------------------------------------
+02-01


1 row selected.




SQL> select interval '02:02:02' hour to second from dual;


INTERVAL'02:02:02'HOURTOSECOND
---------------------------------------------------------------------------
+00 02:02:02.000000


1 row selected.


SQL> select interval '+23 02:02:02'  day to second from dual;


INTERVAL'+2302:02:02'DAYTOSECOND
---------------------------------------------------------------------------
+23 02:02:02.000000


1 row selected.




SQL> select INTERVAL '11:12:10.1234567' HOUR TO SECOND from dual;


INTERVAL'11:12:10.1234567'HOURTOSECOND
---------------------------------------------------------------------------
+00 11:12:10.123457


1 row selected.


SQL> select INTERVAL '11:12:10.1234567' HOUR TO SECOND(2) from dual;


INTERVAL'11:12:10.1234567'HOURTOSECOND(2)
---------------------------------------------------------------------------
+00 11:12:10.12


1 row selected.




SQL> select INTERVAL '10.1234567' SECOND(2,4) from dual
  2  /


INTERVAL'10.1234567'SECOND(2,4)
---------------------------------------------------------------------------
+00 00:00:10.1235


1 row selected.


SQL> select INTERVAL '10.1234567' SECOND(5,4) from dual;


INTERVAL'10.1234567'SECOND(5,4)
---------------------------------------------------------------------------
+00000 00:00:10.1235


1 row selected.


SQL> select INTERVAL '10.1234567' SECOND(1,4) from dual;


INTERVAL'10.1234567'SECOND(1,4)
---------------------------------------------------------------------------
+0 00:00:10.1235


1 row selected.

Change the location of afiedt.buf generated by SQL Plus

By default, when using ed in SQL Plus, the file afiedt.buf is created in current directory. This can be a problem because there may be generated multiple files, depending from where you launch SQL Plus.
To override this, just put following lines


SET EDITFILE "/tmp/afiedt.buf"
DEFINE _EDITOR='vi'


in $ORACLE_HOME/sqlplus/admin/glogin.sql, and there will be just on file generated.
The second line specifies that default editor for editfile is VI.