ORA-02273: this unique/primary key is referenced by some foreign keys

Problem occurs because you are trying to drop a primary constraints which has a dependent foreign key Example

• Create two tables  which one  references to other
  create table departments
  (
  dept_id number,
  dept_name varchar2(100),
  constraint dept_id_pk primary key (dept_id)
  );
  
  create table employees
  (
  emp_id number,
  emp_name varchar2(100),
  dept_id number,
  constraint dept_id_fk foreign key(dept_id) references departments(dept_id)
  );

• I will try to drop the primary key of departments which is referenced by employees but could not
  SQL>alter table departments drop constraint dept_id_pk;
  
  ERROR at line 1:
  ORA-02273: this unique/primary key is referenced by some foreign keys

• I will find the foreign key which refer to the dept_id_pk
  
  SQL>select table_name,constraint_name
        from dba_constraints where r_constraint_name =upper('dept_id_pk');
  TABLE_NAME CONSTRAINT_NAME
  ------------------------------ ------------------------------
  EMPLOYEES DEPT_ID_FK

• First I will drop the foreign key which refer to the dept_id_pk
  
  SQL>alter table employees drop constraint DEPT_ID_FK;
  Table altered.

• Then I can drop the primary key of departments
  SQL>alter table departments drop constraint dept_id_pk;
  Table altered.

How to extract DDL for all Tablespaces

I  want  to  create  all  the  tablespaces  on development from the production so I  need  all tablespace  create script  then  I  will  modify  the  the  size  of datafiles
I need  a  script which  extract  all tablespace  create commands 



SQL>set pages 20000;
SQL>set long 100000;
SQL>set heading off;
SQL>set echo off;
SQL>spool create_ts.sql
SQL>select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

...

...
SQL>spool off

Now  I have all tablespace  create script is  create_ts.sql
Also we  can use  is  for only  one tablespace e.x TSDENEME

SQL>select dbms_metadata.get_ddl('TABLESPACE','TSDENEME') from dual;