Skip navigation.

TRUNCATE

SQL> rem
SQL> rem Ejemplo con TRUNCATE:
SQL> rem
SQL> rem Para empezar hay que crear una tabla:
SQL> rem
SQL> create table truncate_example as select * from dba_tables
2 /

Table created.

SQL> rem La tabla tiene muchas líneas:
SQL> rem
SQL> select count(*) from truncate_example
2 /

COUNT(*)
----------
2522

SQL> rem Y 10 pedazos en el disco:
SQL> rem
SQL> select count(*) from dba_extents where segment_name = 'TRUNCATE_EXAMPLE'
2 /

COUNT(*)
----------
10

SQL> rem Se puede borrar las líneas y quedarse con los pedazos en el disco
SQL> rem con TRUNCATE y REUSE STORAGE:
SQL> rem
SQL> truncate table truncate_example reuse storage
2 /

Table truncated.

SQL> select count(*) from truncate_example
2 /

COUNT(*)
----------
0

SQL> select count(*) from dba_extents where segment_name = 'TRUNCATE_EXAMPLE'
2 /

COUNT(*)
----------
10

SQL> rem Pero con DROP STORAGE, lo que pasa por defecto, los pedazos en
SQL> rem el disco desaparecen también:
SQL> rem
SQL> insert into truncate_example select * from dba_tables
2 /

2523 rows created.

SQL> select count(*) from truncate_example
2 /

COUNT(*)
----------
2523

SQL> truncate table truncate_example drop storage
2 /

Table truncated.

SQL> select count(*) from truncate_example
2 /

COUNT(*)
----------
0

SQL> select count(*) from dba_extents where segment_name = 'TRUNCATE_EXAMPLE'
2 /

COUNT(*)
----------
1

SQL> drop table truncate_example
2 /

Table dropped.

SQL> spool off

I had a go at submitting this

I had a go at submitting this last night at home but I'm not sure if it worked as I could not read the CAPTCHA letters very well.

I've approved you post.

I've approved you post. However, it's not really a blog post, but just a script. It would be great if you can expand it to describe what you're trying to demonstrate.

Feedback from Frank

Dear Frank,

Thank you for your feedback.
I'm sorry I did not get back to you earlier.
I fully understand what you mean and will submit a revised version ASAP.

Regards,

Andrew