Here is a good procedure for resetting any sequence to 0 from Oracle guru Tom Kyte. Great discussion on the pros and cons in the links below too.
[email protected]> create or replace procedure reset_seq( p_seq_name in varchar2 ) is l_val number; begin execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val; execute immediate 'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0'; execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val; execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0'; end; /
From this page: Dynamic SQL to reset sequence value
Another good discussion is also here: How to reset sequences?
Related Posts:
- ORA-00904: invalid identifier
- What is it exactly a BLOB in a DBMS context
- MySQL equivalent of DECODE function in Oracle
- MySQL Error: : ‘Access denied for user ‘root’@’localhost’
- What is the difference between “INNER JOIN” and “OUTER JOIN”?
- What is the difference between “INNER JOIN” and “OUTER JOIN”?
- Self Join to get employee manager name
- how to fix oracle ORA-01722 invalid number error
- MySQL Error: : ‘Access denied for user ‘root’@’localhost’
- ORA-00979 not a group by expression
- ORA-00907: missing right parenthesis
- Why do we need “Relationships” between tables at all?
- Case statement in MySQL
- How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
- ORA-01843 not a valid month- Comparing Dates
- ORA-00918: column ambiguously defined in SELECT *
- Oracle error : ORA-00905: Missing keyword
- How do I limit the number of rows returned by an Oracle query after ordering?
- Oracle SELECT TOP 10 records
- Can a foreign key be NULL and/or duplicate?
- How to Select Top 100 rows in Oracle?
- ORA-12560: TNS:protocol adaptor error
- What is the difference between JOIN and UNION?
- Error Code: 2013. Lost connection to MySQL server during query
- ORA-00972 identifier is too long alias column name
- Oracle Apex column link from LOV values
- PLS-00103: Encountered the symbol when expecting one of the following:
- What’s the difference between RANK() and DENSE_RANK() functions in oracle?
- ORA-00918: column ambiguously defined in SELECT *
- ORA-00918: column ambiguously defined in SELECT *
- how to drop partition without dropping data in MySQL?
- Oracle – ORA-01489: result of string concatenation is too long [duplicate]
- ORA-01861: literal does not match format string
- The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
- “ORA-01438: value larger than specified precision allowed for this column” when inserting 3
- How do you create a temporary table in an Oracle database?
- Oracle “Partition By” Keyword
- How to insert date values into table
- Oracle SQL: Update a table with data from another table
- Oracle SQL query for Date format
- The backend version is not supported to design database diagrams or tables
- Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
- SQL selecting rows by most recent date with two unique columns
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- Postgres Error: More than one row returned by a subquery used as an expression
- Comparing Dates in Oracle SQL
- Convert timestamp to date in Oracle SQL
- Update statement with inner join on Oracle
- what is the difference between triggers, assertions and checks (in database)
- Oracle: If Table Exists
- DATEDIFF function in Oracle
- How to create id with AUTO_INCREMENT on Oracle?
- LEFT function in Oracle
- Solutions for INSERT OR UPDATE on SQL Server
- What is the difference between Views and Materialized Views in Oracle?
- How do I view the Explain Plan in Oracle Sql developer?
- ORA-01779: cannot modify a column which maps to a non key-preserved table
- “select * into table” Will it work for inserting data into existing table
- ORA-01735: invalid ALTER TABLE option – Toad
- ora-06553 pls-306 wrong number or types of arguments in call to ‘ogc_x’
- SQL WITH clause example [duplicate]
- The wait operation timed out. ASP
- SQL query to select dates between two dates
- MySQL query String contains
- mysql Foreign key constraint is incorrectly formed error
- How do composite indexes work?
- Sql query – getting rid of hard-coded values
- How To Run A Github Repository?
- SQL Switch/Case in ‘where’ clause
- SQL Server reports ‘Invalid column name’, but the column is present and the query works through management studio
- Remote table-Valued Function Calls are not allowed
- Difference between View and table in sql
- Exclude a column using SELECT * [except columnA] FROM tableA?
- Why is SQL server throwing this error: Cannot insert the value NULL into column ‘id’?
- pg_ctl: no database directory specified and environment variable PGDATA unset
- PostgreSQL create table if not exists
- How to do a batch insert in MySQL
- GROUP BY and COUNT using ActiveRecord
- MySQL – Get row number on select
- How to auto login user again after change user_login
- Update user_login, user_nicename, and display_name
- Change sticky status of posts from phpMyAdmin
- WordPress and MySQL: how to transfer Meta_key and Meta_Value from one post_id to another
- Transferring working local PHP site to wordpress – with database (MySQL)
- Create table from array with prepare
- Does deleting a Plugin via the WordPress admin ‘completely’ remove the code?
- Using AND and bracket grouping in SQL not working
- How to Assign / Move all Deleted post to a category
- How to delete a particular row in a database table
- Clean up very big and very dirty database
- custom tables in wordpress Database? (can i just create them with sql?)
- Bulk delete WordPress Post and all metadata, etc using SQL query
- Accidently changed the GUID
- Translating WP query into to SQL query
- Reset post IDs with all post meta
- Help posting values to DB on submit using $wpdb->query
- How to create index (sql) to a meta_key?
- How to refactor DB queries for better TTFB in WordPress?
- Backtick (MySQL norm) added to SQL Server Query causing error
- $wpdb not working