oracle 常用sql命令

进行数据库备份迁移之前，如果是oracle11g，一定要做导出空表设置(ArcSDE10.1版本中的SDE库比较奇怪，导不出所有的空表，必须先进行设置)。下列cmd命令或sql执行语句，请实施人员一定要看懂，如有必要，请进行相应的替换。


一、数据库备份步骤：
0、导出sde用户前准备：
1）在PL*SQL中sde用户登录后,执行：select 'alter table '||table_name||' allocate extent;' from user_tables
2) 复制查询结果，执行所有的上述查询结果（查询结果一般为多行sql语句）


1、导出sde用户文件(生成sde.dmp)
cmd命令:exp sde/sde@orcl file=c:\temp\sde.dmp
2、导出Jasframework用户(生成jasframework.dmp)
cmd命令：exp jasframework/passw@orcl file=c:\temp\jas.dmp


二、数据库还原步骤：（假如空间表存放用户为Jasframework,ArcSDE管理用户为sde）


1、drop掉Jasframework,sde用户
sql执行语句:
1）DROP USER jasframework CASCADE;
2）DROP USER sde CASCADE;


2、建Jasframework用户，选择Jasframework表空间，给dba，resource,connect权限;
sql执行语句:
1）CREATE USER szgasdcs IDENTIFIED BY szgasdcs DEFAULT TABLESPACE SZGASDCS TEMPORARY TABLESPACE TEMP;
2）GRANT DBA,RESOURCE,CONNECT TO szgasdcs;


3、建sde用户，选择sde表空间，给dba，resource,connect权限
sql执行语句:
1）CREATE USER sde IDENTIFIED BY sdejasframework DEFAULT TABLESPACE sde TEMPORARY TABLESPACE TEMP;
2）GRANT DBA,RESOURCE,CONNECT,select_catalog_role TO sde;
3) GRANT ALL ON DBMS_CRYPTO TO sde;


4、导入sde.dmp
cmd命令:
1)imp szgasdcs/szgasdcs@szgasdcs file=c:\temp\sr_20160705.dmp fromuser=szgasdcs touser=szgasdcs log=c:\temp\szgasdcs.log
2)在pl*sql中用sde用户登录后，编译未编译对象




5、导入Jasframework.dmp，在pl*sql用Jasframework用户登录后，编译未编译对象
cmd命令：
1）imp jasframework/passw@orcl file=c:\temp\jas.dmp fromuser=jasframework touser=jasframework log=c:\temp\jasframework.log;
2)在pl*sql中用Jasframework用户登录后，编译未编译对象


6、在catalog中测试某个空间表，如果能看到图形，恭喜，成功了




#给某用户表空间权限
grant unlimited tablespace to halfayadb




----------------------以下为常用技巧-----------------------------


---------Oralce 11g导出空表-------------


oracle 11g导出空表设置：
1、dba登入，执行下列SQL语句:
alter system set deferred_segment_creation=false;


2、用导出用户账号登入，执行
select 'alter table '||table_name||' allocate extent;' from user_tables
然后再执行所有的上述查询结果（查询结果一般为多行sql语句）。




----------Oracle常用SQL语句-------------------




#以dba身份连接
sqlplus sys/Pdgispdb01@gispdb01 as sysdba


#获取用户及对应的表空间
select username,default_tablespace from user_users


#删除jasframework用户
DROP USER jasframework CASCADE;


#删除jasframework表空间
DROP TABLESPACE jasframework INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINT;


#创建jasframework表空间，默认大小200M,每当容量满后自增100M
CREATE TABLESPACE jasframework DATAFILE 'J:\PipelineGIS\oradata\GISPDB01\Jasframework.dbf' SIZE 200m AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;


#创建用户jasframework,密码为pwdpdjasframework,默认表空间为spacejasframework
CREATE USER jasframework IDENTIFIED BY pwdpdjasframework DEFAULT TABLESPACE spacejasframework TEMPORARY TABLESPACE TEMP;


#给用户jasframework赋予DBA,RESOURCE,CONNECT角色
GRANT DBA,RESOURCE,CONNECT TO jasframework;


#修改SDE用户密码(在CMD中运行)
sdeservice -o modify -r SDE_DBA_PASSWORD -p xxxxx -v yyyyy -i esri_sde -d ORACLE(“xxxxx”为旧密码“yyyyy”为新密码)


#数据导出
exp username/passwork@sid file=c:\temp\temp.dmp


#数据还原
imp jasframework/pdjasframework@gispdb01 file=J:\jas.dmp fromuser=jasframework touser=jasframework log=J:\PipelineGIS\数据Dmp\jas.log


#Oracle11g导出空表设置
第一步，执行：alter system set deferred_segment_creation=false;
第二步，执行:select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 。将查询结果的每条SQL语句执行一遍


#查询密码有效期
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
#设置密码永不过期
ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;


#修改用户密码
alter user user01 identified by user10;


#获取当用户下的表或视图或字段
select * from user_tables
select * from user_views 
select * from user_tab_columns
select * from user_tab_comments
select * from user_col_comments


#获取当前用户下的表名及表注释
select t.TABLE_NAME,t1.COMMENTS from user_tables t,user_tab_comments t1 WHERE t.TABLE_NAME=t1.TABLE_NAME order by t.TABLE_NAME


#从一表选择数据插入另一张表
insert into tableA(colum1,colum2) select colum3,colum4 from tableB


#依据某个主键更新另一张表(单列)
update tableA set column1=(select column2 from tableB where tableA.id=tableB.id)


#依据某个主键更新另一张表(多列)
update tableA set(column1,column2)=(select column3,column4 from tableB where tableA.id=tableB.id)


#树状查询，查由id,parentid关联关系的表
Select level jishu, t1.* from PRI_FUNCTIONPRIVILEGE t1 
         connect by prior t1.eventid=t1.parenteventid 
         start with t1.parenteventid is null order siblings by t1.ordernumber


#expdb导出备份
1、在pl*sql中，先用sys账户登录，创建导出存放目录（以后导出的文件从这个路径中找）：create directory dpdir as 'c:\temp'
2、再为导出用户分配目录权限：grant read,write on directory dpdir to sde
3、运行cmd,执行：expdp sde/sde@gispdb01 schemas=sde dumpfile=sdedp20160621.dmp directory=dpdir
4、再从c:\temp文件夹中，拷出sdedp.dmp文件
expdp sde/sde@gispdb01  DIRECTORY=dpdir dumpfile=hositpal.dmp tables=ER_HOSPITAL
#impdb还原备份
1)导到指定用户下
impdp scott/tiger@orcl directory=dpdir dumpfile=expdp.dmp schemas=scott logfile=expdb.log
2)改变用户
impdp user1/tiger@orcl directory=dpdir dumpfile=expdp.dmp remap_schema=老用户:新用户;
3)改变表空间
impdb scott/tiger@orcl directory=dpdir dumpfile=expdb.dmp remap_tablespace=导出表空间名称：导入表空间名称
4)当某个用户使用了多个表空间时，先执行1），了解表空间名称后，再执行
impdp pgip/pgip@pgpdb01 directory=dpdir dumpfile=pgip.dmp schemas=pgip remap_tablespace=users:pgip TABLE_EXISTS_ACTION=skip logfile=imp_pgip.log