SQL> desc all_tables; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) IOT_NAME VARCHAR2(30) STATUS VARCHAR2(8) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2(3) BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5) TABLE_LOCK VARCHAR2(8) SAMPLE_SIZE NUMBER LAST_ANALYZED DATE PARTITIONED VARCHAR2(3) IOT_TYPE VARCHAR2(12) TEMPORARY VARCHAR2(1) SECONDARY VARCHAR2(1) NESTED VARCHAR2(3) BUFFER_POOL VARCHAR2(7) ROW_MOVEMENT VARCHAR2(8) GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) SKIP_CORRUPT VARCHAR2(8) MONITORING VARCHAR2(3) CLUSTER_OWNER VARCHAR2(30) DEPENDENCIES VARCHAR2(8) COMPRESSION VARCHAR2(8) DROPPED VARCHAR2(3) SQL> desc all_users; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME NOT NULL VARCHAR2(30) USER_ID NOT NULL NUMBER CREATED NOT NULL DATE SQL> select TABLE_NAME FROM ALL_TABLES; TABLE_NAME ------------------------------ DUAL SYSTEM_PRIVILEGE_MAP TABLE_PRIVILEGE_MAP STMT_AUDIT_OPTION_MAP AUDIT_ACTIONS PSTUBTBL WRI$_ADV_ASA_RECO_DATA ODCI_SECOBJ$ ODCI_WARNINGS$ DEF$_TEMP$LOB OL$ TABLE_NAME ------------------------------ OL$HINTS OL$NODES KU$NOEXP_TAB IMPDP_STATS PLAN_TABLE$ WM$WORKSPACES_TABLE WM$VERSION_TABLE WM$NEXTVER_TABLE WM$VERSION_HIERARCHY_TABLE HELP DR$OBJECT_ATTRIBUTE TABLE_NAME ------------------------------ DR$POLICY_TAB DR$NUMBER_SEQUENCE RLM$PARSEDCOND OGIS_SPATIAL_REFERENCE_SYSTEMS OGIS_GEOMETRY_COLUMNS SDO_UNITS_OF_MEASURE SDO_PRIME_MERIDIANS SDO_ELLIPSOIDS SDO_DATUMS SDO_COORD_SYS SDO_COORD_AXIS_NAMES TABLE_NAME ------------------------------ SDO_COORD_AXES SDO_COORD_REF_SYS SDO_COORD_OP_METHODS SDO_COORD_OPS SDO_PREFERRED_OPS_SYSTEM SDO_PREFERRED_OPS_USER SDO_COORD_OP_PATHS SDO_COORD_OP_PARAMS SDO_COORD_OP_PARAM_USE SDO_COORD_OP_PARAM_VALS SDO_XML_SCHEMAS TABLE_NAME ------------------------------ XML_LOAD_RECORDS XML_LOAD_LOG CWM2$_AW_NEXT_TEMP_CUST_MEAS CWM2$_AW_TEMP_CUST_MEAS_MAP CWM2$_TEMP_VALUES CWM2$AWDIMCREATEACCESS CWM2$AWCUBECREATEACCESS OLAP_SESSION_DIMS OLAP_SESSION_CUBES OLAP_OLEDB_KEYWORDS OLAP_OLEDB_MDPROPVALS TABLE_NAME ------------------------------ OLAP_OLEDB_MDPROPS OLAP_OLEDB_FUNCTIONS_PVT SDO_TXN_IDX_INSERTS SDO_TXN_IDX_DELETES SDO_TXN_IDX_EXP_UPD_RGN SDO_TOPO_TRANSACT_DATA SDO_TOPO_RELATION_DATA SDO_TOPO_DATA$ SDO_PROJECTIONS_OLD_SNAPSHOT SDO_CS_SRS SDO_ELLIPSOIDS_OLD_SNAPSHOT TABLE_NAME ------------------------------ SDO_DATUMS_OLD_SNAPSHOT SDO_GEOR_XMLSCHEMA_TABLE SDO_GR_MOSAIC_0 SDO_GR_MOSAIC_1 SDO_GR_MOSAIC_2 SDO_GR_MOSAIC_3 SDO_GR_RDT_1 SDO_GEOR_PLUGIN_REGISTRY DEPT EMP BONUS TABLE_NAME ------------------------------ SALGRADE A AW$EXPRESS AW$AWCREATE AW$AWMD AW$AWREPORT AW$AWCREATE10G AW$AWXML OLAPTABLEVELS OLAPTABLEVELTUPLES MVIEW$_ADV_INDEX TABLE_NAME ------------------------------ MVIEW$_ADV_PARTITION MVIEW$_ADV_OWB OLAPI_HISTORY OLAPI_SESSION_HISTORY OLAPI_IFACE_OBJECT_HISTORY OLAPI_IFACE_OP_HISTORY OLAPI_MEMORY_OP_HISTORY OLAPI_MEMORY_HEAP_HISTORY DEPT_TARGET EMP1 T2 TABLE_NAME ------------------------------ TEST_SORT 100 rows selected. SQL> SELECT USER_NAME FROM ALL_USERS; SELECT USER_NAME FROM ALL_USERS * ERROR at line 1: ORA-00904: "USER_NAME": invalid identifier SQL> SELECT USERNAME FROM ALL_USERS; USERNAME ------------------------------ BI PM SH IX OE HR SCOTT MGMT_VIEW MDDATA SYSMAN MDSYS USERNAME ------------------------------ SI_INFORMTN_SCHEMA ORDPLUGINS ORDSYS OLAPSYS ANONYMOUS XDB CTXSYS EXFSYS WMSYS DBSNMP TSMSYS USERNAME ------------------------------ DMSYS DIP OUTLN SYSTEM SYS 27 rows selected. SQL> SET PAGESIZE 80; SQL> SELECT USER_ID,USERNAME FROM ALL_USERS; USER_ID USERNAME ---------- ------------------------------ 60 BI 59 PM 58 SH 57 IX 56 OE 55 HR 54 SCOTT 53 MGMT_VIEW 50 MDDATA 51 SYSMAN 46 MDSYS 45 SI_INFORMTN_SCHEMA 44 ORDPLUGINS 43 ORDSYS 47 OLAPSYS 39 ANONYMOUS 38 XDB 36 CTXSYS 34 EXFSYS 25 WMSYS 24 DBSNMP 21 TSMSYS 35 DMSYS 19 DIP 11 OUTLN 5 SYSTEM 0 SYS 27 rows selected. SQL> SELECT USER_ID,USERNAME,CREATED FROM ALL_USERS; USER_ID USERNAME CREATED ---------- ------------------------------ --------- 60 BI 02-AUG-14 59 PM 02-AUG-14 58 SH 02-AUG-14 57 IX 02-AUG-14 56 OE 02-AUG-14 55 HR 02-AUG-14 54 SCOTT 17-APR-07 53 MGMT_VIEW 17-APR-07 50 MDDATA 17-APR-07 51 SYSMAN 17-APR-07 46 MDSYS 17-APR-07 45 SI_INFORMTN_SCHEMA 17-APR-07 44 ORDPLUGINS 17-APR-07 43 ORDSYS 17-APR-07 47 OLAPSYS 17-APR-07 39 ANONYMOUS 17-APR-07 38 XDB 17-APR-07 36 CTXSYS 17-APR-07 34 EXFSYS 17-APR-07 25 WMSYS 17-APR-07 24 DBSNMP 17-APR-07 21 TSMSYS 17-APR-07 35 DMSYS 17-APR-07 19 DIP 17-APR-07 11 OUTLN 17-APR-07 5 SYSTEM 17-APR-07 0 SYS 17-APR-07 27 rows selected. SQL> DESC GLOBAL_NAME; Name Null? Type ----------------------------------------- -------- ---------------------------- GLOBAL_NAME VARCHAR2(4000) SQL> SELECT * FROM GLOBAL_NAME; GLOBAL_NAME -------------------------------------------------------------------------------- ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM SQL> desc ALL_CONSTRAINTS; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE INDEX_OWNER VARCHAR2(30) INDEX_NAME VARCHAR2(30) INVALID VARCHAR2(7) VIEW_RELATED VARCHAR2(14) SQL> select CONSTRAINT_NAME,OWNER,CONSTRAINT_TYPE FROM ALL_CONSTRAINTS WHERE TABLE_NAME='EMP'; CONSTRAINT_NAME OWNER C ------------------------------ ------------------------------ - PK_EMP SCOTT P FK_DEPTNO SCOTT R SQL> select CONSTRAINT_NAME,OWNER,CONSTRAINT_TYPE FROM ALL_CONSTRAINTS WHERE TABLE_NAME='DEPT'; CONSTRAINT_NAME OWNER C ------------------------------ ------------------------------ - PK_DEPT SCOTT P SQL> SELECT DISTINCT CONSTRAINT_TYPE FROM ALL_CONSTRAINTS; C - V U R P C O 6 rows selected. /* C - Check constraint on a table P - Primary key U - Unique key R - Referential integrity (Foreign Key) V - With check option, on a view O - With read only, on a view */ SQL> SELECT DISTINCT CONSTRAINT_TYPE,CONSTRAINT_NAME FROM ALL_CONSTRAINTS; C CONSTRAINT_NAME - ------------------------------ C SYS_C00602 C SYS_C00605 O SYS_C002830 O SYS_C002849 O SYS_C002914 O SYS_C002861 O SYS_C002869 O SYS_C002877 O SYS_C002898 O SYS_C002901 O SYS_C002938 O SYS_C002920 O SYS_C002956 V SYS_C003122 U SYS_C003129 R FK_SRID R ELLIPSOID_FOREIGN_LEGACY R PRIME_MERIDIAN_FOREIGN_UOM R ELLIPSOID_FOREIGN_UOM C SYS_C003229 C SYS_C003235 P COORDINATE_SYSTEM_PRIM ... 358 rows selected. SQL> CREATE TABLE supplier 2 ( 3 supplier_id numeric(10) not null, 4 supplier_name varchar2(50) not null, 5 contact_name varchar2(50), 6 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) 7 ); Table created. SQL> desc supplier; Name Null? Type ----------------------------------------- -------- ---------------------------- SUPPLIER_ID NOT NULL NUMBER(10) SUPPLIER_NAME NOT NULL VARCHAR2(50) CONTACT_NAME VARCHAR2(50) SQL> select CONSTRAINT_NAME,OWNER,CONSTRAINT_TYPE FROM ALL_CONSTRAINTS WHERE TABLE_NAME='supplier'; no rows selected SQL> select CONSTRAINT_NAME,OWNER,CONSTRAINT_TYPE FROM ALL_CONSTRAINTS WHERE TABLE_NAME='SUPPLIER'; CONSTRAINT_NAME OWNER C ------------------------------ ------------------------------ - SYS_C007518 SCOTT C SYS_C007519 SCOTT C SUPPLIER_PK SCOTT P SQL> ALTER TABLE supplier 2 ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_name); ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_name) * ERROR at line 2: ORA-02260: table can have only one primary key SQL> ALTER TABLE supplier 2 DROP CONSTRAINT supplier_pk; Table altered. SQL> desc supplier; Name Null? Type ----------------------------------------- -------- ---------------------------- SUPPLIER_ID NOT NULL NUMBER(10) SUPPLIER_NAME NOT NULL VARCHAR2(50) CONTACT_NAME VARCHAR2(50) SQL> select CONSTRAINT_NAME,OWNER,CONSTRAINT_TYPE FROM ALL_CONSTRAINTS WHERE TABLE_NAME='SUPPLIER'; CONSTRAINT_NAME OWNER C ------------------------------ ------------------------------ - SYS_C007518 SCOTT C SYS_C007519 SCOTT C SQL> ALTER TABLE supplier 2 ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name); // grouping 2 columns as a single primanry key. Table altered. SQL> desc supplier; Name Null? Type ----------------------------------------- -------- ---------------------------- SUPPLIER_ID NOT NULL NUMBER(10) SUPPLIER_NAME NOT NULL VARCHAR2(50) CONTACT_NAME VARCHAR2(50) SQL> select CONSTRAINT_NAME,OWNER,CONSTRAINT_TYPE FROM ALL_CONSTRAINTS WHERE TABLE_NAME='SUPPLIER'; CONSTRAINT_NAME OWNER C ------------------------------ ------------------------------ - SYS_C007518 SCOTT C SYS_C007519 SCOTT C SUPPLIER_PK SCOTT P SQL> select STATUS FROM ALL_CONSTRAINTS WHERE TABLE_NAME='SUPPLIER'; STATUS -------- ENABLED ENABLED ENABLED SQL> SET LINESIZE 200; SQL> SELECT CONSTRAINT_NAME,SEARCH_CONDITION,STATUS,VALIDATED FROM ALL_CONSTRAINTS WHERE TABLE_NAME='SUPPLIER'; CONSTRAINT_NAME SEARCH_CONDITION STATUS VALIDATED ------------------------------ -------------------------------------------------------------------------------- -------- ------------- SYS_C007518 "SUPPLIER_ID" IS NOT NULL ENABLED VALIDATED SYS_C007519 "SUPPLIER_NAME" IS NOT NULL ENABLED VALIDATED SUPPLIER_PK ENABLED VALIDATED SQL> ALTER TABLE supplier 2 DISABLE CONSTRAINT supplier_pk; Table altered. SQL> SELECT CONSTRAINT_NAME,SEARCH_CONDITION,STATUS,VALIDATED FROM ALL_CONSTRAINTS WHERE TABLE_NAME='SUPPLIER'; CONSTRAINT_NAME SEARCH_CONDITION STATUS VALIDATED ------------------------------ -------------------------------------------------------------------------------- -------- ------------- SYS_C007518 "SUPPLIER_ID" IS NOT NULL ENABLED VALIDATED SYS_C007519 "SUPPLIER_NAME" IS NOT NULL ENABLED VALIDATED SUPPLIER_PK DISABLED NOT VALIDATED SQL> DESC SUPPLIER; Name Null? Type ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- SUPPLIER_ID NOT NULL NUMBER(10) SUPPLIER_NAME NOT NULL VARCHAR2(50) CONTACT_NAME VARCHAR2(50) SQL> INSERT INTO SUPPLIER VALUES(1,'ONE','ONLY ONE'); 1 row created. SQL> INSERT INTO SUPPLIER VALUES(1,'TWO','ONLY TWO'); 1 row created. SQL> INSERT INTO SUPPLIER VALUES(1,'ONE','ONLY ONE'); 1 row created. SQL> SELECT * FROM SUPPLIER; SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME ----------- -------------------------------------------------- -------------------------------------------------- 1 ONE ONLY ONE 1 TWO ONLY TWO 1 ONE ONLY ONE SQL> ALTER TABLE supplier 2 ENABLE CONSTRAINT supplier_pk; ALTER TABLE supplier * ERROR at line 1: ORA-02437: cannot validate (SCOTT.SUPPLIER_PK) - primary key violated SQL> DELETE FROM SUPPLIER; 3 rows deleted. SQL> SELECT * FROM supplier; no rows selected SQL> ALTER TABLE supplier 2 ENABLE CONSTRAINT supplier_pk; Table altered. SQL> SELECT CONSTRAINT_NAME,SEARCH_CONDITION,STATUS,VALIDATED FROM ALL_CONSTRAINTS WHERE TABLE_NAME='supplier'; no rows selected SQL> SELECT CONSTRAINT_NAME,SEARCH_CONDITION,STATUS,VALIDATED FROM ALL_CONSTRAINTS WHERE TABLE_NAME='SUPPLIER'; CONSTRAINT_NAME SEARCH_CONDITION STATUS VALIDATED ------------------------------ -------------------------------------------------------------------------------- -------- ------------- SYS_C007518 "SUPPLIER_ID" IS NOT NULL ENABLED VALIDATED SYS_C007519 "SUPPLIER_NAME" IS NOT NULL ENABLED VALIDATED SUPPLIER_PK ENABLED VALIDATED SQL> INSERT INTO SUPPLIER VALUES(1,'ONE','ONLY ONE'); 1 row created. SQL> INSERT INTO SUPPLIER VALUES(1,'TWO','ONLY TWO'); 1 row created. SQL> INSERT INTO SUPPLIER VALUES(1,'ONE','ONLY ONE'); INSERT INTO SUPPLIER VALUES(1,'ONE','ONLY ONE') * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SUPPLIER_PK) violated SQL> CREATE TABLE persons(P_Id int NOT NULL PRIMARY KEY, 2 FirstName varchar(25) NOT NULL, 3 LastName varchar(25), 4 Address varchar(255)); Table created. SQL> select CONSTRAINT_NAME,SEARCH_CONDITION,STATUS,VALIDATED FROM ALL_CONSTRAINTS WHERE TABLE_NAME= 2 'PERSONS'; CONSTRAINT_NAME SEARCH_CONDITION STATUS VALIDATED ------------------------------ -------------------------------------------------------------------------------- -------- ------------- SYS_C007522 "P_ID" IS NOT NULL ENABLED VALIDATED SYS_C007523 "FIRSTNAME" IS NOT NULL ENABLED VALIDATED SYS_C007524 ENABLED VALIDATED SQL> spool off;