Move Tables Between Tablespaces / Move Table from one Tablespace to another Tablespace

There are many methods available to move the tables between tablespaces.

1. Export /Import
export the existing table
remove quota from the old tablespace
change the default tablespace to the new tablespace
drop the old table
import the table
change the default tablespace back again
restore quota on the old tablespace

2. ALTER TABLE
MOVE TABLESPACE < target_tablespace>

This is possible Only if U are on 8i Provided the table does not have LOBs
You cannot MOVE an entire partitioned table (either heap or index organized). You must move individual partitions or subpartitions.
You cannot move a table with a LONG or LONG RAW. You must exp that table and imp it into a table
Recreate underlying indexes using ‘alter index rebuild’
Check the Invalid objects using this table and compile it to become valid.
The indexes on the tables being moved will become unusable after the table, rebuild them right after moving a table — before moving the next table (to reduce downtime).

- You can find out using the following dictionray views
DBA_TABLES.temporary, DBA_SEGMENTS.segment_type,V$TEMPFILE, DBA_TEMP_FILES

- select * from dba_segments where segment_type = ‘TEMPORARY’;

- What is temporary segments or temporary tables

– other sessions can see table definition but not data
– data truncated when transaction/session (commit/rollback/exit) ends
– sessions get bound to temporary tables with the first insert into the table
– segments allocated to table at insert time not create time like normal tables

– creating
– session specific:
CREATE GLOBAL TEMPORARY TABLE ON COMMIT PRESERVE ROWS;
– transaction specific:
CREATE GLOBAL TEMPORARY TABLE ON COMMIT DELETE ROWS;

– example:
CREATE GLOBAL TEMPORARY TABLE emp_temp
(emp_name VARCHAR(60) NOT NULL,
emp_id NUMBER(4) NOT NULL,
CONSTRAINT emp_temp_pk PRIMARY KEY (emp_id)) ON COMMIT PRESERVE ROWS;

INSERT INTO emp_temp (emp_name, emp_id) values(‘Mudhalvan’,1);

COMMIT;

SELECT sid,
serial#,
username,
machine
FROM v$session
WHERE sid IN
(SELECT sid
FROM v$lock
WHERE id1 IN
(SELECT object_id
FROM dba_objects
WHERE object_name =
AND owner = ))

Logminer can be utilised in a effective way to read the archive log files.It can be used as a very useful tool for almost any kind of debuggingactivity or just to see what exactly is going on in the redo stream.It is very easy to set it up. 
Set up the utl_file_dir.

SQL> alter system set utl_file_dir=’/u02/app/oracle/utl_out_dir’ scope=spfile

Build the dictionary file.
EXECUTE DBMS_LOGMNR_D.BUILD(‘dictionary.ldd’,'/u02/app/oracle/utl_out_dir’);
–Ensure that there is no extra ‘/’ in the directory name.
Add the log files to be mined.
begin;
–dbms_logmnr.new to add new file
–dbms_logmnr.removefile to remover a file
dbms_logmnr.add_logfile(‘/oracle/logs/1_25_594242111.dbf’,dbms_logmnr.addfile);
–
dbms_logmnr.add_logfile(‘/oracle/logs/1_25_594242111.dbf’,dbms_logmnr.removefile);
— dbms_logmnr.add_logfile(‘/oracle/logs/1_25_594242111.dbf’,dbms_logmnr.new);
dbms_logmnr.start_logmnr(dictfilename=>’/u02/app/oracle/oradata/logarch/dictionary.ldd’);
end;
Use the view V$LOGMNR_CONTENTS to view the contents of the logfile. You want to look at the column sql_redo in the V$LOGMNR_CONTENTS to do your research.
SELECT * FROM V$LOGMNR_CONTENTS;
End the Log miner session BEGIN DBMS_LOGMNR.END_LOGMNR; END;

Logminer can also be used to look for changes between a start and end scn.

SQL>  SELECT dbms_flashback.get_system_change_number
FROM dual; 
 GET_SYSTEM_CHANGE_NUMBER
 ————————
                  1117941

Do some drop / create tables.
Capture the SCN again 
SQL>  SELECT dbms_flashback.get_system_change_number
FROM dual; 
 GET_SYSTEM_CHANGE_NUMBER
 ————————
                  1117955

execute sys.dbms_logmnr.start_logmnr(1117941, 1117955);

SELECT * FROM v$Logmnr_contents;

Once done issue
execute sys.dbms_logmnr.end_logmnr;

This is very simple script and please modify it to take a backup of alert log on your own as it will delete the alert log once it is send email. Also Change the ORACLE_HOME and Database Name on your own environment.
—- Scrip Start
#!/bin/sh
export ORACLE_HOME=/p02/prod/proddb/9.2.0
cd $ORACLE_HOME/admin/PROD_abc23/bdump
if [ -f alert_PROD.log ]
then
grep ORA- alert_PROD.log > alert.err
fi
if [ `cat alert.err|wc -l` -gt 0 ]
then
mailx -s “ERRORS IN PROD INSTANCE ALERT LOG FILE” [Email address ] < alert.err cat alert_PROD.log >> alert_PROD.hist
rm alert_PROD.log — This will remove the Current alert log. Please make a backup before delete
touch alert_PROD.log
rm alert.err
fi
—– Script End

1. Shutdown the Database

SVRMGRL> shutdown immediate;

2. Change the init<>.ora parameter related to the instance

3. Startup with mount

SVRMGRL> startup mount;

SVRMGRL> alter database noarchivelog;

SVRMGRL> alter database open;

SVRMGR> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination ?/dbs/arch
Oldest online log sequence 61286
Current log sequence 61287

How can you check the hidden parameters of your database Check the following view
x$ksppi

You can access remote database sequences using following statement. Here is the Example

SQL> select seq_name.nextval@dblink from dual;

Example
SQL> select seq_address_id.nextval@test from dual;

SQL>update tbl_seq_address set new_key_value=seq_address_id.nextval@test

Oracle 9i has an excellent feature called External Table. Basically when you get CSV files from some other company to see the data from Oracle and access before 9i we have to load the data into Oracle Table using SQL Loader, which involves lot of procedure, space and steps.
But in 9i we don’t need anything you can just join your CSV or DAT file as table in your database and you can do all manipulation.

For Example you got the file from company named ext_table.csv Content of this file is
1,”Mudhalvan”,5000
2,”Aarthi”,3000
3,”Balu”,5000

You want to use this file as Table in Oracle database. Please follow this steps

1. UTL_FILE_DIR parameter should be set to “*’. Meaning oracle is allowed to read files from any folder of that machine.
You can check the same by “show parameter utl_file_dir” from SQL command

2. Make sure external directory was created and read, write access for that directory was granted to your user name.
Login as SYSDBA privileged user and create External Directory
Sql> connect / as sysdba;
Sql> create or replace directory ext_tables as ‘c:\mudhalvan’;
Sql> grant read,write on directory ext_tables to mudhalvan;

3 Move the file (ext_table.csv) to c:\mudhalvan folder.

4 Login into oracle run the following command.

CREATE TABLE MUDHALVAN
(EMPNO NUMBER(10),
ENAME VARCHAR2(25),
SALARY NUMBER(17,2)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tables
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
MISSING FIELD VALUES ARE NULL
)
LOCATION (‘ext_table.csv’)
)
REJECT LIMIT UNLIMITED;

6. Now you can see the new table created in your username called “MUDHALVAN”

7. sql> select * from mudhalvan;

1. V$LOCK
2. V$LOCKED_OBJECT
3. V$SESSION
4. V$ACCESS
5. V$TRANSACTION