How to put dumps in multiple locations

The %U causes file name to be generated by oracle and sequentially like rest01.dmp, rest02.dmp and etc.

Below is an example which will take dump of tablespace users and each dump size will be 300K and it will span in C:\, D:\ and E:\ drive each.

SQL> create or replace directory d as 'd:';
Directory created.

SQL> create or replace directory e as 'e:';
Directory created.

SQL> create directory c as 'c:';
Directory created.

$expdp maximsg/a dumpfile=d:part1.dmp,e:part2.dmp,c:rest%U.dmp filesize=300K tablespaces=users

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsStarting "MAXIMSG"."SYS_EXPORT_TABLESPACE_01": maximsg/******** dumpfile=d:part1.dmp,e:part2.dmp,c:rest%U.dmp filesize=300K tablespaces=usersEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 20.12 MBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "CR_2"."O_CDR_TEST" 310.9 KB 2166 rows. . exported "CR_2"."O_CDR" 484.4 KB 2606 rows. . exported "CR_2"."USER_IP" 126 KB 3403 rows. . exported "CR_2"."O_CDR_TEMP" 33.72 KB 361 rows. . exported "CR_2"."USERS" 42.57 KB 230 rows. . exported "SCOTT"."DEPT" 5.656 KB 4 rows. . exported "SCOTT"."EMP" 7.851 KB 15 rows. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsMaster table "MAXIMSG"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

******************************************************************************Dump file set for MAXIMSG.SYS_EXPORT_TABLESPACE_01 is:D:\PART1.DMPE:\PART2.DMPC:\REST01.DMPC:\REST02.DMPC:\REST03.DMPJob "MAXIMSG"."SYS_EXPORT_TABLESPACE_01" successfully completed at 12:16:33For dumpfile=d:part1.dmp,e:part2.dmp,c:rest%U.dmp,first in D: drive part1.dmp will be created with sized 300K.Then in E: drive part2.dmp will be created with sized 300K.Then all remaining parts of the dump will be created in C: drive each with 300K (possibly except last one due to dump size is not multiple of 300K) and their name will be like rest01.dmp, rest02.dmp etc.

Diferencia Entre DDL, DML and DCL commands?

1)Query-select

2)DDL - Data Definition Language: statements used to define the database structure or schema. Some examples:
* CREATE - to create objects in the database
* ALTER - alters the structure of the database
* DROP - delete objects from the database
* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
* COMMENT - add comments to the data dictionary
* RENAME - rename an object

3)DML - Data Manipulation Language: statements used for managing data within schema objects. Some examples:
* SELECT - retrieve data from the a database
* INSERT - insert data into a table
* UPDATE - updates existing data within a table
* DELETE - deletes all records from a table, the space for the records remain
* MERGE - UPSERT operation (insert or update)
* CALL - call a PL/SQL or Java subprogram
* EXPLAIN PLAN - explain access path to data
* LOCK TABLE - control concurrency

4)DCL - Data Control Language. Some examples:

* GRANT - gives user's access privileges to database
* REVOKE - withdraw access privileges given with the GRANT command

5)TCL - Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

6)System Control Statements
These statements change the properties of the Oracle database instance. The only system control statement is ALTER SYSTEM. It lets users change settings, such as the minimum number of shared servers, kill a session, and perform other tasks.

7)Embedded SQL StatementsThese statements used in a procedural language program, such as those used with the Oracle precompilers. Examples include OPEN, CLOSE, FETCH, and EXECUTE.

Updating a table based on another table

Create table table_1(id number, code varchar2(20));
insert into table_1 values(1,'First Row');
insert into table_1 values(2, 'Rows to be updated');
Create table table_2(id number, code varchar2(20));
insert into table_2 values(2,'Second Row');

SQL> select * from table_1;
ID CODE---------- --------------------
1 First Row

SQL> select * from table_2;
ID CODE---------- --------------------
2 Second Row


Method 01:

SQL> update table_1 set code= (select t2.code from table_2 t2 JOIN table_1 t1 ON t1.id=t2.id) where table_1.id in(select id from table_2);
1 row updated.

SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row

Method 02:

SQL> update table_1 t1 set code= (select t2.code from table_2 t2 JOIN table_1 t1 ON t2.id=t1.id) where exists (select t2.code from table_2 t2 where t1.id=t2.id);
1 row updated.
SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row


Method 03:

adding an unique constraint in table_2.
SQL> alter table table_2 add constraint table_2_UK UNIQUE (id);
Table altered.

SQL> update (select t1.code col1, t2.code col2 from table_1 t1 JOIN table_2 t2 ON t1.id=t2.id) set col1=col2;1 row updated.

SQL> select * from table_1;
ID CODE}
---------- --------------------
1 First Row
2 Second Row

Instalacion Oracle 11g Solaris 64 Bits

At least 1 GB of RAM

# /usr/sbin/prtconf grep "Memory size"

The following table describes the relationship between installed RAM and the configured swap
space requirement:

RAM Swap Space
Between 1024 MB and 2048 MB 1.5 times the size of RAM
Between 2049 MB and 8192 MB Equal to the size of RAM
More than 8192 MB 0.75 times the size of RAM

To determine the size of the configured swap space, enter the following command:# /usr/sbin/swap -s

To determine whether the system architecture can run the software, enter the following command:# /bin/isainfo -kv

Between 225 and 275 MB of disk space in the /tmp directory

To determine the amount of disk space available in the /tmp directory, enter the following command:

# df -h /tmp

To determine the distribution and version of Solaris installed, enter the following command:

uname -r

The following packages (or later versions) must be installed:SUNWarc
SUNWbtool
SUNWhea
SUNWlibC
SUNWlibm
SUNWlibms
SUNWsprot
SUNWtoo
SUNWi1of
SUNWi1cs
SUNWi15cs
SUNWxwfnt
SUNWsprox

pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot \ SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

Validar si existe el grupo

grep dba /etc/group

root@afrodita # id -a oracleuid=100(oracle) gid=100(oinstall) groups=101(dba),102(oper)

root@afrodita # /usr/sbin/useradd -g oinstall -G dba oracle11gUX: /usr/sbin/useradd: ERROR: oracle11g is already in use. Choose another.
root@afrodita # id -a oracle11guid=101(oracle11g) gid=100(oinstall) groups=101(dba)root@afrodita # id -a oracleuid=100(oracle) gid=100(oinstall) groups=101(dba),102(oper)


# /usr/sbin/usermod -d /export/home/oracle11g -m oracle11g

Cambiar el Password Oracle11g

passwd -r files oracle11g


6.1 Configuring Kernel Parameters On Solaris 9

Parameter Recommended Value
noexec_user_stack
1
semsys:seminfo_semmni
100
semsys:seminfo_semmns
1024
semsys:seminfo_semmsl
256
semsys:seminfo_semvmx
32767
shmsys:shminfo_shmmax
4294967296
shmsys:shminfo_shmmni
100


# cp /etc/system /etc/system.orig

set noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=1024
set semsys:seminfo_semmsl=256
set semsys:seminfo_semvmx=32767
set shmsys:shminfo_shmmax=4294967296
set shmsys:shminfo_shmmni=100

Enter the following command to restart the system:

# /usr/sbin/reboot


# df -k

A single file system with at least 1.2 GB of free disk space

To create the required directories and specify the correct owner, group, and permissions for them:

Creacion de Directorios

mkdir -p /u02/app/oracle

chown -R oracle11g:oinstall /u02/app/oracle

chmod -R 775 /u02/app/oracle