Archive for March, 2012

Copy File using SCP in Linux/AIX

You can copy file using command to another Linux/AIX Location using SCP.


scp filename username@userhost:/directory/subdirectory/

Thanks
A Rahim Khan

Advertisements

Datapump import/Export Tables/Schemas

For specific table import from datapump backup use this script. it will truncate existing one.


impdp system/*** tables=sibl.account remap_schema=sibl:jan28 directory=backup_dir
dumpfile=ABABIL.28Jan2012.02.38PM.dmp logfile=imp_log_of_28012012.log 
TABLE_EXISTS_ACTION=TRUNCATE remap_tablespace=temp:ababil_temp_ts,UNDOTBS1:ABABIL_UNDO_TS

To import whole schema use this one.


impdp system/****** schemas=sibl,ababilfe remap_schema=sibl:month41,ababilfe:monthfe41
directory=backup_dir dumpfile=ABABIL.26Apr2011.09.04PM.dmp logfile=imp_log_of_260411.log
remap_tablespace=temp:ababil_temp_ts,UNDOTBS1:ABABIL_UNDO_TS

Thanks
A Rahim Khan

Table corresponding Tablespace, Datafile in Oracle

This following query retrieves table name with corresponding tablespace name and datafile name.


select 
dba_tables.table_name, 
dba_tables.tablespace_name,
dba_data_files.file_name 
from dba_tables 
inner join dba_data_files 
on 
dba_tables.tablespace_name = dba_data_files.tablespace_name


Output:


TABLE_NAME                     TABLESPACE_NAME                FILE_NAME                           
------------------------------ ------------------------------ ------------------------------ 
IND$                           SYSTEM                         /database/ababil/system01.dbf                                                                                                                                                              
COL$                           SYSTEM                         /database/ababil/system01.dbf                                                                                                                                                              
CLU$                           SYSTEM                         /database/ababil/system01.dbf                                                                                                                                                              
TAB$                           SYSTEM                         /database/ababil/system01.dbf                                                                                                                                                              
ICOL$                          SYSTEM                         /database/ababil/system01.dbf

Thanks
A Rahim Khan

Listing Active Statement/Privellege Audit Options in Oracle

Listing Active Statement Audit Options:


SELECT * FROM DBA_STMT_AUDIT_OPTS;

USER_NAME               AUDIT_OPTION         SUCCESS         FAILURE
--------------------    -------------------  ----------      ---------
JWARD                   SESSION              BY SESSION      BY SESSION
SWILLIAMS               SESSION              BY SESSION      BY SESSION
                        LOCK TABLE           BY ACCESS       NOT SET

Listing Active Privilege Audit Options:


SELECT * FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME           PRIVILEGE            SUCCESS      FAILURE
------------------- -------------------- ---------   ----------
ALTER USER          BY SESSION           BY SESSION


Continue reading

Undo Tablespace in Oracle Database

First of all, check out your undo settings using this following SQL Command.


SQL> show parameter undo

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
undo_management                      string      AUTO                           
undo_retention                       integer     900                            
undo_tablespace                      string      UNDOTBS1 

You can create one or more undo tablespace but you must have only one active undo tablespace at a time. You can change your active undo tablespace or even retention period. Continue reading

Useful Linux Commands for Database Administrations

I am going to put together some useful Linux/AIX Commands for Database Administration.


$ echo $ORACLE_SID
 oracleinstanceid

# if you don't get any output, use this following one to set ORACLE_SID.

$ export ORACLE_SID = oracleinstanceid

# Now, check out ORACLE_HOME, PATH value.

$ echo $ORACLE_HOME
 oracle home directory

$ echo $PATH
 path value

Continue reading