oracle数据库常用命令

create tablespace GRP
datafile ‘E:/GRP_TBSPACE.dbf‘
size 3000M
autoextend on next 5M maxsize 5000M;

/*第3步：创建用户并指定表空间 */
create user tctest0707 identified by a default tablespace GRP temporary tablespace temp profile default;

/*第4步：给用户授予权限 */
grant connect,resource,dba,select any table to tctest0707;
GRANT select ANY TABLE TO nncz2017 WITH ADMIN OPTION;（常态库执行）
--开始->运行->输入cmd->imp tctest0707/a@127.0.0.1:1521/orcl full=y file=

-----11g导入到10g
pm_db01 / szxmk16
--impdp nsxmk16/a@orcl dumpfile=NSXMK16062001.dmp logfile=NSXMK16062001.log remap_schema=nsxmk16:nsxmk16 transform=oid:n version=10.2.0.1.0
remap_tablespace=qjhxtbs:szxmktbs
--导入文件放在F:\oracle\product\10.2.0\admin\orcl\dpdump

--(导深圳11G库 创建表空间）
create tablespace SZXMKTBS
datafile ‘D:\app\yanchao\oradata\orcl\SZXMKTBS.dbf‘ size 1000m autoextend on next 10m ;
create tablespace GRP
datafile ‘D:\app\yanchao\oradata\orcl\GRP.dbf‘ size 1000m autoextend on next 10m ;
create tablespace SHENZHENDB
datafile ‘D:\app\yanchao\oradata\orcl\SHENZHENDB.dbf‘ size 1000m autoextend on next 10m ;

--导出数据库
exp nncz2017/nncz2017@10.56.96.82/nncz owner=nncz2017 file=F:\nncz2017.dmp;

--修改数据库大小
alter database datafile ‘E:\GRP_TBSPACE.DBF‘ resize 10240m

----闪回数据

select sysdate 时间, timestamp_to_scn(to_date(‘2017-09-01 17:20:31‘,‘yyyy-MM-dd hh24:mi:ss‘)) SCN from dual

alter table ydz_exi_budget_trans enable row movement

flashback table ydz_exi_budget_trans to scn 29127907

----查询闪回数据：
select * from sal_person as of timestamp to_date(‘2012-04-27 15:00:00‘,‘YYYY-MM-DD HH24:MI:SS‘)

--查询重复数据
select * from gfm_ps_uv_view a where rowid !=(select max(rowid)

　　from gfm_ps_uv_view b where a.template_id=b.template_id and a.menu_id=b.menu_id and a.class_name = b.class_name and a.assembly_name = b.assembly_name ) order by view_id;

--添加列字段、列注释
alter table gfm_ps_pfs_interface add c1 number ;
comment on column gfm_ps_pfs_interface.c1 is ‘测试‘;
alter table gfm_ps_pfs_interface drop column c1;--不会删除列注释

--查询某个字段在哪个表的哪个列
select table_name,column_name from user_tab_columns where column_name like ‘%ACTION_CODE%‘;

--创建job自动执行存储过程（命令模式）
declare
Job_InitJk pls_integer;
begin
sys.dbms_job.submit(job => Job_InitJk,
what => ‘Pro_InitInterfaceTable;‘,
next_date => sysdate,
interval => ‘TRUNC(SYSDATE+1)+6/24‘);
commit;
end;

--

/**
* Object数组转换为List（推荐使用）
*
* @param arr 数组
* @return List
*/
public static List array2List2(Object[] arr) {
List list = new ArrayList();
if (arr == null) return list;
list = Arrays.asList(arr);
return list;
}

--无法删除当前连接的用户
drop user WZCZ2016 cascade

DROP TABLESPACE GRP INCLUDING CONTENTS AND DATAFILES;
drop tablespace GRP including contents and datafiles cascade constraints;

select owner,table_name,tablespace_name from dba_tables
where tablespace_name=‘GRP‘
and table_name in (select mview_name from dba_mviews);

--oracle中用户删除不了，ORA-01940提示 “无法删除当前已连接用户”
select username,sid,serial# from v$session where username=‘WZCZ2016‘

alter system kill session ‘137,41‘;

alter system kill session‘201,14034‘;

--之后可以删除用户

drop user WZCZ2016 cascade;

--查看表空间情况

SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name

--去空字符
update lyz_ls set tt=rtrim(tt,chr(0))
--截取某个字符最后出现的位置之后的字符串
substr(a.parent_sr,instr(a.parent_sr,‘.‘,-1)+1)

--查询某个表被那个视图引用了
select * from dba_dependencies A where REFERENCED_NAME=‘GFM_PS_PFS‘ and TYPE=‘VIEW‘

oracle数据库常用命令

标签：bst   oid   oracl   return   add   ant   files   interface   flashback