Learn Database Concepts

There may be situation where you are trying to create a new database similar to old one and it is a fresh install and you need to get the Tablespaces DDL from the old one. This query will be very help full. SQL>Set pages 999; SQL>set long 90000; SQL>spool ddl_list.sql SQL>select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb; SQL>spool off Sample Output : "  CREATE TABLESPACE "USERS" DATAFILE   '/u02/oracle/oradata/ORCL/datafiles/users01.dbf' SIZE 5242880   AUTOEXTEND ON NEXT 52428800 MAXSIZE 20000M   LOGGING ONLINE PERMANENT BLOCKSIZE 8192   EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT  NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO    ALTER DATABASE DATAFILE   '/u02/oracle/oradata/ORCL/datafiles/users01.dbf' RESIZE 2097152000"; "  CREATE TABLESPACE "TOOLS" DATAFILE   '/u02/oracle/oradata/ORCL/datafiles/tools01.dbf' SIZE 67108864   LOGGING ONLINE PERMANENT BLOCKSIZE 8192  ...

How to audit failed logon attempts Oracle Audit -- failed connection Background: In some situation DBA team wants to audit failed logon attempts when "unlock account"  requirement becomes frequently and user cannot figure out who from where is using incorrect password to cause account get locked. Audit concern: Oracle auditing may add extra load and require extra operation support. For this situation DBA only need audit on failed logon attempts and do not need other audit information. Failed logon attempt is only be able to track through Oracle audit trail, logon trigger does not apply to failure logon attempts Hint: The setting here is suggested to use in a none production system. Please evaluate all concern and load before use it in production. Approach: 1. Turn on Oracle audit function by set init parameter:                audit_trail=DB Note: database installed by manual script, the audit function may not tur...

Recently I have installed a client on a local machine and I was trying to connect the database. I know I have everything right but still I was getting this error. C:\Users\localhost>tnsping orcl TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 19-NOV-2 014 15:22:45 Copyright (c) 1997, 2010, Oracle.  All rights reserved. Used parameter files: C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora TNS-03505: Failed to resolve name C:\Users\localhost>   I went back to check the Tnsnames.ora file to see if there is anything wrong in it. But no luck I couldn't find anything.     Sample Tnsnames.ora on my machine.     orcl =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     )     (CONNECT_DATA =       (SID = orcl)     )   )     Wondered what c...

Oracle AUDIT Explained Enabling Auditing in Database #1 You can specify DB,EXTENDED in either of the following ways: ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE; ALTER SYSTEM SET AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE; However, do not enclose DB, EXTENDED in quotes, for example: ALTER SYSTEM SET AUDIT_TRAIL='DB, EXTENDED' SCOPE=SPFILE; OS Directs all audit records to an operating system file. #2 Directs audit records to the database audit trail (the SYS.AUD$ table), except for mandatory  and SYS audit records, which are always written to the operating system audit trail #3 The operating system and database audit trails both capture many of the same types of actions. Operating System Audit Record Equivalent DBA_AUDIT_TRAIL View Column SESSIONID                                  ...

Today I encountered a problem while importing a scehma into my local database. I have exported a schema from ORCL (lets say) using expdp command. I tried to import it to another database and I was getting this error.   ORA-31633: unable to create master table [oracle@orcl dpump]$ impdp sam/oracle directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2 Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-31626: job does not exist ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 1038 ORA-01031: insufficient privileges I tried again and again same error. The...

Applying “version 4″ Time Zone Files on an Oracle Database Applying “version 4″ Time Zone Files on an Oracle Database if your timezone file version is less than 4 Yesterday I was upgrading the database form 10.2.0.3 to 10.2.0.5 on RHEL 5, In readme of the 10.2.0.5 patch i came across the timezoe files, We need to upgarde the timezone files of the base database to 4, is minimum requirement. I found my timezone files 3, you can use following query to find the timezone of the database : SQL> select * from v$timezone_file; FILENAME        VERSION ------------ ---------- timezlrg.dat          3   If  your database versions are 9.2.0.8 & 10.2.0.4,your  timezone file version will be 4 by default, upgrading to 11.1.0.6.0  or 11.2.0.1.0 we need timezone version 4, If your database is 9.2.0.7, we need to upgarde it to 9.2.0.8  or database versions are 10.2.0.1, 10.2.0.2, 10.2....