[oracle@1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 3月 18 02:23:03 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@WUGQ10G> create user wugq identified by wugq;
用户已创建。
sys@WUGQ10G> grant resource,connect,plustrace to wugq;
授权成功。
sys@WUGQ10G> connect wugq/wugq
已连接。
wugq@WUGQ10G> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
WUGQ UNLIMITED TABLESPACE NO
wugq@WUGQ10G> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
WUGQ CONNECT NO YES NO
WUGQ PLUSTRACE NO YES NO
WUGQ RESOURCE NO YES NO
wugq@WUGQ10G> select count(*) from v$fixed_table;
select count(*) from v$fixed_table
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
wugq@WUGQ10G> connect / as sysdba
已连接。
sys@WUGQ10G> grant select on v_$fixed_table to wugq;
授权成功。
sys@WUGQ10G> connect wugq/wugq
已连接。
wugq@WUGQ10G> select count(*) from v$fixed_table;
COUNT(*)
----------
1383
wugq@WUGQ10G> connect / as sysdba
已连接。
sys@WUGQ10G> grant select any table to wugq;
授权成功。
scott@WUGQ10G> select count(*) from v$fixed_table;
COUNT(*)
----------
1383
scott@WUGQ10G> select count(*) from v_$fixed_table;
select count(*) from v_$fixed_table
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
scott@WUGQ10G> select count(*) from sys.v_$fixed_table;
COUNT(*)
----------
1383
scott@WUGQ10G> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SCOTT CONNECT NO YES NO
SCOTT DBA NO YES NO
SCOTT RESOURCE NO YES NO
scott@WUGQ10G> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
scott@WUGQ10G> select count(*) from v$fixed_table;
COUNT(*)
----------
1383
scott@WUGQ10G> select * from v_$fixed_table where rownum<2;
select * from v_$fixed_table where rownum<2
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
sys@WUGQ10G> select * from v$fixed_table where rownum<2;
NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
X$KQFTA 4294950912 TABLE 0
sys@WUGQ10G> select * from sys.v_$fixed_table where rownum<2;
NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
X$KQFTA 4294950912 TABLE 0
scott@WUGQ10G> set heading off echo off long 50000 pages 10000
scott@WUGQ10G> select * from v$fixed_view_definition where view_name='V$FIXED_TABLE';
V$FIXED_TABLE
select NAME , OBJECT_ID , TYPE , TABLE_NUM from GV$FIXED_TABLE where inst_id = USERENV('Instance')
> select * from v$fixed_view_definition where view_name='GV$FIXED_TABLE'; GV$FIXED_TABLE scott@WUGQ10G> select * from v$indexed_fixed_column where table_name='X$KQFTA'; X$KQFTA 1 ADDR 0 scott@WUGQ10G> select * from v$fixed_view_defintion where view_name='GV$PX_PROCESS'; GV$PX_PROCESS V$PX_PROCESS scott@WUGQ10G> set heading off echo off long 1000000000 pages 10000 select u.name, u.user#, u.password, scott@WUGQ10G> select substr(table_name,1,20) tabname, scott@WUGQ10G> select dbms_metadata.get_ddl(upper('TABLE'),upper('DEPT')) from dual; scott@WUGQ10G> select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
select inst_id,kqftanam, kqftaobj, 'TABLE', indx from x$kqfta union all select inst_id,kqfvinam, kqf
viobj, 'VIEW', 65537 from x$kqfvi union all select inst_id,kqfdtnam, kqfdtobj, 'TABLE', 65537 from x
$kqfdt
scott@WUGQ10G> desc v$indexed_fixed_column
名称 是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
TABLE_NAME VARCHAR2(30)
INDEX_NUMBER NUMBER
COLUMN_NAME VARCHAR2(30)
COLUMN_POSITION NUMBER
X$KQFTA 2 INDX 0
select * from v$fixed_view_defintion where view_name='GV$PX_PROCESS'
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
scott@WUGQ10G> select * from v$fixed_view_definition where view_name='GV$PX_PROCESS';
select a.inst_id, a.kxfpdpnam, decode(bitand(a.kxfpdpflg, 16), 0, 'IN USE', 'AVAILABLE'), b.pid, a
.kxfpdpspid, c.sid, c.serial# from x$kxfpdp a, V$PROCESS b, V$SESSION c where bitand(kxfpdpflg, 8)
!= 0 and a.kxfpdpspid = b.SPID and a.kxfpdpspid = c.PROCESS(+)
scott@WUGQ10G> select * from v$fixed_view_definition where view_name='V$PX_PROCESS';
select SERVER_NAME, STATUS, PID, SPID, SID, SERIAL# from GV$PX_PROCESS where inst_id = USERENV('In
stance')
scott@WUGQ10G> desc dba_views
名称 是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
scott@WUGQ10G> select text from dba_views where view_name='DBA_USERS';
m.status,
decode(u.astatus, 4, u.ltime,
5, u.ltime,
6, u.ltime,
8, u.ltime,
9, u.ltime,
10, u.ltime, to_date(NULL)),
decode(u.astatus,
1, u.exptime,
2, u.exptime,
5, u.exptime,
6, u.exptime,
9, u.exptime,
10, u.exptime,
decode(u.ptime, '', to_date(NULL),
decode(pr.limit#, 2147483647, to_date(NULL),
decode(pr.limit#, 0,
decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
dp.limit#/86400),
u.ptime + pr.limit#/86400)))),
dts.name, tts.name, u.ctime, p.name,
nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
u.ext_username
from sys.user$ u left outer join sys.resource_group_mapping$ cgm
on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and
cgm.value = u.name),
sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
where u.datats# = dts.ts#
and u.resource$ = p.profile#
and u.tempts# = tts.ts#
and u.astatus = m.status#
and u.type# = 1
and u.resource$ = pr.profile#
and dp.profile# = 0
and dp.type#=1
and dp.resource#=1
and pr.type# = 1
and pr.resource# = 1
2 substr(column_name,1,20) column_name,
3 rtrim(data_type)||'('||data_length')' from dba_tab_columns
4 where owner='&username'
5 /
输入 username 的值: SCOTT
原值 4: where owner='&username'
新值 4: where owner='SCOTT'
rtrim(data_type)||'('||data_length')' from dba_tab_columns
scott@WUGQ10G> select dbms_metadata.get_ddl(upper('TABLE'),upper('EMP')) from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"