Oracle Tablespace Transport

Oracle's transportable tablespace feature is can save a lot of time when copying a user's data. All objects will need to be in one tablespace, so it may not work for all environments. When a schema's objects are in one tablespace, this is a quick way to copy a schema and data.


Verify all objects are in one tablespace. Then SELECT to see any problems.
SQL> exec sys.dbms_tts.transport_set_check('TABLE_SPACE_NAME', true)

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected


Determine names of data files.
SQL> select file_name, file_id, tablespace_name from dba_data_files where tablespace_name='TABLE_SPACE_NAME'

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME
---------- ------------------------------
/u01/oradata/SID/ts01.dbf
6 TABLE_SPACE_NAME

/u01/oradata/SID/ts02.dbf
43 TABLE_SPACE_NAME


Set the tablespace to read only.
SQL> alter tablespace TABLE_SPACE_NAME read only;

Tablespace altered.


To export transportable, user must log on as sysdba: as sysdba
File expdat.dmp will be created. The file will be small because it will contain just metadata.
$ exp transport_tablespace=y tablespaces=TABLE_SPACE_NAME


Copy data files to new location, then and set ORACLE_SID.
$ cp ...
$ export ORACLE_SID=NEWSID


To import transportable, the user must log on as sysdba: as sysdba
$ imp file=expdat.dmp transport_tablespace=y "datafiles=(/u02/oradata/NEWSID/ts01.dbf,/u02/oradata/NEWSID/ts02.dbf)" tts_owner=SCHEMA_OWNER_NAME

Import: Release 9.2.0.5.0 - Production on Mon Jun 12 13:31:48 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Username: username as sysdba
Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SCHEMA_OWNER_NAME's objects into SCHEMA_OWNER_NAME
. . importing table "TABLE1"
. . importing table "TABLE2"
Import terminated successfully without warnings.


The imported tablespace is read only and the objects have been imported.
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'TABLE_SPACE_NAME';

TABLESPACE_NAME STATUS
------------------------------ ---------
TABLE_SPACE_NAME READ ONLY


If desired, set the tablespace to read write.
SQL> alter tablespace TABLE_SPACE_NAME read write;

Tablespace altered.

wall

Before there was instant messenger, there was wall and talk. The unix command wall sends a broadcast message to terminals. The message may be in a file, or the message may be typed interactively.

For interactive use type wall, then the message, then press ctrl-d.


Example on HP-UX:

testuser@host $ wall
Testing 1 2 3 ...
PRESS CTRL-d HERE

Broadcast Message from testuser (pts/0) Mon Jun 12 15:41:54...
Testing 1 2 3 ...

Quick Guide To Sudo

The very quick quide to sudo. This guide is for end users.

Sudo is configured by root to allow a user to run a command as root. If a user is granted all sudo privileges, then the user can do anything. Sudo eliminates password hassles, because the user will only need to know their own passoword.
To become root, a user would type:
$ sudo su - root
The user would be prompted for their own password.
A user can list their sudo privileges with:
$ sudo -l

Users will often be granted sudo for specific commands - such as starting and stopping a service or becoming another user. In this case, the user could (for example) start or stop a web server without needing the root password.

Application software may be installed as user appowner with group appgroup. The application files will typically be group/world read so there is limited ability to accidentally delete the files. There will typically be other users with group appgroup, so the users can do most tasks with their own account. There will be times , for example application patching, when a user will need to become the application owner. The application owner password could be shared. Alternatively, users could be granted sudo permission to become the application owner.

Sudo typically is configured to log sudo access. Sometimes this is used as a security/auditing feature. When configured to write to syslog, an entry will look like:
Jun 7 10:05:22 host001 sudo: usera : TTY=pts/1 ; PWD=/home/usera ; USER=root ; COMMAND=/usr/bin/su - oracle
Be careful when relying on these logs, as the security depends on the correct functioning of the commands that are run under sudo. For example, allowing "sudo vi /etc/hosts" will allow the user to spawn a shell as root and this will not be logged.

Alternatives to sudo are using setuid and setgid (which have significant gotchas). Solaris has Role Based Access Control, which provides more granular privileges.