Export issue : UDE-00014: invalid value for parameter, 'attach'

Running expdp with sys as sysdba user:
============================

[oracle@RAC1 ~]$   /opt/oracle/product/11.2.0.3/db_1/bin/expdp \'/ as sysdba \'/ directory=expdp_22Nov14 dumpfile=expdp_22Nov14_LISTING_content_%U logfile=expdp_22Nov14_LISTING_content.log tables='EME.BMLS_LISTING' content=METADATA_ONLY exclude=constraint,ref_constraint,index,statisstatistics parallel=4

Export: Release 11.2.0.3.0 - Production on Mon Nov 24 01:45:54 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
UDE-00014: invalid value for parameter, 'attach'.

Correct Syntax:
==========


[oracle@RAC1 ~]$  /opt/oracle/product/11.2.0.3/db_1/bin/expdp  \'/ as sysdba\' directory=EXPDP_21NOV14 dumpfile=expdp_24Nov14_LISTING_content_%U content=METADATA_ONLY
 logfile=expdp_24Nov14_LISTING_content.log tables='EME.BMLS_LISTING' content=METADATA_ONLY exclude=constraint,ref_constraint,index,statisstatistics parallel=4


Export: Release 11.2.0.3.0 - Production on Mon Nov 24 01:56:51 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=EXPDP_21NOV14 dumpfile=expdp_24Nov14_LISTING_content_%U content=METADATA_ONLY logfile=expdp_24Nov14_LISTING_content.log tables='EME.BMLS_LISTING'  content=METADATA_ONLY exclude=constraint,ref_constraint,index,statisstatistics parallel=4
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /orabackup/expdp_21Nov14/expdp_24Nov14_LISTING_content_01.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 01:57:45


Also you can try:

expdp \"SYS@service AS SYSDBA\"

ORA-17503: ksfdopn:2 Failed to open file +DATA/ractp/spfileractp.ora

[oracle@RAC1 ~]$ srvctl start instance -i ractp1 -d ractp
PRCR-1079 : Failed to start resource ora.ractp.db
CRS-5017: The resource action "ora.ractp.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/ractp/spfileractp.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/ractp/spfileractp.ora
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux-x86_64 Error: 13: Permission denied
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.3/grid_11203/log/rac1/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2674: Start of 'ora.ractp.db' on 'rac1' failed
CRS-2632: There are no more servers to try to place resource 'ora.ractp.db' on that would satisfy its placement policy

User: oracle

RAC 1  :  -rwsr-s--x. 1 oracle oinstall 222M Nov 21 01:41 oracle
RAC 2  :  -rwsr-s--x. 1 oracle oinstall 222M Nov 21 02:09 oracle

User Grid:

RAC  1 : -rwxrwxr-x. 1 grid oinstall 203972171 Nov 19 12:55 /u01/app/11.2.0.3/grid_11203/bin/oracle
RAC  2 : -rwsr-s--x. 1 grid oinstall 203972171 Nov 19 13:04 /u01/app/11.2.0.3/grid_11203/bin/oracle

Switch to root and change the permission on RAC1 to 6751 :

chmod 6751 /u01/app/11.2.0.3/grid_11203/bin/oracle

-rwsr-s--x. 1 grid oinstall 203972171 Nov 19 12:55 /u01/app/11.2.0.3/grid_11203/bin/oracle

Switch to oracle user:

[oracle@RAC1 ~]$ srvctl start instance -i ractp1 -d ractp

[oracle@RAC1 ~]$ ps -ef|grep pmon
grid      8860     1  0 22:05 ?        00:00:00 asm_pmon_+ASM1
oracle   10676     1  0 23:00 ?        00:00:00 ora_pmon_ractp1

[oracle@RAC1 ~]$ srvctl status database -d ractp
Instance ractp1 is running on node rac1
Instance ractp2 is running on node rac2

RAC listener Commands

[grid@RAC1 ~]$ ps -ef|grep LISTENER

grid      4008     1  0 00:36 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER -inherit
grid      4179     1  0 00:39 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER_SCAN1 -inherit


oracle Local listener:

lsnrcl status check run in grid home :  check the user who is running listerner from ps -ef output:

[grid@RAC1 ~]$ lsnrctl status LISTENER_SCAN1
[grid@RAC1 ~]$ lsnrctl status LISTENER

[grid@RAC1 admin]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac1
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac2
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac2


[grid@RAC1 admin]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac1
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac2
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac2

[oracle@RAC1 ~]$ show parameter listener
------------------------------------------
local_listener     string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.81)(PORT=1521))))
remote_listener     string RAC-SCAN.localdomain:1521

GRID_HOME:
cd $ORACLE_HOME/network/admin/
cd /u01/app/11.2.0.3/grid_11203/network/admin

cat $GRID_HOME/network/admin/listener.oracle
---------------------------------------------

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

Listener Log File         /u01/app/grid/diag/tnslsnr/RAC1/listener/alert/log.xml


cat $GRID_HOME/network/admin/endpoints_listener.ora
----------------------------------------------------
LISTENER_RAC1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=RAC1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.71)(PORT=1521)(IP=FIRST)))) # line added by Agent

Listener Log File: $GRID_HOME/log/diag/tnslsnr/RAC1/listener_scan1/alert/log.xml


[grid@RAC1 admin]$ srvctl config scan
SCAN name: RAC-SCAN.localdomain, Network: 1/192.168.56.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /RAC-SCAN.localdomain/192.168.56.91
SCAN VIP name: scan2, IP: /RAC-SCAN.localdomain/192.168.56.92
SCAN VIP name: scan3, IP: /RAC-SCAN.localdomain/192.168.56.93

[grid@RAC1 admin]$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

[grid@RAC1 admin]$ crsctl status resource -w 'TYPE = ora.scan_vip.type' -t
NAME           TARGET      STATE        SERVER                   STATE_DETAILS    
-------------------------------------------------------------------------------
ora.scan1.vip    1        ONLINE        ONLINE                    rac1                                      
ora.scan2.vip    1 `       ONLINE       ONLINE    rac2
ora.scan3.vip    1        ONLINE        ONLINE                    rac2


NODE RAC2:

[grid@RAC2 ~]$ ps -ef|grep LISTENER

[grid@RAC2 admin]$ crsctl status resource -w 'TYPE = ora.scan_vip.type' -t
NAME           TARGET      STATE        SERVER                   STATE_DETAILS    
-------------------------------------------------------------------------------
ora.scan1.vip    1        ONLINE        ONLINE                    rac1                                      
ora.scan2.vip    1 `       ONLINE       ONLINE    rac2
ora.scan3.vip    1        ONLINE        ONLINE                    rac2

[grid@RAC2 ~]$ ps -ef|grep LISTENER

grid      3948     1  0 00:35 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER_SCAN3 -inherit
grid      3950     1  0 00:35 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER_SCAN2 -inherit
grid      4016     1  0 00:35 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER -inherit

Compare Scan on both nodes:
----------------------------
[grid@RAC1 admin]$ cluvfy comp scan  

Issue #3: Service not registered with SCAN listener after SCAN listener failover:

After SCAN VIP and SCAN listener failover, instance does not register with the SCAN listener. It might happen for only 1 of the scan listener. Client connection
gets intermittent ORA-12514 TNS:listener does not currently know of service requested in connect descriptor.

Cause:
1. Unpublished Bug 12659561 after scan listener failover, database instance might not register to the scan listener (refer Note 12659561.8), fixed in 11.2.0.3.2,
merge patch 13354057 for 11.2.0.2 available for certain platform.
2. Unpublished Bug 13066936 Instance does not register services when scan fails over (refer Note 13066936.8)

Solution:

1) For both above bugs, the workaround is to unregister and register remote listener on the database instance which does not
register to a SCAN listener with following steps.
show parameter remote_listener
alter system set remote_listener='';
alter system register;
alter system set remote_listener='<scan>:<port>';
alter system register;
2) Other points to check if service is not registered with SCAN listener:
a. remote_listener and local_listener is defined correctly
b. EZCONNECT is defined in sqlnet.ora, eg: NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
c. SCAN name is defined in /etc/hosts or DNS and whether there is any mismatch if it is defined in both places
d. nslookup <scan> should display SCAN VIP in round-robin fashion
e. do not set SECURE_REGITER_<listener> in listener.ora if the class of secure transports (COST) is not configured.



11G R2: Oracle Cluster SCAN Listeners: Use easy connect naming method
=========================================================

Just recently I came across another unclear error message while troubleshooting an Oracle Instance registration with SCAN Listener. This post is one of the posts which may save quite a bit of time for people who will hit the same issue.

The errors message I got configuring a database just moved to run under 11GR2 Oracle Cluster control is as following:

1
2
3
4
5
6
7
8
9
10
SQL> alter system set remote_listener='scan.usolygrid-prod.cinram.com:1521';
alter system set remote_listener='scan.your.domain.com:1521'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name
'scan.your.domain.com:1521'

SQL>
I was quite sure that the syntax I used was 100% correct as other instances running on the same host used the same remote_listener parameter value.

It appears that someone adjusted the following string in sqlnet.ora file on the server:

1
2
3
4
from
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
to
NAMES.DIRECTORY_PATH= (TNSNAMES)
The change makes sense to me as it always good to have strict control on where tns names are sourced from on the servers side.

However it appears that Oracle use an easy connect naming method to register Oracle Instances in SCAN Listeners by default. Therefore EZCONNECT keyword should be present in sqlnet.ora file. Strictly speaking you can use other tns names as the resolution method for the remote_listener parameter but by default it uses the easy connect naming method and you may face the error message shown above if you omit the EZCONNECT keyword.

Top 5 Issues That Cause Troubles with Scan VIP and Listeners (Doc ID 1373350.1)
--------------------------------------------------------------------------------

How to Find Sessions Generating Lots of Redo or Archive logs

To find sessions generating lots of redo, you can use either of the following
methods. Both methods examine the amount of undo generated. When a transaction
generates undo, it will automatically generate redo as well.

The methods are:
1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates
   how much blocks have been changed by the session. High values indicate a
   session generating lots of redo.

   The query you can use is:
       SQL> SELECT s.sid, s.serial#, s.username, s.program,
           i.block_changes
           FROM v$session s, v$sess_io i
           WHERE s.sid = i.sid
           ORDER BY 5 desc, 1, 2, 3, 4;

   Run the query multiple times and examine the delta between each occurrence
   of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. This view contains information about the amount of
   undo blocks and undo records accessed by the transaction (as found in the
   USED_UBLK and USED_UREC columns).

  The query you can use is:
      SQL> SELECT s.sid, s.serial#, s.username, s.program,
          t.used_ublk, t.used_urec
          FROM v$session s, v$transaction t
          WHERE s.taddr = t.addr
          ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

   Run the query multiple times and examine the delta between each occurrence
   of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by
   the session.

You use the first query when you need to check for programs generating lots of
redo when these programs activate more than one transaction. The latter query
can be used to find out which particular transactions are generating redo.

How to check if your DB is open using spfile or pfile

If your DB is open using pfile:

-----------------------------------------

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 

   FROM sys.v_$parameter WHERE name = 'spfile';

--- PFILE

If your DB is open using SPFILE:

--------------------------------------------

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 

   FROM sys.v_$parameter WHERE name = 'spfile';

--SPFILE

sqlplus / as sysdba - ORA-01031 insufficient priv

Check the below:

1. check groups for oracle user. 
2.check password file is proper or not.
3. Try to see this line, SQLNET.AUTHENTICATION_SERVICES = (NTS), in sqlnet.ora file if exists. If yes, comment that and try to connect using / as sysdba.

Using Query level export

[oracle@vm1 ~]$ cat parameterFile.par

tables=MY_SCHEMA.DETAILED_REPORT,MY_SCHEMA.SUMMARY_REPORT
dumpfile=Sumit_expdp_Report.dmp
logfile=expdp_Sumit_Rpt.log
directory=EXPIMP
query=MY_SCHEMA.DETAILED_REPORT:"WHERE to_char(transfer_date,'MM-YY')='05-14'"
query=MY_SCHEMA.SUMMARY_REPORT:"WHERE to_char(transfer_date,'MM-YY')='05-14'"



Now run the export command:

expdp parfile=parameterFile.par ;

Requirement was to export the above mentioned tables with all data for MAY-2014 based on column transfer_date .

Gird and NFS : [FATAL] [INS-41321] Invalid Oracle Cluster Registry (OCR) location.

 Grid is very picky and somewhat uninformative about its NFS support

You need to trace the installer to find out what exactly it doesn’t like about your configuration.

Running the installer normally, the error message is:
[FATAL] [INS-41321] Invalid Oracle Cluster Registry (OCR) location.
CAUSE: The installer detects that the storage type of the location (/cmsstgdb/crs/ocr/ocr1) is not supported for Oracle Cluster Registry.
ACTION: Provide a supported storage location for the Oracle Cluster Registry.

OK, so Oracle says the storage is not supported, but I know that Netapp NFS is support just fine. This means I used the wrong parameters for the NFS mounts. But when I check my fstab and /etc/mount, everything looks A-OK. Can Oracle tell me what exactly bothers it?

It can. If you run the silent install by adding the following flags to the command line:
-J-DTRACING.ENABLED=true -J-DTRACING.LEVEL=2

Then you will see the following lines that explain why Oracle does not like your storage:

[main] [ 2011-01-04 23:43:55.184 GMT+00:00 ] [TaskSharedStorageAccess.reportStorageExceptions:754] Adding exception for node [node01]:

[main] [ 2011-01-04 23:43:55.184 GMT+00:00 ] [TaskSharedStorageAccess.reportStorageExceptions:755] Exception message: Mount options did not meet the requirements [Expected = “rw,hard,rsize>=32768,wsize& gt;=32768,proto=tcp |tcp,vers=3|nfsvers=3|nfsv3|v3,timeo>=600, acregmin=0&acregmax=0&acdirmin=0& amp;acdirmax=0|actimeo=0? ; Found = “rw,vers=3,rsize=32768,wsize=32768, acregmin=0,acregmax=0, acdirmin=0,acdirmax=0,hard,proto=tcp,timeo=300,retrans=2,sec=sys, addr=fas01b”]

This way it was much easier to see that I had timeo=300 while Oracle wanted timeo>=600.

 Your NFS configuration is not what you think it is.

If /etc/fstab says “timeo=600? and running “mount” shows that the volume is mounted with “timeo=600?, why does Oracle thinks that the volume is mounted with “timeo=300??

Turns out that the right place to look if you want to know what is your real NFS configuration is in “/proc/mounts”. The man page for “mount” says:

It is possible that files /etc/mtab and /proc/mounts don’t match. The first file is based only on the mount command options, but the content of the second file also depends on the kernel and others settings (e.g. remote NFS server. In particular case the mount command may reports unreliable information about a NFS mount point and the /proc/mounts file usually contains more reliable information.)

Aha! So /proc/mounts shows that timeo=300, which causes installation to fail, and the man page says that this could be caused by remote NFS server settings. Perfect. The problem was packaged and sent to the customer’s sysadmin, and was solved by the next morning.

Archive log Report On Hourly Basis

col day for a10
col thread# format 9999 heading "Thread"
break on thread# skip 2;
set lines 500
set pages 300
set trimspool on

select thread#, to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
count(*) Total
from v$log_history
where first_time > sysdate - &DaysMinus
group by thread#, to_char(first_time,'YYYY-MM-DD') order by 2 ;

Moving data (table and index) across tablespace

There are 2 methods we can use to do this.  One is to use a combination of "alter table X move tablespace Y" and "alter index X rebuild tablespace Y"  -- this works in Oracle8i release 8.1 and up ONLY.  Attached is a script called moveall.sql. 

Moveall.sql

set echo off

column order_col1 noprint

column order_col2 noprint

set heading off

set verify off

set feedback off

set echo off

spool tmp.sql

select decode( segment_type, 'TABLE',  segment_name, table_name ) order_col1,

             decode( segment_type, 'TABLE', 1, 2 ) order_col2,

      'alter ' || segment_type || ' ' || segment_name ||decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||  chr(10) || ' tablespace &1 ' || chr(10) || ' storage ( initial ' || initial_extent || ' next ' ||

        next_extent || chr(10) || ' minextents ' || min_extents || ' maxextents ' || max_extents || chr(10) ||' pctincrease ' || pct_increase || ' freelists ' || freelists || ');'

from user_segments,

       (select table_name, index_name from user_indexes )

where segment_type in ( 'TABLE', 'INDEX' )

   and segment_name = index_name (+)

order by 1, 2

/

spool off

set heading on

set verify on

set feedback on

set echo on

REM UNCOMMENT TO AUTO RUN the generated commands

REM ELSE edit tmp.sql, modify as needed and run it

REM @tmp

--- eof ----

It uses the user_segments table to generate all of the needed "alter table move" and "alter index rebuild" statements to move a table/index into another tablespace preserving the storage characteristics currently assigned to the object.  For example, when we run moveall.sql in the SCOTT schema, we might see:

scott@ORACLE> @moveall

scott@ORACLE> set echo off

alter INDEX PK_DEPT rebuild

tablespace users

storage ( initial 10240 next 10240

minextents 1 maxextents 121

pctincrease 50 freelists 1);

....

It begins by moving a table and then rebuilding each of the indexes on that table.  Since the indexes on the tables being moved will become unusable after the table, this script rebuilds them right after moving a table -- before moving the next table (to reduce downtime).

Running the moveall.sql script is harmless as it is written.  It generates the SQL you need to run and saves the sql into yet another script file "tmp.sql".  You should edit tmp.sql, review it, modify it if you want (eg: if you have a multi-cpu system, you could modify the index rebuilds to be "parallel N", "unrecoverable" and add other options to make them go faster on your system), and then run it.

Another method would be to use EXPort and IMPort.  You would

o EXP the user account.

o drop all of the objects this user owns.  You can 'select' the drop statements you need

(script attached) in much the same way we 'select' the alter table/index statements

o revoke UNLIMITED TABLESPACE from the user

o alter the users default tablespace to the target tablespace

o give the user an unlimited quota on this new tablespace and their temporary tablespace

o IMP this users data.

So, the process to move SCOTT's objects from their current tablespace to a NEW_TABLESPACE

would be:

1) do an export of all of scott's objects.  Make sure no one modifies them after you begin this process.  You will lose these changes if they do.

   $ exp userid=scott/tiger owner=scott

2) you would drop all of scotts tables.  This will get the indexes as well.  I don't suggest dropping the user SCOTT but rather dropping scott's objects.  Dropping scott would cause any system priveleges SCOTT has to disappear and the import would not restore them.  This script can be used to drop someones tables:

--------------------------------------

set heading off

set feedback off

set verify off

set echo off

spool tmp.sql

select 'drop table &1..' || table_name || ' cascade constraints;'

  from dba_tables

where owner = upper('&1')

/

spool off

@tmp.sql

--------------------------------------

3) You would modify the user to *not* have unlimited tablespace (else the IMP will just put the objects right back into the tablespace they came from) and then give them unlimited quota's on the new tablespace you want the objects to go into and on their temporary tablespace (for the sorts the index creates will do)

alter user SCOTT default tablespace NEW_TABLESPACE

/

revoke unlimited tablespace from SCOTT

/

alter user SCOTT quota unlimited on NEW_TABLESPACE

/

alter user SCOTT quota unlimited on SCOTTS_TEMPORARY_TABLESPACE

/

4) you will IMP the data back in for that user.  IMP will rewrite the create statements to use the users default tablespace when it discovers that it cannot create the objects in their original tablespace.  Please make sure to review the file imp.log after you do this for any and all errors after you import.

imp userid=scott/tiger full=y ignore=y log=imp.log

5) you can optionally restore 'unlimited tablespace' to this user (or not).  If you do not, this user can only create objects in this new tablespace and temp (which in itselfis not a bad thing)...

As with any operation of this magnitude -- please test these procedures on a small test

account (such as SCOTT) to become familar with them.

A couple of side notes:

o the alter table move/alter index rebuild is more flexible and faster the exp/imp (and less error prone -- you never actually drop the objects).  Additionally, it would be easy to modify the script to move TABLES to one tablespace and INDEXES to a different tablespace.  The drawback to using this method is the you cannot move a table with a LONG or LONG RAW.  You must exp that table and imp it into a table.  You can do this easily  by exporting the table with the LONG/LONG RAW, dropping that table -- creating an empty version of this table in the new tablespace and importing just that table.

o if you use the exp/imp, it is upto you to ensure that no modifications happen to the tables after you begin the export.  There are no mechanisms in place to ensure this -- you must do this (else you will lose changes)

How the above code works:

select decode( segment_type, 'TABLE',

                       segment_name, table_name ) order_col1,

       decode( segment_type, 'TABLE', 1, 2 ) order_col2,

     

If the segment_type is a table, return the SEGMENT_NAME (which is actually the table

name).  Else, if the segment type is not a table (it is an index) return the TABLE_NAME

that the index is on.  This'll group a table and all of its indexes together. 

The second column just makes sure that tables are done PRIOR to their indexes.