1. 查询数据库中每个表的记录条数(count):
SELECT t.table_name,t.num_rows FROM user_tables t order by num_rows desc;
2. 查询数据库中表的数据量(bytes):
select s.segment_name,bytes/1024/1024||'MB' as p from user_segments s where s.segment_type='TABLE' order by bytes desc;
3.新建临时表
①指定表空间
create table temp_sales_detail tablespace users as select osurcd ,sales_id from sales_detail group by osurcd ,sales_id ;
②不指定表空间
create table temp_sales as SELECT s.id ,
(SELECT COALESCE(TRIM(ABAC22),'0') FROM ADMIN T WHERE T.ID = s.soan8) abac22
FROM SALES S
where S.SODCTO IN ('Z2','Z3','F2','F3')
注:此处不能用nullif来替换COALESCE,因为ABAC22不为null,里面有空格,即使trim后任判断为true
3.新建索引
create index temp_sales_detail_idx on temp_sales_detail(sales_id) tablespace users;
若是创建unique索引,则只需在index前面加上“unique”即可。
4.创建用户并赋予权限
create user my_user identified by my_password default tablespace users; // 创建一个用户my_user ,密码为my_password ,默认表空间为users
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO my_user ; // 赋予dba权限
5.查询一个用户下所有的表名
select owner,table_name from dba_tables
6.查询用户下所有表的列的定义(可以用来反向生成项目的数据库定义文档)
select * from cols
7.在cmd中导入导出数据
导出命令:exp my_user/my_password@127.0.0.1/XE file=d:/py_db.dmp full=y rows=y
导入命令:imp my_user/my_password@127.0.0.1/XE file=d:/py_db.dmp full=y