Oracle常用sql语句整理

最新推荐文章于 2020-06-02 11:10:11 发布

原创最新推荐文章于 2020-06-02 11:10:11 发布 · 612 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍Oracle数据库中表空间的创建与管理方法，包括临时表空间、用户表空间及大文件表空间的创建，同时讲解了如何创建用户、设置权限、建立表与索引、创建序列以及设置数据库链接等关键步骤。

1 创建临时表空间

CREATE TEMPORARY TABLESPACE user_tmp
TEMPFILE '/oradataa/user_tmp.dbf '
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

2 创建用户表空间

CREATE TABLESPACE xxuser
LOGGING
DATAFILE /oradataa/xxuser.dbf ' 
SIZE 32M 
AUTOEXTEND ON 
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

3 创建大表空间

create bigfile tablespace XXX
datafile '/oradataa/XXX.dbf' size 1G autoextend on next 1G;

4 删除表空间和文件

drop tablespace XXX including contents and datafiles;

5 创建用户并指定默认表空间

CREATE USER username IDENTIFIED BY userpassword  DEFAULT TABLESPACE TEST_DATA TEMPORARY TABLESPACE TEMP;

6 给用户授予表空间的权限(可以选择大小/无限制)

alter user username quota 10M/unlimited on XXX_tablespace;

7 修改用户密码

alter user newname identified by newpassword;

8 给用户授权

GRANT CREATE SEQUENCE,CREATE SESSION,CREATE ANY TABLE,CREATE ANY VIEW, CREATE ANY INDEX, 
CREATE ANY PROCEDURE, CREATE ANY JOB,ALTER ANY TABLE, ALTER ANY PROCEDURE,DROP ANY TABLE, 
DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,SELECT ANY TABLE, INSERT ANY TABLE, 
UPDATE ANY TABLE, EXP_FULL_DATABASE,IMP_FULL_DATABASE, DELETE ANY TABLETO USER;
GRANT DBA TO USER;

9 建表+主键

-- Create table
create table TB_MAILS_SERVER_MAP
(
  mailsserver VARCHAR2(64) not null,
  id          VARCHAR2(32),
  remark      VARCHAR2(64),
  status      INTEGER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TB_MAILS_SERVER_MAP
  add constraint TB_MAILS_SERVER_MAP_ID primary key (MAILSSERVER)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

10 索引

-- Create/Recreate indexes 
create index CREATETIME_STAT_SOURCE on TB_STAT_OBJECTID_SOURCE (CREATETIME)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

11 序列

-- Create sequence 
create sequence SEQ_ASSESSID
minvalue 1
maxvalue 999999999999999999999999999
start with 10000
increment by 1
cache 20;

12 DB Link

-- Create database link 
create database link XXXLINK connect to XXXUSER IDENTIFIED BY XXXPASSWD using '(description=(address=(protocol=TCP)(host=IP)(port=1521))(connect_data=(sid=ora11g)))';