OraFAQ Forum: Server Administration » ALTER TABLE , changing STORAGE parameters

Home » RDBMS Server » Server Administration » ALTER TABLE , changing STORAGE parameters

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ALTER TABLE , changing STORAGE parameters [message #7944]

Thu, 17 July 2003 10:39

Gerald
Messages: 54
Registered: January 2001

Member

Hello everybody,
I explain:
I create a table TOTO
I insert 40 records
I duplicate the structure in a new table CLONE_TOTO
I want to change Storage parameters of CLONE_TOTO
in order to have the TOTO one's.
(In fact I want to addapt the new size of TOTO to Clone_TOTO)

#----- Modification des param�tres Storage -----#
Declare
resize NUMBER;V1 NUMBER;V2 NUMBER;V3 NUMBER;V4 NUMBER;V5 NUMBER;
V6 NUMBER;V7 NUMBER;V8 NUMBER;V9 NUMBER;
Begin
SELECT
NVL(PCT_FREE,0),
NVL(PCT_USED,0),
NVL(INI_TRANS,0),
NVL(MAX_TRANS,0),
NVL(INITIAL_EXTENT,0),
NVL(NEXT_EXTENT,0),
NVL(MIN_EXTENTS,0),
NVL(MAX_EXTENTS,0),
NVL(PCT_INCREASE,0)
INTO
v1,v2,v3,v4,v5,v6,v7,v8,v9
FROM DBA_TABLES
WHERE TABLE_NAME='TOTO';

resize:=(v8*8*2048)/1024;

ALTER TABLE Clone_toto
PCTFREE v1
PCTUSED v2
INITRANS v3
MAXTRANS v4
INITIAL v5
NEXT v6
MINEXTENTS v7
MAXEXTENTS v8
PCTINCREASE v9;
ALTER TABLE CLONE_TOTO ALLOCATE EXTENT
(size resizeK);
end;
/
-----------------------------------------------
And here's the msg :

ALTER TABLE CLONE_TOTO
*
ERREUR � la ligne 36 :
ORA-06550: line 36, column 1:
PLS-00103: Encountered the symbol "ALTER" when expecting
begin declare end exception exit for goto if loop mod nul
pragma raise return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
close current delete fetch lock insert open rollback
savepoint set sql commit <a single-quoted SQL string>

... anyone ?
(seems to be a newbie question, sorry ...

Report message to a moderator

Re: ALTER TABLE , changing STORAGE parameters [message #8034 is a reply to message #7944]

Tue, 22 July 2003 20:21

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

ALTER TABLE is DDL (data definition language), which can be done in SQL, but not PL/SQL, unless you execute it dynamically, like:

DECLARE
  .....
BEGIN
  .....
  EXECUTE IMMEDIATE ('ALTER TABLE clone_toto .....');
  .....
END;
/

Report message to a moderator

Re: ALTER TABLE , changing STORAGE parameters [message #8124 is a reply to message #7944]

Mon, 28 July 2003 20:39

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

I just say your other post, saying that you are using Oracle 8.0 and therefore can't use execute immediate, so you have to use dbms_sql. If you want to be able to execute ddl from various procedures, you could save some code by writing an execute_ddl procedure, like this:

SQL> CREATE OR REPLACE PROCEDURE execute_ddl
  2    (p_sql      IN VARCHAR2)
  3  IS
  4    cursor_name       PLS_INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
  5  BEGIN
  6    DBMS_SQL.PARSE (cursor_name, p_sql, DBMS_SQL.NATIVE);
  7    DBMS_SQL.CLOSE_CURSOR (cursor_name);
  8  END execute_ddl;
  9  /

Procedure created.

SQL> CREATE TABLE test_table
  2    (test_col1 NUMBER)
  3  /

Table created.

SQL> DESC test_table
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------
 TEST_COL1                                          NUMBER

SQL> BEGIN
  2    execute_ddl ('ALTER TABLE test_table ADD (test_col2 NUMBER)');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> DESC test_table
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------
 TEST_COL1                                          NUMBER
 TEST_COL2                                          NUMBER

Report message to a moderator

Previous Topic:	Quick help! Oracle 7.3.4 migration sequence
Next Topic:	SQL Plus error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 06:48:45 CDT 2024