Shulou(Shulou.com)06/01 Report--

Query database tablespace capacity and data volume

SELECT a.tablespace_name

Total

Free

(total-free)

Total / (1024 * 1024 * 1024) "TS (G)"

Free / (1024 * 1024 * 1024) "TSfree (G)"

(total-free) / (1024 * 1024 * 1024) "TSuse (G)"

Round ((total-free) / total, 4) * 100 "BaiFenBi%"

FROM (SELECT tablespace_name, SUM (bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a

(SELECT tablespace_name, SUM (bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

ASMCMD query storage usage

Ls-s

Lsdg

Du

Export data

Create or replace directory data as'/ home/oracle/imp'

Grant read,write on directory data to test1

Expdp test1/test1@pdborcl directory=data dumpfile=test1.dump logfile=test1.log schemas=test1

Impdp test1/test1@pdborcl directory=data dumpfile=test1.dump logfile=test1.log tables=tab_t1

Concatenate sql statements

Select 'select * from dba_tab_privs where grantee=' | | username | |'; 'from dba_users

Select 'select * from dba_tab_privs where grantee=''' | | username | |''; 'from dba_users

Select 'insert into table_name values (''| | field_1 | |'',''| | field_2 | |''); 'from table_name where field_n=some_conditon

Http://www.cppblog.com/fwxjj/archive/2011/10/17/158567.html

Query non-system users

Select username from dba_users where username not in

('SYS'

, 'SYSTEM'

, 'OUTLN'

, 'FLOWS_FILES'

, 'MDSYS'

, 'ORDSYS'

, 'EXFSYS'

, 'DBSNMP'

, 'WMSYS'

, 'APPQOSSYS'

, 'APEX_030200'

, 'ORDDATA'

, 'CTXSYS'

, 'ANONYMOUS'

, 'XDB'

, 'ORDPLUGINS'

, 'SI_INFORMTN_SCHEMA'

, 'OLAPSYS'

, 'ORACLE_OCM'

, 'XS$NULL'

, 'MDDATA'

, 'DIP'

, 'APEX_PUBLIC_USER'

, 'SPATIAL_CSW_ADMIN_USR'

, 'SPATIAL_WFS_ADMIN_USR')

Authorize a user's object to another user and revoke it

Select 'grant all on A1.' | | table_name | |' to B1 'from dba_tables where owner='A1'

Select 'revoke all on A1.' | | table_name | |' from B1 'from dba_tables where owner='A1'

Alter user A1 default_tablespace='B1'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.