What is Oracle Instant Client?

•Instant Client allows you to run your applications
without installing the standard Oracle client or
having an ORACLE_HOME.
•While using significantly less disk space than
before.
•SQL*Plus can be used with Instant Client

Advertisements

Index information

–show index information. showDex.sql
set linesize 150
col owner for a10
col index_name for a25
col table_name for a20 hea TNAME
col table_owner for a10 hea TOWNER
col compression for a3 trunc hea CMP
col index_type for a10 trunc
col degree for a6
col ANLYZED for a7 trunc
col STATUS for a3 trunc hea STS
col ini_trans for 999 hea ITR
col max_trans for 999 hea MTR
col num_rows for 9999999 hea ROWS
col tablespace_name for a10 hea TBNAME
select owner,index_name,index_type,STATUS,INI_TRANS,MAX_TRANS,COMPRESSION,degree,TABLESPACE_NAME,num_rows,
to_char(LAST_ANALYZED, ‘DDMonYY’) ANLYZED,table_owner,table_name from DBA_INDEXES where owner in
(‘SYS’,’SYSTEM’,’OUTLN’,’DBSNMP’,’OUTLN’,’ORACLE’,’PERFSTAT’,’OPS$ORACLE’) order by 1,2;

Monitoring: database extents

1. Objects whose next extent is too big to fit in the tablespace (ORA-1653, ORA-1654, ORA-1655):

SELECT OWNER,SEGMENT_NAME, round(NEXT_EXTENT/(1024*1024),1) next_in_MB,
S.TABLESPACE_NAME,round(MAX_FREE_BYTES/(1024*1024),1) max_free_MB,
autoextensible_bytes/(1024*1024) autoextend_MB
FROM SYS.DBA_SEGMENTS S,
(SELECT TABLESPACE_NAME,MAX(BYTES) MAX_FREE_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(select tablespace_name, sum(maxbytes)-sum(bytes) autoextensible_bytes
from dba_data_files
group by tablespace_name) d
WHERE
S.NEXT_EXTENT > ( F.MAX_FREE_BYTES + d.autoextensible_bytes )
AND S.TABLESPACE_NAME=F.TABLESPACE_NAME and
f.tablespace_name=d.tablespace_name;

2. Objects that are near their MAXEXTENTS (ORA-1632):

select I.OWNER, INDEX_NAME, EXTENTS, I.MAX_EXTENTS
from dba_indexes I, DBA_SEGMENTS S
where I.OWNER=S.OWNER AND I.INDEX_NAME=S.SEGMENT_NAME AND
S.SEGMENT_TYPE=’INDEX’
AND I.max_extents < 100000
AND I.MAX_EXTENTS – S.EXTENTS < 10;

RMAN – When do you require DBID for database recovery

dbid integer  

specifies the db identifier, which is a unique 32-bit identification number computed when the database is created. The DBID column of the V$DATABASE data dictionary view displays the identifier. The DBID is also stored in the DB table of the recovery catalog.

The set dbid command is useful for restoring the control file when each of these conditions is met:

  • The control file has been lost and must be restored from a backup.

  • You are using a recovery catalog.

  • Multiple databases registered in the recovery catalog share a database name.

  • You receive the “RMAN-20005: target database name is ambiguous” message when you attempt to restore the control file.

If these conditions are not met, RMAN will correctly identify the control file to restore, so you do not need to use the set dbid command.

RMAN accepts set dbid only if you have not yet connected to the target database, i.e., set dbid must precede the connect target command. If the target database is mounted, then RMAN verifies that the user-specified DBID matches the DBID from the database; it not, RMAN signals an error. If the target database is not mounted, RMAN uses the user-specified DBID to restore the control file. Once you have restored the control file, you can mount the database to restore the rest of the database.

Different Types of Shells to Declare

#!/usr/bin/sh OR #!/bin/sh Declares a Bourne shell
#!/usr/bin/ksh OR #!/bin/ksh Declares a Korn shell
#!/usr/bin/csh OR #!/bin/csh Declares a C shell
#!/usr/bin/bash OR #!/bin/bash Declares a Bourne-Again shell