SET LINES 200 -- NEW_VALUE in sqlplus specifies a variable to hold a column value COL tabspace_path FORMAT A50 NEW_VALUE path SELECT SUBSTR(FILE_NAME, 1, INSTR(FILE_NAME, '/', -1) -1) tabspace_path FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'SYSTEM'; SELECT '&path' variable_value FROM DUAL; CREATE BIGFILE TABLESPACE test_tbspc DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; exit
Run the script:
oracle@myserver:[testdb01]# sqlplus / as sysdba @test.sql Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Data Mining options TABSPACE_PATH -------------------------------------------------- /u02/oradata/testdb01 old 1: SELECT '&path' variable_value new 1: SELECT '/u02/oradata/testdb01' variable_value VARIABLE_VALUE --------------------- /u02/oradata/testdb01 Enter value for tablespace_name: mytablespace old 1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '&path/&tablespace_name..dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED new 1: CREATE BIGFILE TABLESPACE mytablespace DATAFILE '/u02/oradata/testdb01/mytablespace.dbf' SIZE 32M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED Tablespace created. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
No comments:
Post a Comment