看表的大小
创建表空间(本地)
Create tablespace xxx datafile ‘/………../test.dbf’ size 10M;
创建用户
Create user sss identified by “*******” default tablespace xxx quota 2M on xxx password expire;
给用户权限
Grant connect,resource to sss;
受权限
revoke connect from zhuanglu
改密码
alter user zl identified by "123456";
改空间配额
alter user zl quota 1M on xxx;
锁定于解锁
alter user zhuanglu account lock;
alter user zhuanglu account unlock ;
删用户
drop user zhuanglu cascade;
看正在运行的用户的PID
select spid from v$process where addr=(select paddr from v$session where username=upper('test'));
!kill -9
切换用户
Conn sss / 123456
建表
Create table a(
A integer,
B varchar(12)
);
Select bytes/1024 from v$dbfile;
 
 
切换用户
Conn sys as sysdba
删除表空间
Drop tablespace xxx including contents and datafiles;
创建表空间(本地)
Create tablespace xxx datafile ‘/………../test.dbf’ size 10M uniform size 128K;
创建用户
Create user sss identified by “*******” default tablespace xxx quota 2M on xxx;
给用户权限
Grant connect,resource to sss;
切换用户
Conn sss / 123456
Create table a(
A integer,
B varchar(12)
);
Select bytes/1024 from v$dbfile;
 
 
 
create undo tablespace zzz datafile '/home/oracle/t2/undo01.dbf' size 10M
vi initdb1.ora
undo_tablespace=undo01.dbf
 
 
修改临时表空间
Create temporary tablespace NAME tempfile ‘/…………../temp.dbf’ size 10M
Alter database default temporary tablespace NAME;
Col value$ format a50;
Select name,value$ from props$;
 
 
修改块的大小
Alter system set db_2k_cache_size=4M
Create tablespace name idatafile ‘/……../…..dbf’ size 10M blocksize 4K;
desc dba_tablespaces;
Select BLOCK_SIZE from dba_tablespaces where TABLESPACE_NAME=apper(‘name’);
 
 
修改表空间的可用性
Alter tablespace name  offline /online;
 
修改数据文件的可用性
Alter database ‘/…………./’ offline/online;
             File_id
file_id
select * from v$dbfile;
 
上不了
Recover datafile ‘          /   file-id
下不了
归档
 
Alter database default tablespace name;
 
修改空间的读写状态
Alter tablespace name read only/read write;
 
修改表空间的名
Alter tablespace namee1 rename to name2
 
看表空间
Select name from v$tablespace;
 
看数据文件
Select name from v$dbfiel;
 
 
 
 
 
扩大表空间
第一种
Alter tablespace name add datafile ‘/……../.’ size 10M;
Alter tablespace test online;
select file_name from dba_data_files where tablespace _name_name=upper(‘test’);
 
第二种
alter database datafile 5 resize 10M
 
第三种
Alter database datafile 5 autoextend on;
                    Autoextend on next 5M maxsize 20M
Select file_name,autoextensible from dba_data_files;
   
 
 
移动表空间
Select talbe_name,tablespace_name from user_tables where tablespace_name=’表空间名
alter tablespace xxx offline;
cp /home/oracle/t1/a.dbf /home/oracle/t2
alter tablespace xxx rename datafile '/home/oracle/t1/a.dbf' to '/home/oracle/t2/a.dbf';
alter tablespace xxx online;
 
 
alter database datafile 6 offline;
cp /home/oracle/t1/a02.dbf /home/oracle/t4
alter database rename file '/home/oracle/t1/a02.dbf' to '/home/oracle/t4/a02.dbf';
recover datafile 6;
alter database datafile 6 online;
 
                         
 
 
select CURRENT_SCN from v$database;
 
把时机转换为scn;
select scn_to_timestamp(407882) from dual;
select to_char(scn_to_timestamp(407882),'hh24:mi:ss') from dual;
select to_char(scn_to_timestamp(407882),'hh24:mi:ss') from dual;
select timestamp_to_scn(scn_to_timestamp(407882)) from dual;
select scn_to_timestamp(407882) from dual;
 
 
 
 
create table a (A integer,B integer);
insert into a values(1,12);
select CURRENT_SCN from v$database;
delete from a;
commit;
select * from a as of scn 408093;
insert into a select * from a as of scn 408093;
 
 
 
 
select tname from tab;
create table t (v number) storage(initial 1M);
查表大小
select segment_name,bytes/1024 from dba_segments where segment_name=upper(‘a’);
 
 
 
修改
create table b(v number)pctfree 20 pctused 60;
查看
select pct_used,pct_free from user_tables;
select TABLE_NAME,pct_used,pct_free from user_tables;
 
看用户的表
select TABLE_NAME,pct_used,pct_free from user_tables;
 
 
 
针对普通用户
删表
Drop table name;
恢复删了的表
Flashback table name to before drop;
看回收站的内容
Show recyclebin;
清空回收站
Purge reclebin;
 
 
 
create table tt (a varchar2(10),b integer);
insert into tt values('haha',1);
复制表
create table t as select * from tt;
create table t1 nologging as select * from tt;
create table t2 compress as select * from tt;
create table t3 parallel 2 as select * from tt;
 
 
 
改表名
alter table t1 rename to tt1;
移动表
alter table t3 move tablespace xxx;
改列名
alter table t2 rename column b to v;
增加列
alter table t2 add x varchar2(10);
删列
alter table tt1 drop column a;
 
 
select dbms_rowid.rowid_object(rowid) from tt;
select dbms_rowid.rowid_relative_fno(rowid) from tt;
select dbms_rowid.rowid_block_number(rowid) from tt;
select dbms_rowid.rowid_row_number(rowid) from tt;
在普通用户下
看用户的表
select tname from tab;
看表的表空间
select table_name,tablespace_name from user_tables where table_name=upper('tt');
sys用户
看数据文件
select file_name,file_id from dba_data_files where file_id=5;
 
 
 
建目录
mkdir txt
编辑文件
vi a.txt
1,12
2,13
3,14
4,15
5,16
Sys赋权限
grant create any directory to zl;
建索引
create directory dir as '/home/oracle/txt';
删索引
drop directory dir;
看索引
select * from dba_directories;
建外部表
create table haha ( id number,name varchar2(12)) organization external (type oracle_loader default directory dir access parameters(records delimited by newline fields terminated by ',' missing field values are null(id,name))location('a.txt'));
 
 
日志外部表
create table alert_log ( text varchar2(400) )organization external (type oracle_loader default directory dir access parameters (records delimited by newline nobadfile nodiscardfile nologfile )location('alert_db1.log') ) reject limit unlimited;
 
查看数据库中曾经出现过的ORA-错误
select * from alert_log where text like 'ORA-%';