Server Administration :: How To View Table Constraints Information
Mar 13, 2010
I know that you can view all constraints in a table using $USER_CONSTRAINTS or $USER_INDEXES view. What i find difficult is that the information in those views is not really well presented.
I find it very hard to follow. For example, just to find out which columns are foreign keys and which tables they refer to is really tedious. Is there a simpler way to view all the constraints especially foreign keys of a table and including which table columns they refer to in the child table.
I like to see a diagramatic representaion of all the tables involved so i like to see both the parent and the child tables, which columns are related and using which FKs etc. is there an easier way to view this information?
What should I do with those disabled CONSTRAINTS (most were those file starts with LOGMNR) & TRIGGERS found on our production dB? Are there any impact once I enable those?
I want to create a store procedure to copy data from a source tables(which may not have any constraints defined) to a table which has primary key, foreign key and unique key constraints.
Any records which are rejected due to these constraints not being satisfied need to go another table.
Once the initial data load is done, these procedures need to be automated(through cron) to do the future incremental uploads in the same manner.
I can desc the view,but i can not get the ddl of the view,how can i do?
SQL> desc oss03.VW_OSS_PM_MONTH_3_201112;
Name Null? Type ----------------------------------------- -------- ---------------------------
RECDATE NOT NULL NUMBER(8) USERNUMBER NOT NULL VARCHAR2(32) MODULEID NOT NULL NUMBER(8) CHANNELID NOT NULL NUMBER(8) OPERID NOT NULL NUMBER(10) CONTENTID NOT NULL NUMBER(10) SERVICEID NOT NULL NUMBER(10) OPERTYPE NOT NULL NUMBER(10) PROVCODE NOT NULL NUMBER(5) AREACODE NOT NULL NUMBER(5) SERVICEITEM NOT NULL VARCHAR2(20) ORDERTYPE NOT NULL NUMBER(8)
When you create a MAV, you automatically get a hidden column and an index. Here's an example,drop user jon cascade;
grant dba to jon identified by jon; conn jon/jon create table emp as select * from scott.emp; create materialized view mv1 enable query rewrite as select deptno,sum(sal) from emp group by deptno; select object_name,object_type from user_objects; select index_name,column_name from user_ind_columns where table_name='MV1'; select column_name,hidden_column from user_tab_cols where table_name='MV1'; select deptno,"SUM(SAL)",sys_nc00003$ from mv1;
SQL> drop MATERIALIZED view log on afccv.tbl_voicechat; drop MATERIALIZED view log on afccv.tbl_voicechat * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
i dont want to kill the sessions or anything is there any way to set prority to do this task?
When we create a duplicate table, we use the below query:
create table table2 as select * from table1;
But table2 is created without any constraints inherited from table1.Can I know how can i create the table with all the constraints existing in parent table.
I want to truncate table partition but I'm getting error:
CODEORA-02266: unique/primary keys in table referenced by enabled foreign keys
because of table has a nested table. Currently this column is not in use so I could drop it, but I want to avoid it (table is huge). Is there another posibility to do truncate partitions in this table? ALTER TABLE ... SET UNUSED doesn't resolve the problem.
can a table level check constraints have conditional checking (if else clause or case conditional structures) and checks which are limited through something like a where clause which inside the table level check constraints.And can a table level check constraints refer to a column in another table column which should have a the same value.
i am a beginner in SQL and want to create a table with the following constraints :
'orderno' should be primary key and start with o 'clientno' should be foreign key 'ordr date' is not null 'salesmanno' foreign key 'delytype' default value 'f' and possible value ( f or p) 'delydate' cannot be less than order date 'orderstatus' values ('in process.......)
here's my
create table sales_order(orderno varchar2(6) like 'o%' primary key, clientno varchar2(6) foreign key references client_master(clientno),deldate date not null,dellyaddr varchar2(25),salesmanno varchar2(6) foreign key references salesman_master(salesmanno), delytype char(1) default 'f',billyn char(1),delydate date check > orderdate,orderstatus varchar2(10)check ('in process','fullfilled','cancelled');
Connected to: Oracle8i Release 8.1.6.0.0 - Production JServer Release 8.1.6.0.0 - Production IMP-00003: ORACLE error 942 encountered ORA-00942: table or view does not exist IMP-00023: Import views not installed, please notify your DBA IMP-00000: Import terminated unsuccessfully
Then i run the catexp.sql using SYS user. But i still got that error.
