Oracle常用sql整理

创建表空间
create tablespace tablespaceName datafile ‘/opt/oracle/oradata/ORCL/tablespaceName.dbf’ size 500m
autoextend on next 5M maxsize unlimited;
(创建表空间<tablespaceName为自定义名称>)—大小500M,每次5M,自动增大,最大不限制

删除表空间
drop tablespace tablespace_name including contents and datafiles;

lsof -n | grep deleted 用lsof -n | grep deleted找到刚才删除的dbf文件（后边会有一个deleted的标识），查看对应的pid，直接kill掉就可以了。
这是因为这些被删除的表空间数据文件还被数据库进程所持有，在进程关闭之前磁盘空间是不会释放的，如果想要验证该方法的可用性把数据库重启一次就可以了，数据库重启的效果等同于使用kill命令。

创建用户及初始密码
create user userName identified by password;
(创建一个用户为userName并设置密码为password)

给用户绑定默认表空间
alter user userName default tablespace tablespaceName;
(绑定表空间到用户)

给用户授权，connect,resource,dba 分别三种不同操作权限，根据实际情况可以自行变更。

grant dba to userName;
grant resource to userName;
grant connect to userName;

删除用户
drop user user_name cascade;
(cascade会级联删除该用户下的所有对象)

删除表
drop table tablename cascade constraints;
(cascade constraints会级联删除约束、索引，如果不加，有外键的表删除不掉)

删除视图
drop view tablename cascade constraints;
(cascade constraints会级联删除约束、索引，如果不加，有外键的表删除不掉)

查询数据库下所有表
select * from all_tables

查询当前登录用户所有约束
select * from user_constraints;

查询当前登录用户所有索引
select * from user_indexes;

查询当前登录用户所有表
select * from user_tables;

查询本用户的表注释
select * from user_tab_comments;

查询本用户的所有表字段
select * from user_tab_columns

查询本用户的表的列名和注释
select * from user_col_comments;

查询锁
如果函数被锁住，那么肯定是有进程在使用这个函数

Select b.SID,b.SERIAL# From dba_ddl_locks a, v$session b Where a.session_id = b.SID And a.name = ‘函数名或者表名’;

删除锁
在oracle使用命令杀死进程

alter system kill session ‘SID,SERIAL#’;

遇到在oracle中无法杀死的情况，则在OS层杀死该进程

select spid, osuser, s.program from v$sessions,v$process p where s.paddr=p.addr and s.sid=1715

kill -9 pid

修改字段长度
ALTER TABLE accounts MODIFY email VARCHAR2( 100 ) NOT NULL;

查看表结构
select * from user_tab_columns where table_name = ‘表名’

查看中文注释
SELECT TABLE_NAME,TABLE_TYPE,COMMENTS FROM USER_TAB_COMMENTS
WHERE
TABLE_NAME = ‘表名’;

导入dmp文件
最常用的 imp name/password@IP:1521/orcl[库] file=“c:\123.dmp” full=y ignore=y buffer=102400000

例:imp abc/123@192.168.1.3:1521/orcl file = “c:\123.dmp” full=y ignore = y

“buffer=102400000”是处理字符集问题的

后台执行

nohup imp ttt/ttt@192.168.198.123:1521/orcl file = /mnt/hgfs/dmpbak/ttt.dmp log =/home/data/oradata/orcl/expdat_ttt.log full = y STATISTICS=NONE &

imp命令的help
imp help=y

字符集修改
本案例以修改为ZHS16GBK字符集为例。
1.首先以sysdba的身份登录上去 conn /as sysdba

sqlplus / as sysdba

Oracle查看字符集

SELECT * FROM NLS_DATABASE_PARAMETERS p where p.PARAMETER='NLS_CHARACTERSET';
 
SELECT USERENV('language') FROM DUAL;

关闭数据库

SQL> shutdown immediate

以mount（挂载方式）打开数据库

SQL> startup mount

查看参数值，记录下来，字符集改完了再改回来

show parameter JOB_QUEUE_PROCESSES;
show parameter AQ_TM_PROCESSES;

设置session

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
 
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
 
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

启动数据库

SQL> alter database open;

修改字符集

SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;

可能会报错，提示我们的字符集：新字符集必须为旧字符集的超集，跳过超集检查做更改：

SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

这条语句就可以了，TERNAL_USE提供的帮助就会使oracle绕过了子集与超集的验证，这条语句和上面的语句内部操作时完全相同的。

查看修改后的字符集

SELECT USERENV('language') FROM DUAL;

参数改回默认值

alter system set job_queue_processes=132;
alter system set aq_tm_processes=1;

关闭数据库，重新启动

SQL>shutdown immediate
 
SQL> startup

建议执行（可能使用impdp/expdp无法导入）

原因：数据泵无法使用元数据API。通常，这是由于XSL样式表未正确设置造成的。

SQL> execute sys.dbms_metadata_util.load_stylesheets;