Oracle常用命令

SQL> select username,default_tablespace,temporary_tablespace from dba_users;
SQL> select username,default_tablespace,temporary_tablespace from user_users;
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TEST_USER';
-- 
SELECT
    dba_users.USERNAME,
    dba_users.ACCOUNT_STATUS,
    dba_data_files.tablespace_name AS default_tablespace,
    dba_data_files.file_name AS data_file,
    CASE 
        WHEN dba_data_files.bytes IS NULL THEN NULL
        WHEN dba_data_files.bytes < 1024 THEN dba_data_files.bytes || ' B'
        WHEN dba_data_files.bytes < 1024 * 1024 THEN ROUND(dba_data_files.bytes/1024, 2) || ' KB'
        WHEN dba_data_files.bytes < 1024 * 1024 * 1024 THEN ROUND(dba_data_files.bytes/1024/1024, 2) || ' MB'
        ELSE ROUND(dba_data_files.bytes/1024/1024/1024, 2) || ' GB'
    END AS data_file_size,
    dba_temp_files.tablespace_name AS temp_tablespace,
    dba_temp_files.file_name AS temp_file,
    CASE 
        WHEN dba_temp_files.bytes IS NULL THEN NULL
        WHEN dba_temp_files.bytes < 1024 THEN dba_temp_files.bytes || ' B'
        WHEN dba_temp_files.bytes < 1024 * 1024 THEN ROUND(dba_temp_files.bytes/1024, 2) || ' KB'
        WHEN dba_temp_files.bytes < 1024 * 1024 * 1024 THEN ROUND(dba_temp_files.bytes/1024/1024, 2) || ' MB'
        ELSE ROUND(dba_temp_files.bytes/1024/1024/1024, 2) || ' GB'
    END AS temp_file_size
FROM dba_users
LEFT JOIN dba_data_files ON dba_users.DEFAULT_TABLESPACE = dba_data_files.tablespace_name
LEFT JOIN dba_temp_files ON dba_users.TEMPORARY_TABLESPACE = dba_temp_files.tablespace_name
order by dba_users.USERNAME;

SELECT
    d.tablespace_name tbsname,
    round(d.tablespace_size*(SELECT value FROM v$parameter WHERE name='db_block_size')/1024/1024, 2) total_mb,
    round(d.used_space*(SELECT value FROM v$parameter WHERE name='db_block_size')/1024/1024, 2) used_mb,
    round((d.tablespace_size-d.used_space)*(SELECT value FROM v$parameter WHERE name='db_block_size')/1024/1024, 2) left_mb,
    round(d.used_percent, 2) used_percent,
    (SELECT MAX(autoextensible) FROM dba_data_files WHERE tablespace_name=d.tablespace_name) autoextensible,
    (SELECT COUNT(file_name) FROM dba_data_files WHERE tablespace_name=d.tablespace_name) count_file
FROM
    dba_tablespace_usage_metrics d
WHERE
    d.tablespace_name NOT IN (
        SELECT tablespace_name FROM dba_tablespaces
        WHERE contents = 'TEMPORARY' OR contents = 'UNDO'
    )
ORDER BY 2 DESC;

-- dba 级
SELECT s.tablespace_name,
       s.OWNER,
       s.segment_name AS table_name,
       SUM(s.bytes) / 1024 / 1024 AS mb
FROM dba_segments s
WHERE s.segment_type = 'TABLE'
GROUP BY s.OWNER, s.tablespace_name, s.segment_name
ORDER BY mb DESC;
-- 用户级
SELECT
    s.segment_name AS table_name,
    SUM(s.bytes)/1024/1024 AS mb
FROM user_segments s
WHERE s.segment_type = 'TABLE'
GROUP BY s.segment_name
ORDER BY mb DESC;

-- dba 级
SELECT
    idx.TABLESPACE_NAME,
    idx.OWNER,
    idx.table_name,
    idx.index_name,
    SUM(s.bytes)/1024/1024 AS mb
FROM dba_segments s
JOIN dba_indexes idx
    ON s.segment_name = idx.index_name
WHERE s.segment_type = 'INDEX'
GROUP BY idx.TABLESPACE_NAME, idx.OWNER, idx.table_name, idx.index_name
ORDER BY mb DESC;
-- 用户级
SELECT 
    idx.table_name,
    idx.index_name,
    SUM(s.bytes)/1024/1024 AS mb
FROM user_segments s
JOIN user_indexes idx 
    ON s.segment_name = idx.index_name
WHERE s.segment_type = 'INDEX'
GROUP BY idx.table_name, idx.index_name
ORDER BY mb DESC;

-- dba级
SELECT
    l.TABLESPACE_NAME,
    l.OWNER,
    l.table_name,
    l.column_name,
    l.segment_name AS lob_segment,
    SUM(s.bytes)/1024/1024 AS mb
FROM dba_lobs l
JOIN dba_segments s
    ON l.segment_name = s.segment_name
WHERE s.segment_type = 'LOBSEGMENT'
GROUP BY l.TABLESPACE_NAME, l.OWNER, l.table_name, l.column_name, l.segment_name
ORDER BY mb DESC;
-- 用户级
SELECT
    l.table_name,
    l.column_name,
    l.segment_name AS lob_segment,
    SUM(s.bytes)/1024/1024 AS mb
FROM user_lobs l
JOIN user_segments s
    ON l.segment_name = s.segment_name
WHERE s.segment_type = 'LOBSEGMENT'
GROUP BY l.table_name, l.column_name, l.segment_name
ORDER BY mb DESC;

-- dba级
SELECT
    l.TABLESPACE_NAME,
    l.OWNER,
    l.table_name,
    l.column_name,
    l.index_name AS lob_index_name,
    SUM(s.bytes)/1024/1024 AS mb
FROM dba_lobs l
JOIN dba_segments s
    ON l.index_name = s.segment_name
WHERE s.segment_type = 'LOBINDEX'
GROUP BY l.TABLESPACE_NAME, l.OWNER, l.table_name, l.column_name, l.index_name
ORDER BY mb DESC;
-- 用户级
SELECT
    l.table_name,
    l.column_name,
    l.index_name AS lob_index_name,
    SUM(s.bytes)/1024/1024 AS mb
FROM user_lobs l
JOIN user_segments s
    ON l.index_name = s.segment_name
WHERE s.segment_type = 'LOBINDEX'
GROUP BY l.table_name, l.column_name, l.index_name
ORDER BY mb DESC;

SQL> grant connect,resource to 用户名;
SQL> grant unlimited tablespace to 用户名;
SQL> grant create view to 用户名;
SQL> grant create job to 用户名;
SQL> grant create public database link to 用户名;
SQL> grant create session to 用户名;
SQL> grant select on 表名 to 用户名;
SQL> grant insert on 表名 to 用户名;
SQL> grant update on 表名 to 用户名;
SQL> grant select,insert,update,delete,all on 表名 to 用户名;
# 所有表查询（dba权限执行）
SQL> grant select any table to reader;

-- 常用视图 (权限由大到小: dba_* > all_* > user_*)
-- dba_constraints：侧重约束具体信息
-- dba_cons_columns：侧重约束列信息
-- user_constraints：当前用户模式下创建的对象
-- all_constraints：当前用户模式下创建的对象加上当前用户能访问的其他用户创建的对象

-- 参考如下：

select * from dba_constraints;
select * from user_constraints;
select * from all_constraints;
select * from dba_cons_columns;
select * from user_cons_columns;
select * from all_cons_columns;

expdp ${user}/${passwd} \
directory=DATA_PUMP_DIR \
schemas=${user} \
filesize=2048M \
parallel=4 \
dumpfile=back_${date}_%U.dmp \
compression=all \
parfile=$(dirname "$0")/parfile.txt

参数说明：
filesize # 单个DUMP文件的最大容量
parallel # 并行度
dumpfile # 如使用变量 %U ，impdp时也需使用此变量

impdp ${dbuser}/${dbpasswd} \
directory=DATA_PUMP_DIR \
dumpfile=${filename} \
remap_tablespace=bwcx:bwcx1 \
remap_schema=bwcx_user:bwcx_user1 \
exclude=statistics \
table_exists_action=replace \
transform=segment_attributes:n

参数说明：
remap_tablespace #将表空间对象重新映射到另一个表空间
remap_schema #将一个用户的的数据迁移到另外一个用户
exclude=statistics #排除统计值
table_exists_action=replace #删除已存在表，重新建表并追加数据
transform=segment_attributes:n #TRANSFORM适用场景，导入和导出的时候，有些表空间不一样。segment_attributes Y：默认值，表示这个段的属性(物理属性，存储属性，表空间和logging)都将被包含在DDL的语句中。N:表示导入该对象的时候，不会指定表空间等属性，只是简单的创建一个对象。