Oralce常用SQL -追加中...

第一章：日志管理

    1.forcing log switches 

    sql> alter system switch logfile;

    2.forcing checkpoints

    sql> alter system checkpoint;

    3.adding online redo log groups

    sql> alter database add logfile [group 4]

    sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo') size 1m;

    4.adding online redo log members

    sql> alter database add logfile member

    sql> '/disk3/log1b.rdo' to group 1,

    sql> '/disk4/log2b.rdo' to group 2;

    5.changes the name of the online redo logfile

    sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log'

    sql> to 'c:/oracle/oradata/redo01.log';

    6.drop online redo log groups

    sql> alter database drop logfile group 3;

    7.drop online redo log members

    sql> alter database drop logfile member 'c:/oracle/oradata/redo01.log';

    8.clearing online redo log files

    sql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo';

    9.using logminer analyzing redo logfiles

    a. in the init.ora specify utl_file_dir = ' '

    b. sql> execute dbms_logmnr_d.build('oradb.ora','c:/oracle/oradb/log');

    c. sql> execute dbms_logmnr_add_logfile('c:/oracle/oradata/oradb/redo01.log',

    sql> dbms_logmnr.new);

    d. sql> execute dbms_logmnr.add_logfile('c:/oracle/oradata/oradb/redo02.log',

    sql> dbms_logmnr.addfile);

    e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>'c:/oracle/oradb/log/oradb.ora');

    f. sql> select * from v＄logmnr_contents(v＄logmnr_dictionary,v＄logmnr_parameters

    sql> v＄logmnr_logs);

    g. sql> execute dbms_logmnr.end_logmnr;

第二章：表空间管理

    1.create tablespaces

    sql> create tablespace tablespace_name datafile 'c:/oracle/oradata/file1.dbf' size 100m,

    sql> 'c:/oracle/oradata/file2.dbf' size 100m minimum extent 550k [logging/nologging]

    sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)

    sql> [online/offline] [permanent/temporary] [extent_management_clause]

    2.locally managed tablespace

    sql> create tablespace user_data datafile 'c:/oracle/oradata/user_data01.dbf'

    sql> size 500m extent management local uniform size 10m;

    3.temporary tablespace

    sql> create temporary tablespace temp tempfile 'c:/oracle/oradata/temp01.dbf'

    sql> size 500m extent management local uniform size 10m;

    4.change the storage setting

    sql> alter tablespace app_data minimum extent 2m;

    sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999);

    5.taking tablespace offline or online

    sql> alter tablespace app_data offline;

    sql> alter tablespace app_data online;

    6.read_only tablespace

    sql> alter tablespace app_data read only|write;

    7.droping tablespace

    sql> drop tablespace app_data including contents;

    8.enableing automatic extension of data files

    sql> alter tablespace app_data add datafile 'c:/oracle/oradata/app_data01.dbf'size 200m

    sql> autoextend on next 10m maxsize 500m;

    9.change the size fo data files manually

    sql> alter database datafile 'c:/oracle/oradata/app_data.dbf'resize 200m;

    10.Moving data files: alter tablespace

    sql> alter tablespace app_data rename datafile 'c:/oracle/oradata/app_data.dbf'

    sql> to 'c:/oracle/app_data.dbf';

    11.moving data files:alter database

    sql> alter database rename file 'c:/oracle/oradata/app_data.dbf'

    sql> to 'c:/oracle/app_data.dbf';
第三章：表

    1.create a table

    sql> create table table_name (column datatype,column datatype]....)

    sql> tablespace tablespace_name [pctfree integer] [pctused integer]

    sql> [initrans integer] [maxtrans integer]

    sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)

    sql> [logging|nologging] [cache|nocache]

    2.copy an existing table

    sql> create table table_name [logging|nologging] as subquery

    3.create temporary table

    sql> create global temporary table xay_temp as select * from xay;

    on commit preserve rows/on commit delete rows

    4.pctfree = (average row size - initial row size) *100 /average row size

    pctused = 100-pctfree- (average row size*100/available data space)

    5.change storage and block utilization parameter

    sql> alter table table_name pctfree=30 pctused=50 storage(next 500k

    sql> minextents 2 maxextents 100);

    6.manually allocating extents

    sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf');

    7.move tablespace

    sql> alter table employee move tablespace users;

    8.deallocate of unused space

    sql> alter table table_name deallocate unused [keep integer]

    9.truncate a table

    sql> truncate table table_name;

    10.drop a table

    sql> drop table table_name [cascade constraints];

    11.drop a column

    sql> alter table table_name drop column comments cascade constraints checkpoint 1000;

    alter table table_name drop columns continue;

    12.mark a column as unused

    sql> alter table table_name set unused column comments cascade constraints;

    alter table table_name drop unused columns checkpoint 1000;

    alter table orders drop columns continue checkpoint 1000

    data_dictionary : dba_unused_col_tabs

第四章：索引

    1.creating function-based indexes

    sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);

    2.create a B-tree index

    sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace

    sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]

    sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0

    sql> maxextents 50);

    3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows

    4.creating reverse key indexes

    sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k

    sql> next 200k pctincrease 0 maxextents 50) tablespace indx;

    5.create bitmap index

    sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k

    sql> pctincrease 0 maxextents 50) tablespace indx;

    6.change storage parameter of index

    sql> alter index xay_id storage (next 400k maxextents 100);

    7.allocating index space

    sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf');

    8.alter index xay_id deallocate unused;

第五章：约束

    1.define constraints as immediate or deferred

    sql> alter session set constraint[s] = immediate/deferred/default;

    set constraint[s] constraint_name/all immediate/deferred;

    2. sql> drop table table_name cascade constraints

    sql> drop tablespace tablespace_name including contents cascade constraints

    3. define constraints while create a table

    sql> create table xay(id number(7) constraint xay_id primary key deferrable

    sql> using index storage(initial 100k next 100k) tablespace indx);

    primary key/unique/references table(column)/check

    4.enable constraints

    sql> alter table xay enable novalidate constraint xay_id;

    5.enable constraints

    sql> alter table xay enable validate constraint xay_id;

第六章：LOAD数据

    1.loading data using direct_load insert

    sql> insert /*+append */ into emp nologging

    sql> select * from emp_old;

    2.parallel direct-load insert

    sql> alter session enable parallel dml;

    sql> insert /*+parallel(emp,2) */ into emp nologging

    sql> select * from emp_old;

    3.using sql*loader

    sql> sqlldr scott/tiger /

    sql> control = ulcase6.ctl /

    sql> log = ulcase6.log direct=true

第七章：reorganizing data

    1.using expoty

    ＄exp scott/tiger tables(dept,emp) file=c:/emp.dmp log=exp.log compress=n direct=y

    2.using import

    ＄imp scott/tiger tables(dept,emp) file=emp.dmp log=imp.log ignore=y

    3.transporting a tablespace

    sql>alter tablespace sales_ts read only;

    ＄exp sys/.. file=xay.dmp transport_tablespace=y tablespace=sales_ts triggers=n constraints=n

    ＄copy datafile

    ＄imp sys/.. file=xay.dmp transport_tablespace=y datafiles=(/disk1/sles01.dbf,/disk2/sles02.dbf)

    sql> alter tablespace sales_ts read write;

    4.checking transport set

    sql> DBMS_tts.transport_set_check(ts_list =>'sales_ts' ..,incl_constraints=>true);

    在表transport_set_violations 中查看

    sql> dbms_tts.isselfcontained 为true 是， 表示自包含

第八章： managing password security and resources

    1.controlling account lock and password

    sql> alter user juncky identified by oracle account unlock;

    2.user_provided password function

    sql> function_name(userid in varchar2(30),password in varchar2(30), old_password in varchar2(30)) return boolean

    3.create a profile : password setting

    sql> create profile grace_5 limit failed_login_attempts 3

    sql> password_lock_time unlimited password_life_time 30

    sql>password_reuse_time 30 password_verify_function verify_function

    sql> password_grace_time 5;

    4.altering a profile

    sql> alter profile default failed_login_attempts 3

    sql> password_life_time 60 password_grace_time 10;

    5.drop a profile

    sql> drop profile grace_5 [cascade];

    6.create a profile : resource limit

    sql> create profile developer_prof limit sessions_per_user 2

    sql> cpu_per_session 10000 idle_time 60 connect_time 480;

    7. view => resource_cost : alter resource cost

    dba_Users,dba_profiles

    8. enable resource limits

    sql> alter system set resource_limit=true;

第九章：Managing users

    1.create a user: database authentication

    sql> create user juncky identified by oracle default tablespace users

    sql> temporary tablespace temp quota 10m on data password expire

    sql> [account lock|unlock] [profile profilename|default];

    2.change user quota on tablespace

    sql> alter user juncky quota 0 on users;

    3.drop a user

    sql> drop user juncky [cascade];

    4. monitor user

    view: dba_users , dba_ts_quotas

第十章：managing privileges

    1.system privileges: view => system_privilege_map ,dba_sys_privs,session_privs

    2.grant system privilege

    sql> grant create session,create table to managers;

    sql> grant create session to scott with admin option;

    with admin option can grant or revoke privilege from any user or role;

    3.sysdba and sysoper privileges:

    sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile,

    alter tablespace begin/end backup,recover database

    alter database archivelog,restricted session

    sysdba: sysoper privileges with admin option,create database,recover database until

    4.password file members: view:=> v＄pwfile_users

    5.O7_dictionary_accessibility =true restriction access to view or tables in other schema

    6.revoke system privilege

    sql> revoke create table from karen;

    sql> revoke create session from scott;

    7.grant object privilege

    sql> grant execute on dbms_pipe to public;

    sql> grant update(first_name,salary) on employee to karen with grant option;

    8.display object privilege : view => dba_tab_privs, dba_col_privs

    9.revoke object privilege

    sql> revoke execute on dbms_pipe from scott [cascade constraints];

    10.audit record view :=> sys.aud＄

    11. protecting the audit trail

    sql> audit delete on sys.aud＄ by access;

    12.statement auditing

    sql> audit user;

    13.privilege auditing

    sql> audit select any table by summit by access;

    14.schema object auditing

    sql> audit lock on summit.employee by access whenever successful;

    15.view audit option : view=> all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,dba_obj_audit_opts

    16.view audit result: view=> dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_session,dba_audit_statement

第十一章： manager role

    1.create roles

    sql> create role sales_clerk;

    sql> create role hr_clerk identified by bonus;

    sql> create role hr_manager identified externally;

    2.modify role

    sql> alter role sales_clerk identified by commission;

    sql> alter role hr_clerk identified externally;

    sql> alter role hr_manager not identified;

    3.assigning roles

    sql> grant sales_clerk to scott;

    sql> grant hr_clerk to hr_manager;

    sql> grant hr_manager to scott with admin option;

    4.establish default role

    sql> alter user scott default role hr_clerk,sales_clerk;

    sql> alter user scott default role all;

    sql> alter user scott default role all except hr_clerk;

    sql> alter user scott default role none;

    5.enable and disable roles

    sql> set role hr_clerk;

    sql> set role sales_clerk identified by commission;

    sql> set role all except sales_clerk;

    sql> set role none;

    6.remove role from user

    sql> revoke sales_clerk from scott;

    sql> revoke hr_manager from public;

    7.remove role

    sql> drop role hr_manager;

    8.display role information

    view: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,role_sys_privs,role_tab_privs,session_roles

第十二章: BACKUP and RECOVERY

    1. v＄sga,v＄instance,v＄process,v＄bgprocess,v＄database,v＄datafile,v＄sgastat

    2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size

    3. Monitoring Parallel Rollback

    > v＄fast_start_servers , v＄fast_start_transactions

    4.perform a closed database backup (noarchivelog)

    > shutdown immediate

    > cp files /backup/

    > startup

    5.restore to a different location

    > connect system/manager as sysdba

    > startup mount

    > alter database rename file '/disk1/../user.dbf'to '/disk2/../user.dbf';

    > alter database open;

    6.recover syntax

    --recover a mounted database

    >recover database;

    >recover datafile '/disk1/data/df2.dbf';

    >alter database recover database;

    --recover an opened database

    >recover tablespace user_data;

    >recover datafile 2;

    >alter database recover datafile 2;

    7.how to apply redo log files automatically

    >set autorecovery on

    >recover automatic datafile 4;

    8.complete recovery:

    --method 1(mounted databae)

    >copy c:/backup/user.dbf c:/oradata/user.dbf

    >startup mount

    >recover datafile 'c:/oradata/user.dbf;

    >alter database open;

    --method 2(opened database,initially opened,not system or rollback datafile)

>copy c:/backup/user.dbf c:/oradata/user.dbf (alter tablespace offline)

    >recover datafile 'c:/oradata/user.dbf' or

    >recover tablespace user_data;

    >alter database datafile 'c:/oradata/user.dbf' online or

    >alter tablespace user_data online;

    --method 3(opened database,initially closed not system or rollback datafile)

    >startup mount

    >alter database datafile 'c:/oradata/user.dbf' offline;

    >alter database open

    >copy c:/backup/user.dbf d:/oradata/user.dbf

    >alter database rename file 'c:/oradata/user.dbf'to 'd:/oradata/user.dbf'

    >recover datafile 'e:/oradata/user.dbf' or recover tablespace user_data;

    >alter tablespace user_data online;

    --method 4(loss of data file with no backup and have all archive log)

    >alter tablespace user_data offline immediate;

    >alter database create datafile 'd:/oradata/user.dbf'as 'c:/oradata/user.dbf''

    >recover tablespace user_data;

    >alter tablespace user_data online

    5.perform an open database backup

    > alter tablespace user_data begin backup;

    > copy files /backup/

    > alter database datafile '/c:/../data.dbf' end backup;

    > alter system switch logfile;

    6.backup a control file

    > alter database backup controlfile to 'control1.bkp';

    > alter database backup controlfile to trace;

    7.recovery (noarchivelog mode)

    > shutdown abort

    > cp files

    > startup

    8.recovery of file in backup mode

    >alter database datafile 2 end backup;

    9.clearing redo log file

    >alter database clear unarchived logfile group 1;

    >alter database clear unarchived logfile group 1 unrecoverable datafile;

    10.redo log recovery

    >alter database add logfile group 3 'c:/oradata/redo03.log'size 1000k;

    >alter database drop logfile group 1;

    >alter database open;

    or >cp c:/oradata/redo02.log' c:/oradata/redo01.log

    >alter database clear logfile 'c:/oradata/log01.log';

第十三章: 常用监控SQL

1.监控时间等待：

SQL> select event,sum(decode(wait_time,0,0,1)) prev, sum(decode(wait_time,0,1,0)) curr,count(*)
  from v$session_wait
  group by event order by 4;

2.回滚段的争用：

SQL> select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where a.usn=b.usn;

3.监控表空间的I/O ：

SQL> select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,
  f.phyblkwrt pbw
  from v$filestat f,dba_data_files df
  where f.file#=df.file_id;

4.监空文件系统的I/O：

SQL> select substr(a.file#,1,2) "#",substr(a.name,1,30) "name",a.status,a.bytes,
  b.phyrds,b.phywrts
  from v$datafile a,v$filestat b
  where a.file#=b.file#;

5.在某个用户下找所有的索引：

SQL> select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
  from user_ind_columns, user_indexes
  where user_ind_columns.index_name = user_indexes.index_name
  and user_ind_columns.table_name = user_indexes.table_name
 order by user_indexes.table_type, user_indexes.table_name,
  user_indexes.index_name, column_position;

6.进程监控：

SQL> select distinct p.spid unix_process,
 s.terminal,
 to_char(s.logon_time,'YYYY/MON/DD HH24:MI') Logon_Time,
 s.username
 from v$process p, v$session s
 where p.addr=s.paddr order by 2;
7. 监控 SGA 中字典缓冲区的命中率

SQL> select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
  (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
  from v$rowcache
  where gets+getmisses <>0
  group by parameter, gets, getmisses;
8.监控 SGA 中共享缓存区的命中率，应该小于1%

SQL> select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;

Total Pins Total Reloads   LIBCACHE
---------- ------------- ----------
    178535          3167 1.77388187

SQL> select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
 from v$librarycache;

 

 hit radio reload percent
---------- --------------
 .91026811     .017730278

9.显示所有数据库对象的类别和大小

select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;

10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%

SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

11.监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size

SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

12. 监控当前数据库谁在运行什么SQL语句

SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13.监控字典缓冲区

SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;

SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE

14. 找ORACLE 字符集
select * from sys.props$ where name='NLS_CHARACTERSET';

15. 监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5 时，参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater 接近mts_max_servers 时，参数需加大

16. 碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name

HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

17. 表、索引的存储情况检查
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by
tablespace_name,segment_name;

select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;

 

18、找使用CPU 多的用户session

12 是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

19.监控log_buffer 的使用情况：(值最好小于1%，否则增加log_buffer 的大小)
select rbar.name,rbar.value,re.name,re.value,(rbar.value*100)/re.value||'%' "radio"
from v$sysstat rbar,v$sysstat re
where rbar.name='redo buffer allocation retries'
and re.name='redo entries';

第十四张：Oracle常用视图

表：
select * from cat;
select * from tab;
select table_name from user_tables;
视图：
select text from user_views where view_name=upper('&view_name');
索引：
select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by
table_name;
触发器：
select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;
快照：
select owner,name,master,table_name,last_refresh,next from user_snapshots order by
owner,next;
同义词：
select * from syn;
序列：
select * from seq;
数据库链路：
select * from user_db_links;
约束限制：
select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS
from user_constraints WHERE TABLE_name=upper('&TABLE_Name');
本用户读取其他用户对象的权限：
select * from user_tab_privs;
本用户所拥有的系统权限：
select * from user_sys_privs;
用户：
select * from all_users order by user_id;
表空间剩余自由空间情况：
select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space group
by tablespace_name；
数据字典：
select table_name from dict order by table_name;
锁及资源信息：
select * from v$lock;不包括DDL 锁
数据库字符集：
select name,value$ from props$ where name='NLS_CHARACTERSET';
inin.ora 参数：
select name,value from v$parameter order by name;
SQL 共享池：
select sql_text from v$sqlarea;
数据库：
select * from v$database
控制文件：
select * from V$controlfile;
重做日志文件信息：
select * from V$logfile;
来自控制文件中的日志文件信息：
select * from V$log;
来自控制文件中的数据文件信息：
select * from V$datafile;
NLS 参数当前值：
select * from V$nls_parameters;
ORACLE 版本信息：
select * from v$version;
描述后台进程：
select * from v$bgprocess;
查看版本信息：
select * from product_component_version;
查询表结构
select substr(table_name,1,20) tabname,
substr(column_name,1,20)column_name,
rtrim(data_type)||'('||data_length||')' from system.dba_tab_columns
where owner='username'
表空间使用状态
select a.file_id "FileNo",a.tablespace_name "Tablespace_name",
round(a.bytes/1024/1024,4) "Total MB",
round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4) "Used MB",
round(sum(nvl(b.bytes,0))/1024/1024,4) "Free MB",
round(sum(nvl(b.bytes,0))/a.bytes*100,4) "%Free"
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id(+)
group by a.tablespace_name,
a.file_id,a.bytes order by a.tablespace_name
查询某个模式下面数据不为空的表
declare
Cursor c is select TNAME from tab;
vCount Number;
table_nm Varchar2(100);
sq varchar2(300);
begin
for r in c loop
table_nm:=r.TNAME;
sq:='select count(*) from '|| table_nm;
execute immediate sq into vCount;
if vCount>0 then
dbms_output.put_line(r.tname);
end if;
end loop;
end;
客户端主机信息

SELECT
SYS_CONTEXT('USERENV','TERMINAL') TERMINAL,
SYS_CONTEXT('USERENV','HOST') HOST,
SYS_CONTEXT('USERENV','OS_USER') OS_USER,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP_ADDRESS
FROM DUAL