ORACLE TIPS

  • USER/SCHEMA:

Create new user

 CREATE USER USRPPTEST1
 IDENTIFIED BY VALUES 'USRPPTEST1'
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK;
 GRANT DBA TO USRPPTEST1;
 GRANT CONNECT TO USRPPTEST1;
 GRANT RESOURCE TO USRPPTEST1;
 ALTER USER USRPPTEST1 DEFAULT ROLE ALL;
 GRANT UNLIMITED TABLESPACE TO USRPPTEST1;

Add privileges to new user

 GRANT CREATE SESSION TO user_name;
 GRANT CREATE TABLE TO user_name;
 GRANT CREATE VIEW TO user_name;
 GRANT CREATE TRIGGER TO user_name;
 GRANT CREATE VIEW TO user_name;

Dba privilege to a user

 Grant DBA to user;
 Revoke DBA from user;

Revoking tablespace access to a user

 ALTER USER Scott QUOTA 0 ON Users;

  • EXPORT/IMPORT

Dump export and import commands

Export Command

exp USRTRAIN/USRTRAIN@PRDDB file=exp_usrtrain_at_prddb_20110316.dmp log=exp_usrtrain_at_prddb_20110316.log consistent=Y compress=Y full=y

Import Command

imp USRPRD/USRPRD@PRDDB file=exp_usrtrain_at_prddb_20110316.dmp fromuser=USRTRAIN touser=USRPRD commit=y ignore=y

Note: If you need to import the dump on a new tablespace, open the dump with a text editor and replace the old tablespace name with the new.

Data dump procedure

  • Give privileges
    SQL> grant connect, resource to USRPRD;
    SQL> grant exp_full_database to USRPRD;
  • Define datapump directory
     SQL> create directory dump_dir as 'G:\Orabackup\export';
  • Leave the rights to the user so that he can read and write to the directory
     SQL> grant read, write on directory dump_dir to USRPRD;
  • Export from 11g to 10G
  expdp test1/test1 file=11g1.dmp version='10.2.0.1' dumpfile=my10gv.dmp directory=dump_dir
  • Import to another schema
 impdp USRREPORT/USRREPORT@prddb remap_schema=USRPRD:USRREPORT remap_tablespace=USRPRD_D1:USRREPORT_D DIRECTORY=dump_dir DUMPFILE=EXPDPUSRIGPRD20121223_23_00.DMP

  • TABLESPACES

Return which datafile is used for each tablespace

SELECT * FROM DBA_DATA_FILES;

Set the tablespace datafile autoextend

ALTER DATABASE DATAFILE 'C:\ORACLEXE\ORADATA\XE\toto.dbf' AUTOEXTEND ON;

  Increase the tablespace datafile size

ALTER DATABASE DATAFILE 'C:\ORACLEXE\ORADATA\XE\toto.dbf' resize 8500M;

List tablespace objects

REPHEADER PAGE CENTER 'LISTE DES OBJETS DANS TABLESPACE'
 SET LINESIZE 150
 SET PAGESIZE 900
 COL "TABLESPACE" FORMAT A10
 COL "SCHEMA" FORMAT A8
 COL "NOM OBJET" FORMAT A20
 COL "TYPE OBJET" FORMAT A10
 COL "TAILLE (Mb)" FORMAT 9999.99
 COL "FICHIER DE DONNEES" FORMAT A30
 SELECT e.tablespace_name AS "TABLESPACE",
 e.owner AS "SCHEMA",
 e.segment_name AS "NOM OBJET",
 e.segment_type AS "TYPE OBJET",
 ROUND(Sum(e.bytes)/1024/1024,2) AS "TAILLE (Mb)",
 d.file_name AS "FICHIER DE DONNEES"
 FROM dba_extents e INNER JOIN dba_data_files d
 ON ( e.file_id = d.file_id )
 WHERE e.tablespace_name ='Nom du tablespace'
 GROUP BY e.tablespace_name,
 e.owner,
 e.segment_name,
 e.segment_type,
 d.file_name
 ORDER BY 2,"TAILLE (Mb)" DESC;

Return the percentage of used space for each tablespace

select a.TABLESPACE_NAME TABLESPACE,
 a.BYTES/1024/1024 MO_UTILISES,
 b.BYTES/1024/1024 MO_LIBRES,
 b.largest/1024/1024 PLUS_LARGE,
 round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) POURCENTAGE_UTILISES
 from
 (
 select TABLESPACE_NAME,
 sum(BYTES) BYTES
 from dba_data_files
 group by TABLESPACE_NAME
 )
 a,
 (
 select TABLESPACE_NAME,
 sum(BYTES) BYTES ,
 max(BYTES) largest
 from dba_free_space
 group by TABLESPACE_NAME
 )
 b
 where a.TABLESPACE_NAME=b.TABLESPACE_NAME
 order by ((a.BYTES-b.BYTES)/a.BYTES) desc

  • PERFORMANCE

Rebuild indexes

set autocommit off
 set echo off
 set verify off
 set feedback off
 set space 0
 set linesize 200
 set heading off
 set pagesize 0

spool c:\temp\rebuild_index.sql

select 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name||' compute statistics;'
 from dba_indexes
 where owner like 'USRDGUPROD'
 /
 spool off
 set feedback on
 set echo on

start c:\temp\rebuild_index.sql

commit
 /

Recompute statistics

begin
 dbms_utility.analyze_schema('USRIGPRD','COMPUTE');
 end;
 /

  • DEBUG

Find locks

SELECT sess.sid,sess.serial#,lo.oracle_username,lo.os_user_name,ao.object_name,lo.locked_mode FROM v$locked_object lo,dba_objects ao,v$session sess
 WHERE ao.object_id = lo.object_id AND lo.session_id = sess.sid;

Kill session

Alter system kill session 'sid,serial#' immediate

Find limit number of processes and sessions

select * from v$resource_limit where resource_name in ('processes','sessions');

To view dba_temp_files and Alter (shrink) temp file size

select tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name like 'TEMP%';
 alter tablespace temp shrink space keep 256M;

  • ORA ERRORS

ORA-00031 

    Definition   ORA-00031 : session marked for kill

Find out the thread id (needed to be os level kill session):

select spid, osuser, s.program from v$process p,v$session s
where p.addr=s.paddr

Then in a Windows command prompt:

C:> orakill ORACLE_SID spid

  ORA-12518

     Definition     ORA-12518: TNS:listener could not hand off client connection

sqlplus /nolog
SQL> connect sys/magerna@sg4pdev as sysdba
SQL> shutdown immediate
SQL> startup
SQL> exit
lsnrctl reload

 – ORA-01113, ORA-01110

  Definition   ORA-01113: file 11 needs media recovery
                     ORA-01110: data file 11: ‘E:\ORACLE\ORADATA\SGD\TESTTM_D.DBF

SQL> recover datafile 'E:\ORACLE\ORADATA\SGD\TESTTM_D.DBF'
Media recovery complete.

 – ORA-01940

     Definition   ORA-01940: cannot drop a user that is currently connected

SELECT s.sid, s.serial#, s.status, p.spid 
  FROM v$session s, v$process p 
 WHERE s.username = 'TEST'
  AND p.addr(+) = s.paddr
 /

Pass actual SID and SERIAL# values for user TEST then drop user:

ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>' /

ORA-28000

    Definition    ORA-28000 : The account is locked

If FAILED_LOGIN_ATTEMPTS is not set to UNLIMITED, adjust as follows:

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

If a user is already locked, you can unlock it as follows:

ALTER USER <username> ACCOUNT UNLOCK;

Note: Don’t forget to change the part <username>

ORA-28001

   Definition    ORA-28001 : The password has expired

As SYS:

  ALTER USER USRIGPRD IDENTIFIED BY USRIGPRD ACCOUNT UNLOCK;

then:

  alter profile DEFAULT limit password_life_time UNLIMITED;

ORA-00031: session marked for kill

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: