20170411oracle常用命令

--1新建用户
create user champion identified by century;
-- Create the user 
create user test identified by test default tablespace RISKCTRL;
-- Grant/Revoke role privileges 
grant resource to test;
grant connect to test;
grant dba to test;

--删除表
drop user champion cascade;
--分配权限
grant connect,resource,dba to champion;
--解锁用户
alter user sh account unlock; --解开LOCKED
alter user sh identified by sh;--解开EXPIRED

--切换用户
conn champion/century
--切换到管理员
conn /as sysdba
--创建表空间
create tablespace RISKCTRL datafile 'D:\app\RISKCTRL.dbf' size 1000M autoextend on next 100m maxsize 20480M extent management local segment space management auto;
--删除表空间
drop tablespace RISKCTRL including contents and datafiles  
--查看表空间
select distinct TABLESPACE_NAME from tabs;

--从另外一个用户复制表
create table emp  as select * from scott.emp;

--到入dmp文件
C:\Users\Administrator>imp champion/century@orcl file=E:\guanchen\database\20170413001.dmp full=y ignore=y commit=y

C:\Users\Administrator>imp champion/century@orcl file=E:\guanchen\database\20170422001.dmp log=E:\guanchen\database\20170422001.log full=y commit=y ignore=y

C:\Users\Administrator>imp champion/century@orcl file=bible_db.dmp log=dible_db.log full=y ignore=y ignore=y fromuser=a touser=b 

champion目标用户 要有dba权限

-- 查看ORACLE 数据库中所有表的注释 
select table_name,comments from user_tab_comments;

-- 查看ORACLE 数据库中所有列的注释 
select table_name,column_name,comments from user_col_comments; 
-- 查看所有函数
select object_name from user_objects where object_type='FUNCTION';
-- 查看所有存储过程
select object_name from user_objects where object_type='PROCEDURE';
--在SQL/PLUS工具中运行如下可以得到服务名

select global_name from global_name;