ediate 8.05只能用dbms_sql 最好使用execute immediate 78、ORACLE9i中删除表空间中数据文件的方法 drop tablespace tbsname including contents
79、找出串中的数字 SELECT TRANSLATE(''''2KRW229'''', ''''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'''',''''0123456789'''') "Translate example" FROM DUAL / 2229 --全是数字的: select * from 你的表 where translate(你的列,''''0123456789'''','''' '''')=''''''''; select * from 你的表 where trim(ltrim(rtrim(replace(col_name,''''0123456789'''','''' '''')))) is null 80、分析表 analyze table mzbs.db_code ESTIMATE STATISTICS SAMPLE 20 PERCENT; 81、表空间管理和用户管理
--查看表空间和数据文件 select file_name,tablespace_name,autoextensible from dba_data_files; --数据表空间 CREATE TABLESPACE USER_DATA LOGGING DATAFILE ''''D:\ORACLE\ORADATA\ORCL\test.DBF'''' SIZE 50m REUSE , ''''c:\USERS01112.DBF'''' SIZE 50m REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL --临时表空间 CREATE TEMPORARY TABLESPACE USER_DATA_TEMP TEMPFILE ''''D:\TEMP0111.DBF'''' SIZE 50M REUSE AUTOEXTEND ON NEXT 1024K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K --增加数据文件
ALTER TABLESPACE USER_DATA ADD DATAFILE ''''c:\USERS01113.DBF'''' SIZE 50M; ALTER TABLESPACE USER_DATA ADD DATAFILE ''''c:\USERS01114.DBF'''' SIZE 50M AUTOEXTEND ON ; --删除表空间
DROP TABLESPACE USER_DATA INCLUDING CONTENTS; --修改数据文件大小 ALTER DATABASE DATAFILE ''''c:\USERS01113.DBF'''' RESIZE 40M;
--创建用户、赋予权限 CREATE USER USER_DATA PROFILE DEFAULT IDENTIFIED BY USER_DATA DEFAULT TABLESPACE USER_DATA TEMPORARY TABLESPACE USER_DATA ACCOUNT UNLOCK; GRANT CONNECT TO USER_DATA; GRANT RESOURCE TO USER_DATA; --把表移到另一个表空间 ALTER TABLE TABLENAME MOVE TABLESPACE TABLESPACENAME; --创建索引 CREATE INDEX INDEXNAME ON TABLENAME(COLUMNNAME); CREATE INDEX INDEXNAME ON TABLENAME(COLUMNNAME) TABLESPACE TABLESPACENAME; --重新建立索引 ALTER INDEX INDEXNAME REBUILD TABLESPACE TABLESPACE; --创建表
CREAE TABLE TABLENAME (COLUMN1 COLUTYPE DEFAULT(VALUE) NOT NULL) (COLUMN2 COLUTYPE DEFAULT(VALUE) NOT NULL); --建表的索引存储分配
CREATE TABLE summit.employee(id NUMBER(7) CONSTRAINT employee_id_pk PRIMARY KEY DEFERRABLE USING INDEX STORAGE(INITIAL 100K NEXT 100K) TABLESPACE indx, last_name VARCHAR2(25) CONSTRAINT employee_last_name_nn NOT NULL, dept_id NUMBER(7)) TABLESPACE data;
--建立主键
ALTER TABLE TABLENAME ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY(COLUMN1,COLUMN2) --使约束无效
ALTER TABLE TABLENAME ENABLE NOVALIDATE CONSTRAINT CONSTRANAME; ALTER TABLE TABLENAME ENABLE VALIDATE CONSTRAINT CONSTRANAME; --删除约束 ALTER TABLE TABLENAME DROP CONSTRAINT constraintname; DROP TABLE TABLENAEM CASCADE CONSTRAINTS;(删除表后将所用的外键删除) --给表增加列 ALTER TABLE TABLENAME ADD COLUMN COLUTYPE DEFAULT(VALUE) NOT NULL; --给列增加缺省值 ALTER TABLE TABLENAME MODIFY COLUMNNAME DEFAULT(VALUE) NOT NULL; --给表增加外键 ALTER TABLE TABLENAME ADD CONSTRAINT CONSTRAINTNAME FOREIGN KEY(COLUMN) REFERENCES TABLE1NAME(COLUMN1);
上一页 [1] [2] [3] [4] [5] 没有相关教程
|