oracle常用命令2

1、用户：   创建用户：CREATE USER "NC61_MIGFS1224" PROFILE "DEFAULT" IDENTIFIED BY "1" DEFAULT TABLESPACE "NNC_DATA01" ACCOUNT UNLOCK;   授权连接：     GRANT "CONNECT" TO "NC61_MIGFS1224" WITH ADMIN OPTION;   授权DBA权限：    GRANT "DBA" TO "NC61_MIGFS1224" WITH ADMIN OPTION;   删除用户及数据：drop user user_name cascade; 2、全表导入导出命令：      导入： imp  NC61_MIGFS/1 file=D:\DATAMIG_DATABACK\njfs_old.dmp log=D:\DATAMIG_DATABACK\njfs_old.log  fromuser=NC touser=NC61_MIGFS buffer=2048000      导出：exp2  NC63_MIGJZK/1 file=D:\DATAMIG_DATABACK\JZK.dmp log=D:\DATAMIG_DATABACK\JZK.log buffer=2048000      导入命令2：1、sql>create or replace directory dump as 'c:\dump';             2、impdp username/password directory=dump dumpfile=aa.dmp logfile=log.txt full=y.... 3、某一字段按数量递增       declare        a number;        b number;          begin            select count(*) into a from testa;            while (a > 0) loop               b :=a+1;               update testa set t1=a where t=(select t from (select t,rownum n from testa) where n>=a and n<b);               a := a - 1;            continue;           end loop;          end; 4、查询文件剩余空间 SELECT     tablespace_name,COUNT(*)   AS extends,　round(SUM(bytes) / 1024 / 1024, 2) AS MB, sum(blocks)    AS blocks from dba_free_space group BY tablespace_name;