oracle创建用户建立表空间分配表空间分配权限命令

1、创建用户并设置密码分配dba权限

DROP USER dtjc CASCADE
drop tablespace dtjcTablespace including contents and datafiles;
commit;
create  user dtjc identified by 123456;
create tablespace dtjcTablespace datafile 'E:\app\Administrator\oradata\orcl\dtjcTableSpace.dbf' size 1000m autoextend on next 100m maxsize unlimited;
alter user dtjc default tablespace dtjcTablespace;
grant dba to dtjc;

2、创建表空间指定表空间磁盘路径和表空间大小

 create tablespace projectmanagerTableSpace datafile   'D:\oracle_tablespaces\projectmanager.dbf' size 300m;

3、修改用户对应的表空间

  alter user projectmanager default tablespace projectmanagerTableSpace;

4、分配权限给用户

   grant create session,create table,unlimited tablespace to projectmanager;
     
     grant read,write on directory data_pump_dir1 to sde;--执行失败

     grant exp_full_database,imp_full_database to sde;

    grant  connect,resource to sde identified  by sde;

5、查看用户对应的表空间

  select username,default_tablespace from user_users;
      select * from dba_tablespaces

6、查看表空间使用情况

 select tablespace_name,file_id,file_name,round(bytes/(1024*1024*1024),0) total_space from  dba_data_files order by tablespace_name; 

    select sum(bytes)/(1024*1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name;

    select a.tablespace_name,
        a.bytes/(1024*1024*1024)  total,
        b.bytes/(1024*1024*1024) used,
        c.bytes/(1024*1024*1024) free,
        (b.bytes*100)/a.bytes "% USED",
        (c.bytes*100)/ a.bytes "% FREE"
    FROM sys.SM$ts_avail a ,
        sys.sm$ts_used b, 
        sys.sm$ts_free c
    where a.tablespace_name =b.tablespace_name
        and a.tablespace_name = c.tablespace_name

    select t.tablespace_name
    ,round(sum(bytes/(1024*1024)),0) ts_size
    from dba_tablespaces t
    ,dba_data_files d
    where t.tablespace_name = d.tablespace_name
    group by t.tablespace_name

    select sum(bytes)/(1024*1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name;

7、查看数据库用户

 select username from dba_users;

8、查看 用户,表空间，表空间对应文件

 select * from dba_data_files;
    
    select default_tablespace from dba_users where username='dtjc'

9、查看ASM磁盘空间使用情况

select * from v$asm_diskgroup

10、删除用户

 drop user hebei

11、分配dba权限给用户

grant dba to HEBEI

12、设置表空间自动增长

alter database datafile 'E:\SDE.DBF'  autoextend on next 5M maxsize 5G;
    
    alter tablespace jack_data add datafile '数据文件路径‘ size 1000m autoextend on next 100m maxsize 4000M

    alter tablespace HEBEI add datafile 'E:\ORACLE\APP\ADMINISTRATOR\ORADATA\ORCL\HEBEIADD.DBF'  size 1000m autoextend on next 100m maxsize 20G

    //设置表空间大小没有上限
    alter tablespace HEBEI add datafile 'E:\ORACLE\APP\ADMINISTRATOR\ORADATA\ORCL\HEBEIADD20190125.DBF' size 1000m autoextend on next 100m maxsize unlimited;

    create tablespace nmgsde DATAFILE 'E:\oracle\app\Administrator\oradata\orcl\nmgsde.dbf' size 5000m autoextend on next 100m maxsize 5G;

    create tablespace csdjsde DATAFILE '+DATA/prod/datafile/csdjsde.dbf'  size 3000m autoextend on next 100m maxsize 20G;

13、删除表空间

drop tablespace nmgdjsde including contents and datafiles cascade constraint;

14、查询数据库的所有表空间的数据文件

 select * from dba_directories;

15、导出数据文件

exp dtjc/123456@orcl file=h:\dtjc20190910.dmp
 expdp sde/sde@orcl directory=data_dump_dir1 dumpfile=sde01.dmp;
 导出指定的表
 exp userid=user_data/123456@orcl tables=(T_ZD_JZDZB) file=d:\jzdzb.dmp
 加导出条件
exp gototop/gototop file=1.dmp log=1.log tables=cyx.t query="where c1=20 and c2=gototop"

16、导入数据文件

 imp gtmis_gz/gtmis_gz@localhost/orcl file=D:\gz20160304.dmp log=D:\implogsz.log full=y ignore=y
// 不同用户导入  加入 fromuser    touser 参数
imp dtjc/123456@orcl fromuser=dtjc touser=dtjc file=g:\user.dmp 
导入一个或一组指定用户所属的全部表、索引和其他对象
imp system/manager file=seapark log=seapark fromuser=seapark 
imp system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)
将一个用户所属的数据导入另一个用户
imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy
imp system/manager file=tank log=tank fromuser=(seapark,amy)  touser=(seapark1, amy1)

17、不同版本oracle 导入与导出

impdp USERID=‘dtjc/123456@YZT2’
directory=DATA_PUMP_DIR dumpfile=T_NDJH_SS.dmp
REMAP_SCHEMA=tdly:dtjc logfile=T_NDJH_SS.log
version=10.2.0.1.0 tables=(T_NDJH_SS)
remap_tablespace= tdly_data:dtjc
EXPDP USERID='tdly/123456@orcl'  schemas=tdly directory=DATA_PUMP_DIR dumpfile=gygg.dmp logfile=gygg.log version=10.2.0.1.0 tables=(T_GYGG)
EXPDP USERID='dtjc002/123456@orcl' schemas=dtjc002 directory=DATA_PUMP_DIR dumpfile=dtjc002.dmp logfile=dtjc002.log version=10.2.0.1.0
注意：版本号与remap_tablespace

18、oracle 修改表字段 vchar2 到 clob

先备份一下列，重命名
alter table T_CRGY_KZ rename column BC_TK to BC_TK_bak;
新建clob列
alter table T_CRGY_KZ add BC_TK clob;
更新备份列
update T_CRGY_KZ set BC_TK=BC_TK_bak;
删除备份列
alter table T_CRGY_KZ drop column BC_TK_bak;
提交
commit;

19、查看表的列和数据类型，修改表数据类型，删除列

select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_cols where table_name='T_CRJ_SJZF';
alter table T_CRJ_SJZF modify xh NUMBER(10);
alter table T_CRJ_SJZF drop column zf_je;

20、字符串转换为时间 更新

update T_GDXM set tg_sj=TO_DATE(tg_sj_bak,'YY/MM/DD HH24:MI:SS')

21、复制表结构相同的数据

insert into YJ_YDJN select * from YJ_YDJN_bak；

22、表重命名

alter table test rename to test1;

23、表添加列

altertable Tablename add(column1 varchar2(20),column2 number(7,2)...) --Oracle中修改列名不可以，但是可以删除列，增加列

22、 查看用户下所有的表

select table_name from user_tables;

23、添加主键

alter table tablename add constraint pk_tablename primary key (column1,column2,...);

24、删除表中重复记录

a、从A库中去重后导入B库中
Insert into tdsc.T_CBGY (select  *  from (select  distinct * from tdly.T_CBGY t))
b、删除表中重复数据
DELETE from t_cbgy
WHERE (cbgy_guid) IN ( SELECT cbgy_guid FROM t_cbgy GROUP BY cbgy_guid HAVING COUNT(cbgy_guid) > 1) AND ROWID NOT IN (SELECT MIN(ROWID)  FROM t_cbgy GROUP BY cbgy_guid HAVING COUNT(*) > 1);
c、删除表中重复数据DELETE from T_GYGG
WHERE (gygg_guid) IN ( 
SELECT gygg_guid FROM 
T_GYGG GROUP BY gygg_guid 
HAVING COUNT(gygg_guid) > 1) 
AND ROWID NOT IN (SELECT MIN(ROWID) 
FROM T_GYGG GROUP BY gygg_guid 
HAVING COUNT(*) > 1);
d、以上删除执行效率在10万条以上效率低，执行数小时以上没有结果
用以下语句能提高执行效率

delete   from   t_gdxm   where  
 rowid   in   (select   rid  
  from   (select   rowid   rid,row_number()  
   over(partition   by   gd_guid  
    order   by rowid)  
     rn  from   t_gdxm) where   rn   <>   1   );

25、merge 表处理

a、执行差分处理merge into
     //merge into 语法例
            /*
             *  merge into 
             *      dtjc.t_gdxm new_gdxm 
                using tdly.t_gdxm temp_gdxm 
                    on (new_gdxm.gd_guid = temp_gdxm.gd_guid) 
                when matched then 
                    update set 
                        new_gdxm.zd_guid=temp_gdxm.zd_guid,
                        new_gdxm.xzq_dm=temp_gdxm.xzq_dm,
                        new_gdxm.td_yt=temp_gdxm.td_yt
                when not matched then 
                     insert values(
                        temp_gdxm.zd_guid,
                        temp_gdxm.xzq_dm,
                        temp_gdxm.td_yt
                    ) 
            */
            /* 修正为空的字段也更新的问题做了处理
             * 参照下例
             *  merge into 
                   student_new new_gdxm
                using student temp_gdxm
                   on (new_gdxm.name = temp_gdxm.name) 
                when matched then 
                   -- 当字段为空的时候不更新，获取数据时，有时好多字段为空，字段值获取不稳定
                   -- 字段为空的时候保持原来表中的值
                   update set adress=case 
                                when temp_gdxm.adress  is not null
                                then temp_gdxm.adress else new_gdxm.adress end,
                              adress1=case 
                                when temp_gdxm.adress1 is not null
                                then temp_gdxm.adress1 else new_gdxm.adress1 end 
                 when not matched  then
                    insert values(
                               temp_gdxm.name,
                               temp_gdxm.adress,
                               temp_gdxm.adress1)*/
        

26、日期大于某指定值查询

SELECT t.*, t.rowid FROM tb_guar_attachupload t WHERE f_createtime > to_date('2013/10/25 9:38:51', 'YYYY/MM/DD HH24:MI:SS');