常用SQL语句

1.连接结果集:

select WMSYS.WM_CONCAT(column_name) from (select * from user_tab_cols where table_name='table_name' and HIDDEN_COLUMN='NO' order by segment_column_id)

2.查询表名:

select * from user_tables

3.查询字段名(不包含oracle隐藏字段):

select * from USER_TAB_COLUMNS

4.查询字段名(包含oracle隐藏字段):

select * from USER_TAB_COLS

5.创建表空间

CREATE TABLESPACE tablespace DATAFILE 
  'e:\oracle\product\10.2.0\oradata\sid\tablespace.ORA' SIZE 512M AUTOEXTEND ON
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

6.创建用户

CREATE USER username
  IDENTIFIED BY VALUES 'pwd'
  DEFAULT TABLESPACE tablespace
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 3 Roles for username 
  GRANT DBA TO username WITH ADMIN OPTION;
  GRANT CONNECT TO username WITH ADMIN OPTION;
  GRANT RESOURCE TO username WITH ADMIN OPTION;
  ALTER USER username DEFAULT ROLE ALL;
  -- 1 System Privilege for username 
  GRANT UNLIMITED TABLESPACE TO username WITH ADMIN OPTION;
  GRANT SELECT ANY TABLE TO username WITH ADMIN OPTION;

7.exp导出

exp system/system@sid file=D:\dmp.dmp owner=(username)

8.imp导入

Imp SYSTEM/system@sid file=dmp.DMP log=imp.log fromuser=(username,username1) touser=(username,username1)

9.一张表插入另外一张表 结构 不限

insert into tableA(fieldA,fieldB) select fieldC,fieldD from tableB where fieldE=?

10.查看Oracle版本,64位有明确字符

select * from v$version

引用

转载请注明baOZe