How To Recovery After Losing UNDO Tablespace

Problem Description:
===================
After normal shutdown Oracle9i DB, if i lose all UNDO files and dont have backup, could i
just create a new UNDO tablespace & files and drop the old to recover the DB? Since the
DB was shut down normally, it rolled back all uncommited transactions, so all datafiles
dont contain uncommitted data. Then I think undo is not needed during recovery. The only
thing i will not be able to recover is I cant get flashback, which i dont care, am I
right? But I was not allow to create the UNDO tbs without opening the DB, how can I do to
recover the DB? Any difference between version 8.1.7 and 9?
Thanks!

Ok, here we go (don't try this at home, well, maybe - do try this at home cause you
probably are not running a "real" system at home ;)

1. Shutdown db

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

[ora920@tkyte-pc-isdn ora920]$ ls
control01.ctl  cwmlite01.dbf  indx01.dbf  redo02.log  redo0B.log    temp01.dbf    
users01.dbf
control02.ctl  drsys01.dbf    odm01.dbf   redo03.log  redo0C.log    tools01.dbf   
xdb01.dbf
control03.ctl  example01.dbf  redo01.log  redo0A.log  system01.dbf  undotbs01.dbf

2. Undo file has been renamed:

[ora920@tkyte-pc-isdn ora920]$ mv undotbs01.dbf undotbs01.dbf.xxx

3. Connect DB with No logs:

[ora920@tkyte-pc-isdn ora920]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:33:29 2002

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

SQL> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'

idle> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

4. Change The Undo Management As Manual:

idle> alter system set undo_management = manual scope=spfile;

System altered.

5. Shutdown DB

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

6. Connect DB with No logs:

sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:35:34 2002

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

Connected to an idle instance.
idle> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'

Note: we still have to get rid of that thing..

7. Drop Datafile With  Offline Mode:

SQL> alter database datafile  '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'
  2  offline drop;

Database altered.

SQL> alter database open;

Database altered.

8. Now Drop OLD Undo:

SQL> drop tablespace undotbs1;

Tablespace dropped.

9. Create New Undo Tablespace:

SQL> create UNDO tablespace undotbs1
  2  datafile '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf' size 25m
  3  autoextend on next 1m maxsize 1024m;

Tablespace created

10. Make Undo Management As Auto:

SQL> alter system set undo_management = auto scope=spfile;

System altered.

11. Now, we've put the undo tablespace "back" by creating a new one.  Just bounce...

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
idle>

and we are right back where we started from

Link:

Link: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5669213349582

#################################################################### All The Best ##################################################################
 

Oracle Table Fregmentation

When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.


Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.


HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.



DDL statement always resets the HWM.



How to find table fragmentation?



SQL> select count(*) from big1;


1000000 rows selected.


SQL> delete from big1 where rownum <= 300000;


300000 rows deleted.


SQL> commit;


Commit complete.


SQL> update big1 set object_id = 0 where rownum <=350000;


342226 rows updated.


SQL> commit;


Commit complete.


SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');


PL/SQL procedure successfully completed.

Table size (with fragmentation)


SQL> select table_name,round((blocks*8),2)||'kb' "size"

 from user_tables where table_name = 'BIG1';


TABLE_NAME size

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

BIG1 72952kb

Actual data in table:



SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables where table_name = 'BIG1';



TABLE_NAME                     size

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

BIG1                           30604.2kb

Note = 72952 - 30604 = 42348 Kb is wasted space in table



The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.



How to reset HWM / remove fragemenation?



For that we need to reorganize the fragmented table.



We have four options to reorganize fragmented tables:



1. alter table ... move + rebuild indexes

2. export / truncate / import

3. create table as select ( CTAS)

4. dbms_redefinition



Option: 1 "alter table ... move + rebuild indexes"



SQL> alter table BIG1 move;


Table altered.


SQL> select status,index_name from user_indexes where table_name = 'BIG1';



STATUS INDEX_NAME

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

UNUSABLE BIGIDX



SQL> alter index bigidx rebuild;


Index altered.



SQL> select status,index_name from user_indexes where table_name = 'BIG1';



STATUS INDEX_NAME

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

VALID BIGIDX




SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');



PL/SQL procedure successfully completed.



SQL> select table_name,round((blocks*8),2)||'kb' "size"

from user_tables where table_name = 'BIG1';



TABLE_NAME size

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

BIG1 38224kb



SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables where table_name = 'BIG1';



TABLE_NAME size

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

BIG1 30727.37kb

Option: 2 “Create table as select”



SQL> create table big2 as select * from big1;



Table created.



SQL> drop table big1 purge;



Table dropped.



SQL> rename big2 to big1;



Table renamed.



SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');



PL/SQL procedure successfully completed.



SQL> select table_name,round((blocks*8),2)||'kb' "size" from user_tables where table_name = 'BIG1';



TABLE_NAME size

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

BIG1 85536kb



SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables where table_name = 'BIG1';



TABLE_NAME size

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

BIG1 68986.97kb



SQL> select status from user_indexes where table_name = 'BIG1';



no rows selected



SQL> --Note we need to create all indexes.



Option: 3 "export / truncate / import"



SQL> select table_name, round((blocks*8),2)||'kb' "size"

 from user_tables where table_name = 'BIG1';



TABLE_NAME size

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

BIG1 85536kb



SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables where table_name = 'BIG1';



TABLE_NAME size

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

BIG1 42535.54kb



SQL> select status from user_indexes where table_name = 'BIG1';



STATUS

--------

VALID



SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr

oduction

With the Partitioning, OLAP and Data Mining options



C:\>exp scott/tiger@Orcl file=c:\big1.dmp tables=big1



Export: Release 10.1.0.5.0 - Production on Sat Jul 28 16:30:44 2007



Copyright (c) 1982, 2005, Oracle. All rights reserved.



Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set



About to export specified tables via Conventional Path ...

. . exporting table BIG1 468904 rows exported

Export terminated successfully without warnings.



C:\>sqlplus scott/tiger@orcl



SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:12 2007



Copyright (c) 1982, 2005, Oracle. All rights reserved.



Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production

With the Partitioning, OLAP and Data Mining options



SQL> truncate table big1;



Table truncated.



SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr

oduction

With the Partitioning, OLAP and Data Mining options



C:\>imp scott/tiger@Orcl file=c:\big1.dmp ignore=y



Import: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:54 2007



Copyright (c) 1982, 2005, Oracle. All rights reserved.



Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options



Export file created by EXPORT:V10.01.00 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

. importing SCOTT's objects into SCOTT

. . importing table "BIG1" 468904 rows imported

Import terminated successfully without warnings.



C:\>sqlplus scott/tiger@orcl



SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:32:21 2007



Copyright (c) 1982, 2005, Oracle. All rights reserved.



Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production

With the Partitioning, OLAP and Data Mining options



SQL> select table_name, round((blocks*8),2)||'kb' "size" from user_tables where table_name = 'BIG1';



TABLE_NAME size

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

BIG1 85536kb



SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables where table_name = 'BIG1';



TABLE_NAME size

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

BIG1 42535.54kb



SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');



PL/SQL procedure successfully completed.



SQL> select table_name, round((blocks*8),2)||'kb' "size" from user_tables where table_name = 'BIG1';



TABLE_NAME size

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

BIG1 51840kb



SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables where table_name = 'BIG1';



TABLE_NAME size

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

BIG1 42542.27kb



SQL> select status from user_indexes where table_name = 'BIG1';



STATUS

--------

VALID



SQL> exec dbms_redefinition.can_redef_table('SCOTT','BIG1',-

> dbms_redefinition.cons_use_pk);



PL/SQL procedure successfully completed.



Option: 4 "dbms_redefinition"



SQL> create table TABLE1 ( no number, name varchar2(20) default 'NONE', ddate date default SYSDATE);



Table created.



SQL> alter table table1 add constraint pk_no primary key(no);



Table altered.



SQL> begin

 for x in 1..100000 loop

 insert into table1 ( no , name, ddate)

 values ( x , default, default);

 end loop;

 end;

 /



PL/SQL procedure successfully completed.



SQL> create or replace trigger tri_table1

 after insert on table1

 begin

 null;

 end;

 /



Trigger created.



SQL> select count(*) from table1;



COUNT(*)

----------

100000



SQL> delete table1 where rownum <= 50000;



50000 rows deleted.



SQL> commit;



Commit complete.



SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');



PL/SQL procedure successfully completed.



SQL> select table_name, round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'TABLE1';



TABLE_NAME size

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

TABLE1 2960kb



SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables where table_name = 'TABLE1';



TABLE_NAME size

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

TABLE1 822.69kb





SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg

SQL> --First check table is condidate for redefinition.

SQL>

SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT',-

> 'TABLE1',-

> sys.dbms_redefinition.cons_use_pk);



PL/SQL procedure successfully completed.



SQL> --After verifying that the table can be redefined online, you manually crea

te an empty interim table (in the same schema as the table to be redefined)

SQL>

SQL> create table TABLE2 as select * from table1 WHERE 1 = 2;



Table created.



SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT',-

> 'TABLE1',-

> 'TABLE2');



PL/SQL procedure successfully completed.



SQL> --This procedure keeps the interim table synchronized with the original tab

le.

SQL>

SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT',-

> 'TABLE1',-

> 'TABLE2');



PL/SQL procedure successfully completed.



SQL> --Create PRIMARY KEY on interim table(TABLE2)

SQL> alter table TABLE2

2 add constraint pk_no1 primary key (no);



Table altered.



SQL> create trigger tri_table2

 after insert on table2

 begin

 null;

 end;

 /



Trigger created.



SQL> --Disable foreign key on original table if exists before finish this proces

s.

SQL>

SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT',-

> 'TABLE1',-

> 'TABLE2');



PL/SQL procedure successfully completed.



SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');



PL/SQL procedure successfully completed.



SQL> select table_name, round((blocks*8),2)||'kb' "size"

2 from user_tables

3 where table_name = 'TABLE1';



TABLE_NAME size

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

TABLE1 1376kb



SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables where table_name = 'TABLE1';



TABLE_NAME size

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

TABLE1 841.4kb



SQL> select status,constraint_name from user_constraints

 where table_name = 'TABLE1';



STATUS CONSTRAINT_NAME

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

ENABLED PK_NO1



SQL> select status ,trigger_name from user_triggers

 where table_name = 'TABLE1';



STATUS TRIGGER_NAME

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

ENABLED TRI_TABLE2



SQL> drop table TABLE2 PURGE;



Table dropped.

Linux and Unix sftp command

Quick links
About sftp
Syntax
Examples
Related commands
Linux and Unix main page

About sftp

sftp is a program for transferring files securely.

Description

sftp is an interactive file transfer program, similar to ftp, which performs all operations over an encrypted ssh transport. It may also use many features of ssh, such as public key authentication and compression. sftp connects and logs into the specified host, then enters an interactive command mode.

The second usage format below will retrieve files automatically if a non-interactive authentication method is used; otherwise it will do so after successful interactive authentication.
The third format allows sftp to start in a remote directory.

The final usage format allows for automated sessions using the -b option. In such cases, it is necessary to configure non-interactive authentication to obviate the need to enter a password at connection time (see sshd(8) and ssh-keygen for details).

Since some usage formats use a colon (':') to delimit host names from path names, IPv6 addresses must be enclosed in square brackets ('[]') to avoid ambiguity. 

Syntax

sftp [-1246Cpqrv] [-B buffer_size] [-b batchfile] [-c cipher] [-D sftp_server_path] [-F ssh_config] [-i identity_file] [-l limit] [-o ssh_option] [-P port] [-R num_requests] [-S program] [-s subsystem | sftp_server] host

sftp [user@]host[:file ...]

sftp [user@]host[:dir[/]]

sftp -b batchfile [user@]host 

Options

-1 Specify the use of protocol version 1.

-2 Specify the use of protocol version 2.
-4 Forces sftp to use IPv4 addresses only.

-6 Forces sftp to use IPv6 addresses only.

-B buffer_size Specify the size of the buffer that sftp uses when transferring files. Larger buffers require fewer round trips at the cost of higher memory consumption. The default is 32768 bytes.

-b batchfile Batch mode reads a series of commands from an input batchfile instead of stdin. Since it lacks user interaction it should be used in conjunction with non-interactive authentication. A batchfile of ‘-’ may be used to indicate standard input. sftp will abort if any of the following commands fail: get, put, rename, ln, rm, mkdir, chdir, ls, lchdir, chmod, chown, chgrp, lpwd, df, symlink, and lmkdir. Termination on error can be suppressed on a command by command basis by prefixing the command with a ‘-’ character (for example, -rm /tmp/blah*).

-C Enables compression (via ssh's -C flag).

-c cipher Selects the cipher to use for encrypting the data transfers. This option is directly passed to ssh.

-D sftp_server_path Connect directly to a local sftp server (rather than via ssh). This option may be useful in debugging the client and server.

-F ssh_config Specifies an alternative per-user configuration file for ssh. This option is directly passed to ssh.

-i identity_file Selects the file from which the identity (private key) for public key authentication is read. This option is directly passed to ssh.

-l limit Limits the used bandwidth, specified in Kbit/s.

-o ssh_option Can be used to pass options to ssh in the format used in ssh_config(5). This is useful for specifying options for which there is no separate sftp command-line flag. For example, to specify an alternate port use: sftp -oPort=24. For full details of the options listed below, and their possible values, see ssh_config(5).

AddressFamily

BatchMode

BindAddress

ChallengeResponseAuthentication

CheckHostIP

Cipher

Ciphers

Compression

CompressionLevel

ConnectionAttempts

ConnectTimeout

ControlMaster

ControlPath

ControlPersist

GlobalKnownHostsFile

GSSAPIAuthentication

GSSAPIDelegateCredentials

HashKnownHosts

Host

HostbasedAuthentication

HostKeyAlgorithms

HostKeyAlias

HostName

IdentityFile

IdentitiesOnly

IPQoS

KbdInteractiveAuthentication

KbdInteractiveDevices

KexAlgorithms

LogLevel

MACs

NoHostAuthenticationForLocalhost

NumberOfPasswordPrompts

PasswordAuthentication

PKCS11Provider

Port

PreferredAuthentications

Protocol

ProxyCommand

PubkeyAuthentication

RekeyLimit

RhostsRSAAuthentication

RSAAuthentication

SendEnv

ServerAliveInterval

ServerAliveCountMax

StrictHostKeyChecking

TCPKeepAlive

UsePrivilegedPort

User

UserKnownHostsFile

VerifyHostKeyDNS 

-P port Specifies the port to connect to on the remote host.

-p Preserves modification times, access times, and modes from the original files transferred.

-q Quiet mode: disables the progress meter as well as warning and diagnostic messages from ssh.

-R num_requests Specify how many requests may be outstanding at any one time. Increasing this may slightly improve file transfer speed but will increase memory usage. The default is 64 outstanding requests.

-r Recursively copy entire directories when uploading and downloading. Note that sftp does not follow symbolic links encountered in the tree traversal.

-S program Name of the program to use for the encrypted connection. The program must understand ssh options.

-s subsystem | sftp_server Specifies the SSH2 subsystem or the path for an sftp server on the remote host. A path is useful for using sftp over protocol version 1, or when the remote sshd(8) does not have an sftp subsystem configured.

-v Raise logging level. This option is also passed to ssh.

Interactive Commands

Once in interactive mode, sftp understands a set of commands similar to those of ftp. Commands are case insensitive. Pathnames that contain spaces must be enclosed in quotes. Any special characters contained within pathnames that are recognized by glob must be escaped with backslashes (‘\’). 
bye Quit sftp.

cd path Change remote directory to path.
chgrp grp path Change group of file path to grp. path may contain glob(3) characters and may match multiple files. grp must be a numeric GID.
chmod mode path Change permissions of file path to mode. path may contain glob(3) characters and may match multiple files.
chown own path Change owner of file path to own. path may contain glob(3) characters and may match multiple files. own must be a numeric UID.
df [-hi] [path] Display usage information for the filesystem holding the current directory (or path if specified). If the -h flag is specified, the capacity information will be displayed using "human-readable" suffixes. The -i flag requests display of inode information in addition to capacity information. This command is only supported on servers that implement the “statvfs@openssh.com” extension.
exit Quit sftp.
get [-Ppr] remote-path [local-path] Retrieve the remote-path and store it on the local machine. If the local path name is not specified, it is given the same name it has on the remote machine. remote-path may contain glob(3) characters and may match multiple files. If it does and local-path is specified, then local-path must specify a directory.

If either the -P or -p flag is specified, then full file permissions and access times are copied too.

If the -r flag is specified then directories will be copied recursively. Note that sftp does not follow symbolic links when performing recursive transfers.
help Display help text.
lcd path Change local directory to path.
lls [ls-options [path]] Display local directory listing of either path or current directory if path is not specified. ls-options may contain any flags supported by the local system's ls command. path may contain glob(3) characters and may match multiple files.
lmkdir path Create local directory specified by path.
ln [-s] oldpath newpath Create a link from oldpath to newpath. If the -s flag is specified the created link is a symbolic link, otherwise it is a hard link.

lpwd Print local working directory.
ls [-1afhlnrSt] [path] Display a remote directory listing of either path or the current directory if path is not specified. path may contain glob(3) characters and may match multiple files.

The following flags are recognized and alter the behaviour of ls accordingly:

-1: Produce single columnar output.

-a: List files beginning with a dot (‘.’).

-f: Do not sort the listing. The default sort order is lexicographical.

-h: When used with a long format option, use unit suffixes: Byte, Kilobyte, Megabyte, Gigabyte, Terabyte, Petabyte, and Exabyte in order to reduce the number of digits to four or fewer using powers of 2 for sizes (K=1024, M=1048576, etc.).

-l Display additional details including permissions and ownership information.

-n Produce a long listing with user and group information presented numerically.

-r Reverse the sort order of the listing.

-S Sort the listing by file size.

-t Sort the listing by last modification time. 
lumask umask Set local umask to umask.

imkdir path Create remote directory specified by path.

progress Toggle display of progress meter.

put [-Ppr] local-path [remote-path] Upload local-path and store it on the remote machine. If the remote path name is not specified, it is given the same name it has on the local machine. local-path may contain glob characters and may match multiple files. If it does and remote-path is specified, then remote-path must specify a directory.

If either the -P or -p flag is specified, then full file permissions and access times are copied too.

If the -r flag is specified then directories will be copied recursively. Note that sftp does not follow symbolic links when performing recursive transfers. 

pwd Display remote working directory.

quit Quit sftp.

rename oldpath newpath Rename remote file from oldpath to newpath.

rm path Delete remote file specified by path.

rmdir path Remove remote directory specified by path.

symlink oldpath newpath Create a symbolic link from oldpath to newpath.
version Display the sftp protocol version.

!command Execute command in local shell.

! Escape to local shell.
? Synonym for help.

Examples

sftp exampleftp.computerhope.com

Typing the above command would connect to a secure connection for transferring files. If the host you're using supports a secure login you would then be connected to the host. Below is an example of what would be seen:

Connecting to exampleftp.computerhope.com...

exampleftp.computerhope.com FTP server ready.

Name: yourusername
Password: 

If your user name and password are valid and entered correctly, you will be successfully logged in:
Remote system type is UNIX.
Using ASCII mode to transfer files.
sftp>
Once at the sftp> prompt, you will be in the default directory for the user you logged in as. The first thing you'd probably want to do is see what directory that is. To see the present working directory, use the pwd command just like in Linux:

sftp> pwd
257 "/ftpdefaultdir" is current directory.
The number 257 is a numerical code. All FTP messages have a code number associated with them, and for technical reasons they are included with the messages from the server. The server lets you know you're in the /ftpdefaultdir directory. Let's see what files are in there, using the ls command:
sftp> ls
This will produce a file listing, just like in Linux. You can change remote directories with cd. If you want to change what directory you're using on your local computer, you can use lcd for "local change directory." Let's say you want to get a file from the server named awesome.jpg, and download it to your local directory /home/myuser/images: 
sftp> lcd /home/myuser/images
Local directory now /home/myuser/images
sftp> get awesome.jpg
local: awesome.jpg remote: awesome.jpg
200 PORT command successful.
150 Opening ASCII mode data connection for awesome.jpg (352271 bytes).  
Oops! That's not quite right. JPEG images are binary files, not ASCII (text) files.

FTP supports two different types of file transfers, ASCII and binary. At login, the server told us it was currently in ASCII mode. Let's change that to binary:
sftp> bin
Using binary mode to transfer files.
bin is short for binary, and either command will switch to binary mode. We can now do the same file transfer and the file will come through correctly.

Now let's switch to the remote directory all-images and download every JPEG file using a wildcard.
sftp> cd all-images
250-README for all-images
250-
250-This folder contains all the JPEG images for our project. 
250-
250 CWD command successful.
This directory had a "README" message which is displayed by the FTP server every time you change it to your current directory. The server then let you know that your cd command was successful. We can now use the mget command to get multiple files:
sftp> mget *.jpg *.jpeg *.JPG *.JPEG
We will now get all the jpeg files with the extensions JPG, JPEG, jpg, or jpeg.

If we have any files to upload to the server, we can use the commands put or mput to upload them. When we're done, we can logout using the exit command.