SQL plus rescue data (test), sqlplus

SQL plus rescue data (test), sqlplus
Indexed
-- Execute plsql script
DECLARE
TYPE RowIDTab istable of rowid indexby BINARY_INTEGER;
 
CURSORc1 IS select/* + index (xifenfei ind_xifenfei) */rowid
Fromchf. xifenfei
Whereobject_id isNOT NULL;
 
R RowIDTab;
Rows NATURAL := 20000;
Bad_rows number: = 0;
Errors number;
Error_code number;
Myrowid rowid;
BEGIN
OPENc1;
LOOP
FETCH c1 bulk collect INTOr LIMIT rows;
EXITWHENr. count = 0;
BEGIN
FORALL I INr. FIRST... r. LASTSAVE EXCEPTIONS
Insertinto chf. xifenfei_new
Select/* + ROWID ()*/*
Fromchf. xifenfei A whererowid = r (I );
EXCEPTION
WhenOTHERS then
BEGIN
Errors: = SQL % BULK_EXCEPTIONS.COUNT;
FORerr1 IN1.. errors LOOP
Error_code: = SQL % BULK_EXCEPTIONS (err1). ERROR_CODE;
If error_code in (1410,810 3) then
Myrowid: = r (SQL % BULK_EXCEPTIONS (err1). ERROR_INDEX );
Bad_rows: = bad_rows + 1;
Insertinto chf. bad_rows values (myrowid, error_code );
Else
Raise;
Endif;
ENDLOOP;
END;
END;
Commit;
ENDLOOP;
Commit;
CLOSEc1;
Dbms_output.put_line ('total Bad Rows: '| bad_rows );
END;
/
No Index
-- Retrieve records
Setserveroutput on
Setconcat off
DECLARE
Nrows number;
Rid rowid;
Dobj number;
ROWSPERBLOCK number;
BEGIN
ROWSPERBLOCK: = 1000; -- estimate the maximum number of records in a block.
Nrows: = 0;
 
Selectdata_object_id intodobj
Fromdba_objects
Whereowner = 'chf'
Andobject_name = 't_ xifenfei'
-- And subobject_name = '<table partition> 'add this condition if table is partitioned
;
 
Fori in (selectrelative_fno, block_id, block_id + blocks-1 totblocks
Fromdba_extents
Whereowner = 'chf'
Andsegment_name = 't_ xifenfei'
-- And partition_name = '<table partition> 'add this condition if table is partitioned
-- And file_id! = <Offlined datafile> This condition is only used if a datafile needs to be skipped due to ORA-376 ()
Orderby extent_id)
Loop
Forbr ini. block_id.. I. totblocks loop
Forj in1.. ROWSPERBLOCK loop
Begin
Rid: = dbms_rowid.ROWID_CREATE (1, dobj, I. relative_fno, br, J-1 );
Insertinto CHF. T_XIFENFEI_NEW
Select/* + ROWID ()*/*
FromCHF. T_XIFENFEI
Whererowid = rid;
If SQL % rowcount = 1 thennrows: = nrows + 1; endif;
If (mod (nrows, 10000) = 0) thencommit; endif;
Exceptionwhenothers thennull;
End;
Endloop;
Endloop;
Endloop;
COMMIT;
Dbms_output.put_line ('total rows: '| to_char (nrows ));
END;
/
 
Two good blogs
Http://www.xifenfei.com/4160.html (the core idea of reading block content using rowid)
Http://www.hellodba.com/reader.php? ID = 216 & lang = CN (core idea, data block replacement and copy + query)

Q: How can I use SQL PLUS to connect to the ORACLE database server?

If the connection test is successful, the listener works properly.
(1) In GUI mode, specify the connection descriptor in Host string.
If you use the default installation, the connection descriptor is not specified, usually orcl.
(2) In the command line mode, the format of the connection command is conn username/password @ connect_identifier.
Username: User Name
Password: password
Connect_identifier: Connection Descriptor
If no connection descriptor is specified, connect to the database specified by the system environment variable ORACLE_SID.
If ORACLE_SID is not set, connect to the database.
If you have installed two or more databases on your machine, you must add @ connection descriptor to the command.

Contact us if you have any questions. Wish everything went well.

The error message ora-12154: tns: the specified connection identifier cannot be resolved when logging on to SQL plus

Pl/SQL when the service name in the oracle client changes, it will follow the latest tnsnames under the client installation directory. ora, to read the file as it; if the file is not modified in the oracle client after it is manually deleted or modified after it is uninstalled, the "ORA-12514: tns: failed to parse the specified connection identifier "error.

Solution:

1. Use tnsping <service_name> to check whether the database (database) alias can be correctly parsed when plSQL is logged on. If "TNS-03505: unable to resolve name" is reported, the database alias conflict, to change.

2. After you manually modify tnsnames. ora, modify the Host Name ("object" -- "add database to tree" -- "local..." in the oracle client ......", Delete the incorrect host name or remove it from the tree)

3. re-open plSQL and use the correct database alias to log on.

4. If the problem persists, do not log on to the plSQL interface, go to "tools" -- "Preferences" -- "Oracle" -- "Connection", and set "home" to the oracle home directory name) and then restart plSQL.

Appendix: After the above changes are made, the SQL plus provided by oracle may fail to log on to the remote server normally, because it accesses tnsnames under the database. ora file. The directory is different from the client. The default value is X (drive letter): \ oracle \ product \ 10.2.0 \ db_1 \ NETWORK \ ADMIN \ tnsnames. ora

Solution: simply copy the command line of the host string (service_name) defined in tnsnames. ora under the client to tnsnames. ora under the database, and then save it.