SELECT table_name into v_name FROM all_tables WHERE rownum =1; dbms_output.put_line(v_name); end; / spool off
例如,要导出所有表的DDL到all_tables_ddl.sql文件中,可以使用以下命令: sql SET LONG 10000 SET PAGESIZE 500 SET LINESIZE 200 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET TRIMSPOOL ON SPOOL all_tables_ddl.sql 生成DDL语句: 使用DBMS_METADATA.GET_DDL函数来生成所有表的DDL语句。这里假设你有...
Ctable all_tables.table_name%TYPE) IS SELECT * FROM all_tables WHERE owner=Cowner AND table_name = Ctable; -- 局部变量 Lowner all_tables.owner%TYPE; Ltable all_tables.table_name%TYPE; BEGIN Pstatus:=0; -- OK Lowner:=UPPER(Powner); Ltable:=UPPER(Ptable); OPEN ta...
SQL> set define off SQL> select * from dba_tables where table_name ='&123'; no rows selected SQL> set define on SQL> select * from dba_tables where table_name ='&123'; Enter value for 123: 方法2: select chr(ascii('&')) from dual;...
2.get_tables.sql脚本: [oracle@jumper utl_file]$ more get_tables.sql select owner,table_name,tablespace_name,blocks,last_analyzed from all_tables order by 1,2; 3.执行并获得输出: [oracle@jumper utl_file]$ sqlplus "/ as sysdba" @main ...
1、Oracle sqlplus 常用命令1、查看当前库的所有数据表:SQL> select TABLE_NAME from all_tables;select * from all_tables;SQL> select table_name from all_tables where table_name likeTABLE_NAME default_auditing_options u ;2、查看表结构:desc all_tables;-显示CQI.T_BBS_XUSER的所有字段结构:desc ...
select ’grant select on ’||table_name||’ to public;’ from user_tables where 《条件》;删除用户下各种对象select ’drop ’||tabtype||’’||tname from tab;删除符合条件用户select ’drop user ’||username||’ cascade;’ from all_users where user_id>25;...
from all_tables order by 1,2; main.sql脚本内容: set linesize 200 set term off verify off feedback off pagesize 999 set markup html on entmap ON spool on preformat off spool tables.xls @get_tables.sql spool off exit 输出效果图: ...
63、表:a. 创建gen_drop_table.sql文件,包含如下语句:SPOOL c:/drop_table.sql SELECT 'DROP TABLE '| table_name |'' FROM user_tables; SPOOL OFF b. 以SCOTT用户登录数据库SQLPLUS > ./gen_dorp_table.sqlc. 在c盘根目录下会生成文件drop_table.sql文件,包含删除所有表的语句,如下所示:SQL>...
SQL> SELECT 'DELETE '|| table_name ||';' from dba_tables where owner='LJB' and rownum<=3; DELETE TEST_ABCD; DELETE TEST222; DELETE TEST111; SQL> SPOOL OFF 1. 2. 3. 4. 5. 看上去好像还有点小问题,咋有这个SQL>的东东头尾两个啊,呵呵,用上sqlplus -s 参数进sqlplus后,再操作就消失了...