英文:
How to drop table with dblink
问题
在Oracle中，如何使用dbLink删除一个表，如果它存在？我正在尝试使用Oracle中的DBLink连接到远程数据库。我正在使用Oracle SQL Developer。
`DROP TABLE TableName@dbLinkName`。这给我一个未知命令的错误。
begin declare
v_mytableobject_id
NUMBER(10);
SELECT
object_id INTO v_mytableobject_id FROM user_objects@dblink
WHERE
object_name = 'MYTABLE';
IF v_mytableobject_id IS NOT NULL THEN
dbms_utility.exec_ddl_statement@dblink ( 'drop table MYTABLE' );
ELSE
dbms_utility.exec_ddl_statement@dblink ( 'CREATE TABLE MYTABLE
(ID NUMBER(5,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE ,
CODE NVARCHAR2(15),
DESCRIPTION NVARCHAR2(125)
)'
);
dbms_utility.exec_ddl_statement@dblink ( 'CREATE UNIQUE INDEX MYTABLE_PK ON MYTABLE (ID)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)'
);
END IF;
end;
英文:
How to drop a table if it exist in oracle with dbLink?I am here trying to connect to remote database using DBLink in oracle.I am using oracle sql developer.
DROP TABLE TableName@dbLinkName. This gives me error Unknown Command.
begin declare
v_mytableobject_id
NUMBER(10);
SELECT
object_id INTO v_mytableobject_id FROM user_objects@dblink
WHERE
object_name = 'MYTABLE';
IF v_mytableobject_id IS NOT NULL THEN
dbms_utility.exec_ddl_statement@dblink ( 'drop table MYTABLE' );
ELSE
dbms_utility.exec_ddl_statement@dblink ( 'CREATE TABLE MYTABLE
(ID NUMBER(5,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE ,
CODE NVARCHAR2(15),
DESCRIPTION NVARCHAR2(125)
)'
);
dbms_utility.exec_ddl_statement@dblink ( 'CREATE UNIQUE INDEX MYTABLE_PK ON MYTABLE (ID)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)'
);
END IF;
end;
答案1
得分: 1
你不能像那样直接删除表格：
SQL> drop table test@dbl_mike;
drop table test@dbl_mike
*
ERROR at line 1:
ORA-02021: 不允许在远程数据库上执行DDL操作
不过有一个解决方法：
SQL> begin
2 dbms_utility.exec_ddl_statement@dbl_mike('drop table test');
3 end;
4 /
PL/SQL过程成功完成。
SQL>
英文:
You can't drop table just like that:
SQL> drop table test@dbl_mike;
drop table test@dbl_mike
*
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database
There's a workaround, though:
SQL> begin
2 dbms_utility.exec_ddl_statement@dbl_mike('drop table test');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论