Tag Archives: Oracle Database

ORA-12154 while connecting oracle database with OracleClient and Oracle 10g Client from vs2008 web project in Win7/2008 x64

When you opening connection to Oracle database in web project of Visual Studio 2008, you always get below error:
ORA-12154: TNS:could not resolve the connect identifier specified

But your tnsnames.ora is correct, sqlplus or other projects (like console/win form) can connect successfully.

Solution:
Upgrade Oracle 10g Client to 10.2.0.3 or upper to fix this strange bug.

Advertisements

Oracle: get tablespace/data file space usage information

whenever sqlerror exit sql.sqlcode;
whenever oserror exit failure;

set trimout off
set trimspool on
set pagesize 1000
set linesize 135

col file_id format 999
col file_name format a40
col tablespace_name format a15
col size_mb format 9,999,999
col maxsize_mb format 9,999,999
col ae format a3
col free_mb format 9,999,999
col free_pct format 999

spool space.status

SELECT   a.file_id, a.file_name, a.tablespace_name, TRUNC (a.BYTES / 1024 / 1024) size_mb,
         TRUNC (a.MAXSIZE / 1024 / 1024) maxsize_mb, a.autoextensible ae,
         TRUNC (  DECODE (a.autoextensible,
                          'YES', a.MAXSIZE - a.BYTES + NVL (b.free, 0),
                          'NO', NVL (b.free, 0)
                         )
                / 1024
                / 1024
               ) free_mb,
         TRUNC (DECODE (a.autoextensible,
                        'YES', (a.MAXSIZE - a.BYTES + NVL (b.free, 0))
                         / a.MAXSIZE
                         * 100,
                        'NO', NVL (b.free, 0) / a.MAXSIZE * 100
                       )
               ) free_pct
    FROM (SELECT   file_id, file_name, tablespace_name, autoextensible, BYTES,
                   DECODE (autoextensible, 'YES', maxbytes, BYTES) MAXSIZE
              FROM dba_data_files) a,
         (SELECT   file_id, tablespace_name, SUM (BYTES) free
              FROM dba_free_space
          GROUP BY file_id, tablespace_name) b
   WHERE a.file_id = b.file_id(+) AND a.tablespace_name = b.tablespace_name(+)
         AND a.autoextensible = 'NO'
ORDER BY a.file_name
/

spool off

set trimout on
exit;

Oracle: move table, index or blob from one tablespace to another

Use below sql to generate the alter commands.
v_owner: owner of the object
v_source_tablespace: current tablespace
v_target_tablespace: the “move to” tablespace

select decode( segment_type, 'TABLE', 
                       segment_name, table_name ) order_col1,
       decode( segment_type, 'TABLE', 1, 2 ) order_col2,
      'alter ' || segment_type || ' ' || owner || '.' || decode( segment_type, 'TABLE', segment_name, '"' || segment_name || '"' ) ||  
      decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) || --chr(10) ||
      ' tablespace ' || :v_target_tablespace || --chr(10) ||
      --' storage ( initial ' || initial_extent || 
      --' next ' || next_extent || --chr(10) ||
      --' minextents ' || min_extents || 
      --' maxextents ' || max_extents || --chr(10) ||
      --' pctincrease ' || pct_increase || 
      --' freelists ' || freelists || 
      --')' || 
        ';'
  from (select segment_name, segment_type, owner from dba_segments where owner=:v_owner and tablespace_name=:v_source_tablespace), 
       (select table_name, index_name from dba_indexes where owner=:v_owner and tablespace_name=:v_source_tablespace)
 where segment_type in ( 'TABLE', 'INDEX' )
   and segment_name = index_name (+)
union 
select table_name, 3, 
    'alter table ' || owner || '.' || table_name || 
    ' move lob(' || column_name || 
    ') store as (tablespace ' || :v_target_tablespace || '_BLOB);'
    from dba_lobs where owner=:v_owner and tablespace_name=:v_source_tablespace
order by 1, 2
/