了解sqlplus中的copy命令

复制表数据是Oracle dba和应用开发人员的日常工作之一,常见的三种复制表数据的方式是:

  1. 以INSERT ..SELECT的方式将目标数据由源表插入到源表中,以此种方法复制表上的数据时可以灵活运行SELECT语句剔除那些无用的数据;同时也可以利用append nologging和并行parallel等特性加速数据复制速度.通过使用DBLINK数据库远程连接,INSERT…SELECT方式可以在数据库间复制表数据,当然要比在本库中复制表数据效率下降一些.
  2. 使用Oracle标准的导入导出工具exp/imp或者在10g以后使用DataPump数据泵技术,此种方法可以在数据库之间复制表数据,也可以用QUERY选项指定Where条件以筛选导出数据,10g中server端模式的数据泵极大地提高了导入导出的效率.
  3. 此外我们也可以使用SQLPLUS的copy命令在本库中或者在数据库间复制表上的数据.

使用copy命令复制表数据的这种方式用的较少,我们来着重介绍一下:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> conn maclean/maclean
Connected.

SQL> create table copy_sample as select * from dba_objects;
Table created.

SQL> copy
usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
 <db>   : database string, e.g., hr/your_password@d:chicago-mktg
 <opt>  : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
 <table>: name of the destination table
 <cols> : a comma-separated list of destination column aliases
 <sel>  : any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.

1.使用copy命令在本地同一数据库复制表上的数据

SQL>copy from maclean/maclean@clinica.rh2.oracle.com create copy_table using select * from copy_sample;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table COPY_TABLE created.

   51041 rows selected from maclean@clinica.rh2.oracle.com.
   51041 rows inserted into COPY_TABLE.
   51041 rows committed into COPY_TABLE at DEFAULT HOST connection.

/*  以上命令中from指定了源表所在的数据库连接字符,另可以用to关键字指定目标对象所在数据库连接字符串;
    using字句使用SELECT语句指定了所需复制的数据,因为是查询语句所以数据可以来源于多个表的复杂查询结果
*/

/*  需要注意的是from或to必须指定其一,
    否则出现:"SP2-0495: FROM and TO clauses both missing; specify at least one"的提示,
    若不指定from或者to,则默认其为SQLPLUS当前连接的数据库及模式
*/

/* 可以通过加大sqlplus的arraysize加速copy表数据  */

SQL>drop table copy_table;
Table dropped.

SQL>set arraysize 5000;

SQL>copy from maclean/maclean@clinica.rh2.oracle.com create copy_table using select * from copy_sample;

Array fetch/bind size is 5000. (arraysize is 5000)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table COPY_TABLE created.

   51041 rows selected from maclean@clinica.rh2.oracle.com.
   51041 rows inserted into COPY_TABLE.
   51041 rows committed into COPY_TABLE at DEFAULT HOST connection.

2.使用copy命令在数据库间复制表数据

SQL>copy from maclean/maclean@clinica.rh2.oracle.com to maclean/maclean@clinicb.rh3.oracle.com create
copy_table_from_another_db using select * from copy_sample;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table COPY_TABLE_FROM_ANOTHER_DB created.
   51041 rows selected from maclean@clinica.rh2.oracle.com.
   51041 rows inserted into COPY_TABLE_FROM_ANOTHER_DB.
   51041 rows committed into COPY_TABLE_FROM_ANOTHER_DB at maclean@clinicb.rh3.oracle.com.

SQL> conn maclean/maclean@clinicb.rh3.oracle.com
Connected.

SQL> select count(*) from copy_table_from_another_db;
  COUNT(*)
----------
     51041

3.使用不同的复制copy形式:

copy命令存在多种数据复制的模式,分别为:
1.append模式,将查询到数据插入到目标表中;若目标表不存在,则会创建目标表:
2.create模式,首先创建目标表,之后将数据插入到目标表中;若目标表已存在,copy命令将报错:
ERROR:
ORA-00955: name is already used by an existing object

3.insert模式,只负责将数据插入到目标表.若目标表不存在则copy命令直接报错;当使用insert模式时,需要保证using字句指定的列和目标表上的列一一对应

4.replace模式,负责替换目标表中的数据.若目标表不存在,使用replace模式时copy命令会创建目标表并插入数据;若目标表存在,则copy会将该表先drop掉再重建并插入数据

Script:Generate A DDL Script For A Table

以下脚本用于生成创建表的DDL语句,需要用到DBMS_METADATA.GET_DDL:

-- How to use ddl.sql
-- Run ddl.sql on the sql*plus.
-- Login the sql*plus with apps user or dba user
-- Start ddl.sql, which will ask you table_name and table_owner that you're looking for.
-- It will generate tablename_ddl.txt

set long 1000000
SET PAGESIZE 3000
set lines 200
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
set echo on
set timing off
set wrap On

ACCEPT TABLE_NAME CHAR PROMPT 'Enter Table Name : '
ACCEPT TABLE_OWNER CHAR PROMPT 'Enter Table Owner : '

select DBMS_METADATA.GET_DDL('TABLE',OBJECT_NAME,OWNER)
FROM Dba_objects
where owner = UPPER('&TABLE_OWNER') and object_name = UPPER('&TABLE_NAME')
and object_type = 'TABLE'
union all
select dbms_metadata.GET_DEPENDENT_DDL ('COMMENT', TABLE_NAME, OWNER )
FROM (select table_name,owner
from Dba_col_comments
where owner = UPPER('&TABLE_OWNER')
and table_name = UPPER('&TABLE_NAME')
and comments is not null
union
select table_name,owner
from sys.Dba_TAB_comments
where owner = UPPER('&TABLE_OWNER')
and table_name = UPPER('&TABLE_NAME')
and comments is not null)
union all
select DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',TABLE_NAME, TABLE_OWNER)
FROM (select table_name,table_owner
FROM Dba_indexes
where table_owner = UPPER('&TABLE_OWNER')
and table_name = UPPER('&TABLE_NAME')
and index_name not in (select constraint_name
from sys.Dba_constraints
where table_name = table_name
and constraint_type = 'P' )
and rownum = 1)
union all
select dbms_metadata.GET_DDL ('TRIGGER', trigger_name ,owner )
from Dba_triggers
where table_owner = UPPER('&TABLE_OWNER')
and table_name = UPPER('&TABLE_NAME')
.
SET CONCAT +
spool &TABLE_NAME+_ddl.txt
/
spool off

Sample output:

SQL> @ddl
SQL> set timing off
SQL> set wrap On
SQL>
SQL> ACCEPT TABLE_NAME CHAR PROMPT 'Enter Table Name : '
Enter Table Name : TAB$
SQL> ACCEPT TABLE_OWNER CHAR PROMPT 'Enter Table Owner : '
Enter Table Owner : SYS
SQL>
SQL> select DBMS_METADATA.GET_DDL('TABLE',OBJECT_NAME,OWNER)
2  FROM Dba_objects
3  where owner = UPPER('&TABLE_OWNER') and object_name = UPPER('&TABLE_NAME')
4  and object_type = 'TABLE'
5  union all
6  select dbms_metadata.GET_DEPENDENT_DDL ('COMMENT', TABLE_NAME, OWNER )
7  FROM (select table_name,owner
8  from Dba_col_comments
9  where owner = UPPER('&TABLE_OWNER')
10  and table_name = UPPER('&TABLE_NAME')
11  and comments is not null
12  union
13  select table_name,owner
14  from sys.Dba_TAB_comments
15  where owner = UPPER('&TABLE_OWNER')
16  and table_name = UPPER('&TABLE_NAME')
17  and comments is not null)
18  union all
19  select DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',TABLE_NAME, TABLE_OWNER)
20  FROM (select table_name,table_owner
21  FROM Dba_indexes
22  where table_owner = UPPER('&TABLE_OWNER')
23  and table_name = UPPER('&TABLE_NAME')
24  and index_name not in (select constraint_name
25  from sys.Dba_constraints
26  where table_name = table_name
27  and constraint_type = 'P' )
28  and rownum = 1)
29  union all
30  select dbms_metadata.GET_DDL ('TRIGGER', trigger_name ,owner )
31  from Dba_triggers
32  where table_owner = UPPER('&TABLE_OWNER')
33  and table_name = UPPER('&TABLE_NAME')
34  .
SQL> SET CONCAT +
SQL> spool &TABLE_NAME+_ddl.txt
SP2-0332: Cannot create spool file.
SQL> /
CREATE TABLE "SYS"."TAB$"
(    "OBJ#" NUMBER NOT NULL ENABLE,
"DATAOBJ#" NUMBER,
"TS#" NUMBER NOT NULL ENABLE,
"FILE#" NUMBER NOT NULL ENABLE,
"BLOCK#" NUMBER NOT NULL ENABLE,
"BOBJ#" NUMBER,
"TAB#" NUMBER,
"COLS" NUMBER NOT NULL ENABLE,
"CLUCOLS" NUMBER,
"PCTFREE$" NUMBER NOT NULL ENABLE,
"PCTUSED$" NUMBER NOT NULL ENABLE,
"INITRANS" NUMBER NOT NULL ENABLE,
"MAXTRANS" NUMBER NOT NULL ENABLE,
"FLAGS" NUMBER NOT NULL ENABLE,
"AUDIT$" VARCHAR2(38) NOT NULL ENABLE,
"ROWCNT" NUMBER,
"BLKCNT" NUMBER,
"EMPCNT" NUMBER,
"AVGSPC" NUMBER,
"CHNCNT" NUMBER,
"AVGRLN" NUMBER,
"AVGSPC_FLB" NUMBER,
"FLBCNT" NUMBER,
"ANALYZETIME" DATE,
"SAMPLESIZE" NUMBER,
"DEGREE" NUMBER,
"INSTANCES" NUMBER,
"INTCOLS" NUMBER NOT NULL ENABLE,
"KERNELCOLS" NUMBER NOT NULL ENABLE,
"PROPERTY" NUMBER NOT NULL ENABLE,
"TRIGFLAG" NUMBER,
"SPARE1" NUMBER,
"SPARE2" NUMBER,
"SPARE3" NUMBER,
"SPARE4" VARCHAR2(1000),
"SPARE5" VARCHAR2(1000),
"SPARE6" DATE
) CLUSTER "SYS"."C_OBJ#" ("OBJ#")
CREATE INDEX "SYS"."I_TAB1" ON "SYS"."TAB$" ("BOBJ#")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"

Know about Oracle High Water Mark

there’s no HWM for datafiles, it’s just a virtual term to describe the last block containing data in the data file, which is the minimum size allowed for sizing down the data file.

This article intends to provide an SQL script to find tables which are fragmented (i.e Data is much lower then High Water Mark),so that we can target those segments (tables) for recreation.

Software Requirements/Prerequisites

Execution Environment    :SQL, SQL*Plus

Access Privileges              :Requires dba privileges as script is to be run as   the owner SYS or SYSTEM

Prerequisites                     :Do an Analyze  with compute statistics on all tables present in the Users schema

i.e Analyze table <table_name> compute statistics

Usage                                :Sqlplus username/<password>

SQL> @fragment.sql

Advisory                            Will not work on Compressed tables, may return negative numbers.

 

Configuring the Script

1.User needs dba privileges to access dba_tables  .

2.Statistics needs to be collected on all the tables using  compute statistics

option for the input schema before fragment.sql is   run.

Running the Script

Step 1:- Copy this script to a file named fragment.sql.

Step 2:- Connect as user SYS or SYSTEM.

Step 3:- Run Analyze on all the tables present in the schema  for which you want to find the fragmented table.

SQL> Analyze table <table_name> compute statistics ;

Step 4:- Execute the fragment.sql script.Note the script will prompt for Schema name.

SQL> @fragment.sql

 

Caution

This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it. Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Script

REM This is an example SQL*Plus Script to find tables fragmentated below high water mark
set heading off verify off echo off
Spool fragment.sql
REM The below queries gives information about the size of the table with respect to the High water Mark
REM note that BLOCKS*8192 is BLOCKS times the block size: 8192.  Substitue your DB blocksize.
REM SELECT BLOCKS*8192/1024/1024 FROM  DBA_TABLES WHERE  TABLE_NAME='<TABLE_NAME>'  and    owner='<owner>'   ;
REM The below queries gives the actual size in MB used by the table in terms of data .
REM SELECT NUM_ROWS*AVG_ROW_LEN/1024/1024 FROM  DBA_TABLES WHERE TABLE_NAME='<TABLE_NAME>' and  owner='<owner'
REM
REM You can use the difference of the two sql statements specified above to get the table which
REM has fragementation below high water mark prompt Enter name(s) of schema for which you want to find
REM fragemented object.
PROMPT Please enter the schema name
SELECT TABLE_NAME ,  (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB"   FROM  DBA_TABLES WHERE  UPPER(owner) =UPPER('&OWNER') order by 2 desc;
Spool off

Goal

This article explains, with examples, how to view the high water mark and when the high water mark is reset. The queries given in this article applies when the segment , whose high water mark has to be determined ,  is in one datafile and is not spawned across multiple datafiles .

Solution

The high water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high water mark points to becomes a used block, and the high water mark is advanced to the next block. In other words, the segment space to the left of the high water mark is used, and the space to the right of it is unused.

The high-water mark is the level at which blocks have never been formatted to receive data.

When a table is created in a tablespace, some initial number of blocks / extents are allocated to the table. Later, as the number of rows inserted increases, extents are allocated accordingly.

To find out how many blocks / extents are allocated to the table, query DBA_SEGMENTS for ‘blocks’ and ‘extents’.

For example:

SQL>create table test1 (num number) tablespace tbsp1;

Table created

SQL>select blocks, extents from dba_segments where segment_name=’TEST1′

BLOCKS EXTENTS
——– ————-
8 1

Now, to view the high water mark, perform an:

SQL> analyze table test1 compute statistics;

Querying dba_tables for ‘Blocks’ and ‘Empty_blocks’ should give the high water mark.

Blocks — > Number blocks that has been formatted to recieve data
Empty_blocks —> Among the allocated blocks, the blocks that were never used

SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name=’TEST1′

BLOCKS EMPTY_BLOCKS NUM_ROWS
————– ————————– ——————–
0 7 0

If you insert some rows, then the output of the above query returns:

BLOCKS EMPTY_BLOCKS NUM_ROWS
————– ————————– ——————–
1 6 8

blocks+Empty_blocks=1+6=7 (but not 8) because 1 block is for segment header.

Insert some more rows into table ‘TEST1’ to increase the number of extents allocated so that
DBA_SEGMENTS will show :

BLOCKS EXTENTS
————– —————
32 4

And dba_tables (after analyze table) shows:

BLOCKS EMPTY_BLOCKS NUM_ROWS
————– ————————– ——————–
28 3 14338

Deleting the records doesn’t lower the high water mark. Therefore, deleting the records doesn’t raise the ‘Empty_blocks’. After deleting the records, if you query dba_segments or dba_tables, there would be no change. Even an ‘Alter table test1 deallocate unused;’ will not bring the high water mark down.

To determine the exact number of blocks that contain data i.e. space used by table below the high water mark, query rowid and get the first and last used block from the rowid.

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) “used blocks” from TEST1;

This works fine if only one file is used for the segment. If more files, we need to include the file number in some way, for instance:

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)||’-‘||dbms_rowid.rowid_relative_fno (rowid)) “used blocks” from TEST1

USED_BLOCKS
———————–
22

From this we can conclude that for table ‘TEST1’, 32 blocks are allocated out of which 28 blocks are formatted to receive data but only 22 blocks contain the actual data.

The high water mark can be reset with a truncate table or if the table is moved to another tablespace.  Additionally, in 10g the following option to shrink a segment was introduced to reset the high water mark. eg. ALTER TABLE <tablename> SHRINK SPACE;

When the table is created with CTAS from another table, the high water mark of the latter table is not reflected in the new table. If the tablespace is moved back to the same tablespace, the high water mark is reset. In this case, query on obj#, dataobj# of obj$. Obj# remains the same but dataobj# changes.

 

PURPOSE
This article describes how to find out how many blocks are really being
used within a table ie. are not empty. Please note that this article does
not cover what to do when chaining is taking place.

SCOPE & APPLICATION
For DBA’s needing to determine how many blocks within a table are
empty blocks.

How many blocks contain data (are not empty)
——————————————–
Each row in the table has pseudocolumn called ROWID.
This pseudo contains information about physical location
of the row in format
block_number.row.file

If the table is stored in a tablespace which has one
datafile, all we have to do is to get DISTINCT
number of block_number from ROWID column of this table.

But if the table is stored in a tablespace with more than one
datafile then you can have the same block_number but in
different datafiles so we have to get DISTINCT number of
block_number+file from ROWID.

The SELECT statements which give us the number of “really used”
blocks is below. They are different for ORACLE 7 and ORACLE 8
because of different structure of ROWID column in these versions.

For ORACLE 7:

SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||
SUBSTR(rowid,1,8)) “Used”
FROM schema.table;

For ORACLE 8+:

SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) “Used”
FROM schema.table;
or

SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) “Used”
FROM schema.table;

You could ask why the above information could not be determined
by using the ANALYZE TABLE command. The ANALYZE TABLE command only
identifies the number of ‘ever’ used blocks or the high water mark
for the table.

What is the High Water Mark?
—————————-
All Oracle segments have an upper boundary containing the data within
the segment. This upper boundary is called the “high water mark” or HWM.
The high water mark is an indicator that marks blocks that are allocated
to a segment, but are not used yet. This high water mark typically bumps
up at 5 data blocks at a time. It is reset to “zero” (position to the start
of the segment) when a TRUNCATE command is issued. So you can have empty
blocks below the high water mark, but that means that the block has been
used (and is probably empty caused by deletes). Oracle does not move the
HWM, nor does it *shrink* tables, as a result of deletes. This is also
true of Oracle8. Full table scans typically read up to the high water mark.

Data files do not have a high water mark; only segments do have them.

How to determine the high water mark
————————————
To view the high water mark of a particular table::

ANALYZE TABLEESTIMATE/COMPUTE STATISTICS;

This will update the table statistics. After generating the statistics,
to determine the high water mark:

SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name =;

BLOCKS represents the number of blocks ‘ever’ used by the segment.
EMPTY_BLOCKS represents only the number of blocks above the ‘HIGH WATER MARK’
.
Deleting records doesn’t lower the high water mark. Therefore, deleting
records doesn’t raise the EMPTY_BLOCKS figure.

Let us take the following example based on table BIG_EMP1 which
has 28672 rows (Oracle 8.0.6):

SQL> connect system/manager
Connected.

SQL> SELECT segment_name,segment_type,blocks
2> FROM dba_segments
3> WHERE segment_name=’BIG_EMP1′;
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
—————————– —————– ———- ——-
BIG_EMP1 TABLE 1024 2
1 row selected.

SQL> connect scott/tiger

SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name=’BIG_EMP1′;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
—————————— ———- ———- ————
BIG_EMP1 28672 700 323
1 row selected.

Note: BLOCKS + EMPTY_BLOCKS (700+323=1023) is one block less than
DBA_SEGMENTS.BLOCKS. This is because one block is reserved for the
segment header. DBA_SEGMENTS.BLOCKS holds the total number of blocks
allocated to the table. USER_TABLES.BLOCKS holds the total number of
blocks allocated for data.

SQL> SELECT COUNT (DISTINCT
2> DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
3> DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) “Used”
4> FROM big_emp1;
Used
———-
700
1 row selected.

SQL> DELETE from big_emp1;
28672 rows processed.

SQL> commit;
Statement processed.

SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name=’BIG_EMP1′;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
—————————— ———- ———- ————
BIG_EMP1 0 700 323
1 row selected.

SQL> SELECT COUNT (DISTINCT
2> DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
3> DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) “Used”
4> FROM big_emp1;
Used
———-
0
1 row selected.

SQL> TRUNCATE TABLE big_emp1;
Statement processed.

SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name=’BIG_EMP1′;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
—————————— ———- ———- ————
BIG_EMP1 0 0 511
1 row selected.

SQL> connect system/manager
Connected.

SQL> SELECT segment_name,segment_type,blocks
2> FROM dba_segments
3> WHERE segment_name=’BIG_EMP1′;
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
—————————– —————– ———- ——-
BIG_EMP1 TABLE 512 1
1 row selected.

Note: TRUNCATE has also deallocated the space from the deleted rows.
To retain the space from the deleted rows allocated to the table use:
TRUNCATE TABLE big_emp1 REUSE STORAGE

沪ICP备14014813号

沪公网安备 31010802001379号